MySQL - SELECT WHERE поле IN (подзапрос) - Чрезвычайно медленно почему?

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

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

Таким образом, я получаю все строки с соответствующим_поле, которые происходят более одного раза. Этот запрос требует выполнения миллисекунд.

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

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

По какой-то причине это оказывается очень медленным (требуется минут). Что именно здесь происходит, чтобы сделать это медленным? Соответствующее_информация индексируется.

В конце концов я попытался создать представление "temp_view" из первого запроса (SELECT relevant_field FROM some_table GROUP BY relevant_field HAVING COUNT(*) > 1), а затем вместо этого сделав второй запрос следующим образом:

SELECT *
FROM some_table
WHERE relevant_field IN
(
    SELECT relevant_field
    FROM temp_view
)

И это работает отлично. MySQL делает это за несколько миллисекунд.

Любые эксперты SQL, которые могут объяснить, что происходит?

Ответы

Ответ 1

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

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 только с фиксированным списком значений.

Дополнительные советы

Если вы хотите быстрее выполнять запросы, не делайте выбор SELECT * только поля, которые вам действительно нужны.
Убедитесь, что у вас есть индекс на relevant_field, чтобы ускорить выравнивание.
Обязательно group by на первичном ключе.
Если вы используете InnoDB и, вы выбираете только индексированные поля (и вещи не слишком сложны), чем MySQL разрешит ваш запрос, используя только индексы, ускоряя путь вверх.

Общее решение для 90% ваших запросов IN (select

Используйте этот код

SELECT * FROM sometable a WHERE EXISTS (
  SELECT 1 FROM sometable b
  WHERE a.relevant_field = b.relevant_field
  GROUP BY b.relevant_field
  HAVING count(*) > 1)

Ответ 2

Подзапрос выполняется для каждой строки, потому что это коррелированный запрос. Можно сделать коррелированный запрос в некоррелированный запрос, выбирая все из подзапроса, например:

SELECT * FROM
(
    SELECT relevant_field
    FROM some_table
    GROUP BY relevant_field
    HAVING COUNT(*) > 1
) AS subquery

Окончательный запрос будет выглядеть так:

SELECT *
FROM some_table
WHERE relevant_field IN
(
    SELECT * FROM
    (
        SELECT relevant_field
        FROM some_table
        GROUP BY relevant_field
        HAVING COUNT(*) > 1
    ) AS subquery
)

Ответ 3

Подзапросы против соединений

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

Ответ 4

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;

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

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

Ответ 5

Попробуйте это

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;

Ответ 6

Я переформатировал ваш медленный 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
 );

Помогает ли это?

Ответ 7

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

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

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

Ответ 8

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

Ищем запись, которая имеет account_code='101.100' в tabel_buku_besar, которые имеют companyarea='20000', а также имеют IDR как currency
Мне нужно получить все записи из 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`

Ответ 9

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

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

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

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

Ответ 10

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

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
)