MySQL – SELECCIONAR DONDE campo IN (subconsulta) – Extremadamente lento ¿por qué?

Tengo un par de duplicados en una base de datos que quiero inspeccionar, entonces lo que hice para ver cuáles son duplicados, lo hice:

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

De esta forma, obtendré todas las filas con el campo relevante ocurriendo más de una vez. Esta consulta toma milisegundos para ejecutarse.

Ahora, quería inspeccionar cada uno de los duplicados, así que pensé que podría SELECCIONAR cada fila en alguna tabla con un campo relevante en la consulta anterior, así que me gustó esto:

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

Esto resulta extremadamente lento por alguna razón (lleva minutos). ¿Qué está pasando exactamente aquí para que sea tan lento? relevant_field está indexado.

Eventualmente intenté crear una vista “temp_view” desde la primera consulta (SELECT relevant_field FROM some_table GROUP BY relevant_field HAVING COUNT(*) > 1) , y luego hacer mi segunda consulta así:

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

Y eso funciona bien. MySQL hace esto en algunos milisegundos.

¿Algún experto SQL aquí que pueda explicar qué está pasando?

Reescribe la consulta en este

 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 

Creo que st2.relevant_field debe estar en el select, porque de lo contrario la cláusula having dará un error, pero no estoy 100% seguro

Nunca use IN con una subconsulta; esto es notoriamente lento.
Solo use IN con una lista fija de valores.

Mas consejos

  1. Si quiere hacer consultas más rápido, no haga SELECT * solo seleccione los campos que realmente necesita.
  2. Asegúrate de tener un índice en el campo relevant_field para acelerar la equicombinación.
  3. Asegúrese de group by en la clave principal.
  4. Si está en InnoDB y solo selecciona campos indexados (y las cosas no son demasiado complejas), MySQL resolverá su consulta utilizando solo los índices, acelerando el proceso.

Solución general para el 90% de su IN (select consultas

Usa este código

 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) 

La subconsulta se está ejecutando para cada fila porque es una consulta correlacionada. Uno puede hacer una consulta correlacionada en una consulta no correlacionada al seleccionar todo de la subconsulta, de esta manera:

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

La consulta final se vería así:

 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 ) 
 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; 

Intenté tu consulta en una de mis bases de datos y también intenté reescribirla como una unión a una sub consulta.

Esto funcionó mucho más rápido, ¡pruébalo!

Prueba esto

 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; 

He reformateado tu consulta SQL lenta con http://www.prettysql.net

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

Al usar una tabla tanto en la consulta como en la subconsulta, siempre debe alias las dos, como esta:

 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 ); 

¿Eso ayuda?

a veces, cuando los datos crecen, mysql WHERE IN puede ser bastante lento debido a la optimización de consultas. Intenta usar STRAIGHT_JOIN para decirle a mysql que ejecute la consulta tal como está, por ejemplo

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

pero ten cuidado: en la mayoría de los casos, el optimizador de mysql funciona bastante bien, por lo que te recomendaría usarlo solo cuando tienes este tipo de problema

Esto es similar a mi caso, donde tengo una tabla llamada tabel_buku_besar . Lo que necesito son

  1. Buscando un registro que tenga account_code='101.100' en tabel_buku_besar que tenga companyarea='20000' y también tenga IDR como currency

  2. Necesito obtener todos los registros de tabel_buku_besar que tienen account_code igual que el paso 1 pero tienen transaction_number en el paso 1 resultado

mientras utilizo select ... from...where....transaction_number in (select transaction_number from ....) , mi consulta se ejecuta de forma extremadamente lenta y, en ocasiones, provoca un tiempo de espera de la solicitud o hace que mi aplicación no responda …

Intento esta combinación y el resultado … no está mal …

 `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` 

Encuentro que este es el más eficiente para encontrar si existe un valor, la lógica puede invertirse fácilmente para encontrar si un valor no existe (es decir, 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 

* Reemplace el campo relevante con el nombre del valor que desea verificar existe en su tabla

* Reemplace primaryKey con el nombre de la columna de clave principal en la tabla de comparación.

En primer lugar, puede encontrar las filas duplicadas y encontrar el recuento de filas que se usa cuántas veces y ordenarlas por un número como este;

 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 ) 
Intereting Posts