MySQL-выберите, где поле в (подзапрос) - очень медленно, почему?

перепишите запрос в этот

SELECT st1.*, st2.relevant_field FROM sometable st1
INNER JOIN sometable st2 ON (st1.relevant_field = st2.relevant_field)
GROUP BY st1.id  /* list a unique sometable field here*/
HAVING COUNT(*) > 1

Я думаю st2.relevant_field должно быть в select, потому что в противном случае having предложение выдаст ошибку, но я не уверен на 100%

никогда не используйте IN с подзапросом; это крайне медленно.
Только когда-либо использовать IN с фиксированным списком значений.

подзапросы vs соединения

http://www.scribd.com/doc/2546837/New-Subquery-Optimizations-In-MySQL-6

SELECT st1.*
FROM some_table st1
inner join 
(
    SELECT relevant_field
    FROM some_table
    GROUP BY relevant_field
    HAVING COUNT(*) > 1
)st2 on st2.relevant_field = st1.relevant_field;

Я пробовал ваш запрос на одной из моих баз данных, а также попытался переписать его как соединение с подзапросом.

это сработало намного быстрее, попробуйте!

я переформатировал ваш медленный sql-запрос с помощью www.prettysql.net

SELECT *
FROM some_table
WHERE
 relevant_field in
 (
  SELECT relevant_field
  FROM some_table
  GROUP BY relevant_field
  HAVING COUNT ( * ) > 1
 );

при использовании таблицы как в запросе, так и в подзапросе, вы всегда должны использовать псевдонимы, например:

SELECT *
FROM some_table as t1
WHERE
 t1.relevant_field in
 (
  SELECT t2.relevant_field
  FROM some_table as t2
  GROUP BY t2.relevant_field
  HAVING COUNT ( t2.relevant_field ) > 1
 );

это поможет?

попробуй такое

SELECT t1.*
FROM 
 some_table t1,
  (SELECT relevant_field
  FROM some_table
  GROUP BY relevant_field
  HAVING COUNT (*) > 1) t2
WHERE
 t1.relevant_field = t2.relevant_field;

иногда, когда данные растут больше mysql, где IN может быть довольно медленным из-за оптимизации запросов. Попробуйте использовать STRAIGHT_JOIN, чтобы сказать MySQL, чтобы выполнить запрос, как это, например,

SELECT STRAIGHT_JOIN table.field FROM table WHERE table.id IN (...)

но будьте осторожны: в большинстве случаев оптимизатор mysql работает довольно хорошо, поэтому я бы рекомендовал использовать его только тогда, когда у вас есть такая проблема

это похоже на мой случай, где у меня есть таблица с именем tabel_buku_besar. Что мне нужно, так это

1. Ищу запись, которая есть account_code='101.100' на tabel_buku_besar, которые companyarea='20000' а также IDR как currency

2. мне нужно получить все записи из tabel_buku_besar которые имеют account_code же, как Шаг 1, но имеют transaction_number в шаге 1 результат

при использовании select ... from...where....transaction_number in (select transaction_number from ....) мой запрос работает очень медленно и иногда вызывая тайм-аут запроса или сделать мое приложение не отвечает...

я пробую эту комбинацию и результат...не плохо...

`select DATE_FORMAT(L.TANGGAL_INPUT,'%d-%m-%y') AS TANGGAL,
      L.TRANSACTION_NUMBER AS VOUCHER,
      L.ACCOUNT_CODE,
      C.DESCRIPTION,
      L.DEBET,
      L.KREDIT 
 from (select * from tabel_buku_besar A
                where A.COMPANYAREA='$COMPANYAREA'
                      AND A.CURRENCY='$Currency'
                      AND A.ACCOUNT_CODE!='$ACCOUNT'
                      AND (A.TANGGAL_INPUT BETWEEN STR_TO_DATE('$StartDate','%d/%m/%Y') AND STR_TO_DATE('$EndDate','%d/%m/%Y'))) L 
INNER JOIN (select * from tabel_buku_besar A
                     where A.COMPANYAREA='$COMPANYAREA'
                           AND A.CURRENCY='$Currency'
                           AND A.ACCOUNT_CODE='$ACCOUNT'
                           AND (A.TANGGAL_INPUT BETWEEN STR_TO_DATE('$StartDate','%d/%m/%Y') AND STR_TO_DATE('$EndDate','%d/%m/%Y'))) R ON R.TRANSACTION_NUMBER=L.TRANSACTION_NUMBER AND R.COMPANYAREA=L.COMPANYAREA 
LEFT OUTER JOIN master_account C ON C.ACCOUNT_CODE=L.ACCOUNT_CODE AND C.COMPANYAREA=L.COMPANYAREA 
ORDER BY L.TANGGAL_INPUT,L.TRANSACTION_NUMBER`

Я считаю, что это наиболее эффективно для поиска, если значение существует, логика может быть легко инвертирована, чтобы найти, если значение не существует (т. е. NULL);

SELECT * FROM primary_table st1
LEFT JOIN comparision_table st2 ON (st1.relevant_field = st2.relevant_field)
WHERE st2.primaryKey IS NOT NULL

* замените relevant_field именем значения, которое вы хотите проверить, существует в вашей таблице

* замените primaryKey на имя столбца первичного ключа в таблице сравнения.

во-первых, вы можете найти повторяющиеся строки и найти количество строк используется, сколько раз и заказать его по номеру, как это:

SELECT q.id,q.name,q.password,q.NID,(select count(*) from UserInfo k where k.NID= q.NID) as Count,
(
		CASE q.NID
		WHEN @curCode THEN
			@curRow := @curRow + 1
		ELSE
			@curRow := 1
		AND @curCode := q.NID
		END
	) AS No
FROM UserInfo q,
(
		SELECT
			@curRow := 1,
			@curCode := ''
	) rt
WHERE q.NID IN
(
    SELECT NID
    FROM UserInfo
    GROUP BY NID
    HAVING COUNT(*) > 1
) 

после этого создайте таблицу и вставьте в нее результат.

create table CopyTable 
SELECT q.id,q.name,q.password,q.NID,(select count(*) from UserInfo k where k.NID= q.NID) as Count,
(
		CASE q.NID
		WHEN @curCode THEN
			@curRow := @curRow + 1
		ELSE
			@curRow := 1
		AND @curCode := q.NID
		END
	) AS No
FROM UserInfo q,
(
		SELECT
			@curRow := 1,
			@curCode := ''
	) rt
WHERE q.NID IN
(
    SELECT NID
    FROM UserInfo
    GROUP BY NID
    HAVING COUNT(*) > 1
) 

наконец, удалить дубликат rows.No это начало 0. Кроме первого номера каждой группы удалите все дубликаты строк.

delete from  CopyTable where No!= 0;