update - subquery mysql ejemplos

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

Question

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?

Answer 1

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
) 

después de eso crea una tabla e inserta el resultado.

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
) 

Finalmente, elimine las filas duplicadas. No es inicio 0. Excepto el primer número de cada grupo, elimine todas las filas duplicadas.

delete from  CopyTable where No!= 0;

Answer 2

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.

Answer 3

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`

Answer 4

He reformateado tu consulta SQL lenta con 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?

Answer 5

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
)

Answer 6

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;

Answer 7

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) 

Answer 8

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

Answer 9

Subconsultas vs uniones

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

Answer 10

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!