MySQL-SELECCIONAR DÓNDE campo EN (subconsulta) - Extremadamente lento ¿por qué?

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

Más consejos

1. Si desea realizar consultas más rápido, no haga un SELECT * solo seleccione los campos que realmente necesitas.
2. Asegúrese de tener un indexar en relevant_field para acelerar el equi-join.
3. Asegúrese de group by en la clave primaria.
4. Si estás en InnoDB y solo seleccionas campos indexados (y las cosas no son demasiado complejas) entonces MySQL resolverá tu consulta usando solo los índices, acelerando las cosas.

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

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

Subconsultas vs joins

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;

He intentado su consulta en una de mis bases de datos, y también lo intenté reescrito como un join a una sub-consulta.

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

He reformateado su 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
 );

Cuando se usa una tabla tanto en la consulta como en la subconsulta, siempre debe usar el alias de ambas, de la siguiente manera:

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?

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;

A veces cuando los datos crecen mysql DONDE ins podría ser bastante lento debido a la optimización de consultas. Intente usar STRAIGHT_JOIN para decirle a mysql que ejecute la consulta tal cual, por ejemplo,

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

Pero cuidado: en la mayoría de los casos mysql optimizer funciona bastante bien, por lo que recomendaría usarlo solo cuando tenga este tipo de problema

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

1. Buscando registro que tienen account_code='101.100' en tabel_buku_besar que tienen companyarea='20000' y también tienen 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 resultado del paso 1

Al usar select ... from...where....transaction_number in (select transaction_number from ....), mi consulta se ejecuta extremadamente lenta y, a veces, causa que la solicitud se extinga o que mi aplicación no responder...

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 esto es lo más eficiente para encontrar si existe un valor, la lógica se puede invertir fácilmente para encontrar si un valor no existe (ie ES NULO);

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

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

*Reemplace PrimaryKey con el nombre de la columna de clave primaria en la tabla de comparación.

En primer lugar, puede encontrar filas duplicadas y buscar recuento de filas se utiliza cuántas veces y ordenarlo por 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, cree una tabla e inserte el resultado en ella.

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, eliminar dublicar rows.No es inicio 0. Excepto el primer número de cada grupo eliminar todas las filas dublicate.

delete from  CopyTable where No!= 0;