wake-up-neo.com

MySQL - SELECT WHERE Feld IN (Unterabfrage) - Warum extrem langsam?

Ich habe ein paar Duplikate in einer Datenbank, die ich untersuchen möchte. Was ich getan habe, um zu sehen, welche Duplikate sind, habe ich Folgendes getan:

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

Auf diese Weise bekomme ich alle Zeilen mit relevantem Feld mehr als einmal. Diese Abfrage dauert Millisekunden.

Nun wollte ich jedes der Duplikate überprüfen, also dachte ich, ich könnte jede Zeile in some_table mit einem relevanten_field in der obigen Abfrage auswählen, also habe ich Folgendes getan:

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

Dies stellt sich aus irgendeinem Grund als extrem langsam heraus (es dauert Minuten). Was genau ist hier los, um es so langsam zu machen? relevantes Feld ist indiziert.

Schließlich habe ich versucht, aus der ersten Abfrage (SELECT relevant_field FROM some_table GROUP BY relevant_field HAVING COUNT(*) > 1) eine Sicht "temp_view" zu erstellen, und dann meine zweite Abfrage wie folgt zu machen:

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

Und das funktioniert gut. MySQL macht dies in einigen Millisekunden.

Irgendwelche SQL-Experten, die erklären können, was los ist?

mysqlsubquerywhere-in
113
quano

Die Unterabfrage wird für jede Zeile ausgeführt, da es sich um eine korrelierte Abfrage handelt. Sie können eine korrelierte Abfrage in eine nicht korrelierte Abfrage umwandeln, indem Sie alles aus der Unterabfrage auswählen:

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

Die letzte Abfrage würde folgendermaßen aussehen:

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
)
93
quano

Schreiben Sie die Abfrage in diese um

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

Ich denke, st2.relevant_field muss in der Auswahl sein, da andernfalls die having-Klausel einen Fehler ergibt, aber ich bin nicht zu 100% sicher

Verwenden Sie niemals IN mit einer Unterabfrage. das ist notorisch langsam.
Verwenden Sie IN nur mit einer festen Liste von Werten. 

Mehr Tipps  

  1. Wenn Sie Abfragen beschleunigen möchten, wählen Sie mit ___ nicht SELECT * nur die Felder aus, die Sie wirklich benötigen.
  2. Stellen Sie sicher, dass Sie einen Index für relevant_field haben, um den Equi-Join zu beschleunigen.
  3. Stellen Sie sicher, dass der Primärschlüssel group by ist. 
  4. Wenn Sie sich in InnoDB und befinden, wählen Sie nur indizierte Felder aus (und die Dinge sind nicht zu komplex), dann löst MySQL Ihre Abfrage nur mit den Indizes auf und beschleunigt alles.

Allgemeine Lösung für 90% Ihrer IN (select-Abfragen

Verwenden Sie diesen Code

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)
107
Johan

Unterabfragen vs. Verknüpfungen

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

5
edze
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;

Ich habe Ihre Abfrage in einer meiner Datenbanken ausprobiert und auch als Join für eine Unterabfrage neu geschrieben.

Das hat viel schneller funktioniert, probieren Sie es aus!

4
ceteras

Versuche dies

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;
3
user2244323

Ich habe Ihre langsame SQL-Abfrage mit www.prettysql.net umformatiert

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

Wenn Sie eine Tabelle sowohl in der Abfrage als auch in der Unterabfrage verwenden, sollten Sie immer beide Bezeichnungen verwenden, z.

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

Hilft das?

3
plang

manchmal, wenn die Datenmenge größer wird, werden mysql WHERE INs aufgrund der Abfrageoptimierung ziemlich langsam. Verwenden Sie STRAIGHT_JOIN, um MySQL anzuweisen, die Abfrage so auszuführen, wie sie ist, z. 

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

aber Achtung: In den meisten Fällen funktioniert der MySQL-Optimierer ziemlich gut, daher würde ich empfehlen, ihn nur zu verwenden, wenn Sie ein solches Problem haben

1
Andrey Posudevsky

Erstens können Sie doppelte Zeilen finden und die Anzahl der Zeilen ermitteln, wie oft und ordnen Sie sie nach dieser Nummer ein.

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
)

danach eine Tabelle erstellen und das Ergebnis einfügen.

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
)

Löschen Sie schließlich die Dublettenzeilen. Nein ist der Anfang 0. Außer der ersten Nummer jeder Gruppe löschen Sie alle Dublettenzeilen. 

delete from  CopyTable where No!= 0;

0
harun ugur

Dies ist ähnlich zu meinem Fall, wo ich eine Tabelle namens tabel_buku_besar habe. Was ich brauche, sind

  1. Suche nach Datensätzen, die account_code='101.100' in tabel_buku_besar haben, die companyarea='20000' und auch IDR als currency haben

  2. Ich muss alle Datensätze von tabel_buku_besar abrufen, deren Kontocode mit Schritt 1 identisch ist, jedoch in Schritt 1 transaction_number lautet 

während der Verwendung von select ... from...where....transaction_number in (select transaction_number from ....) läuft meine Abfrage extrem langsam und verursacht manchmal eine Anforderungszeitüberschreitung oder führt dazu, dass meine Anwendung nicht reagiert ...

Ich versuche diese Kombination und das Ergebnis ... nicht schlecht ...

`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`
0
Hilarius L. Doren

Ich finde dies am effizientesten, um herauszufinden, ob ein Wert existiert. Die Logik kann leicht invertiert werden, um herauszufinden, ob ein Wert nicht existiert (dh 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

* Ersetzen Sie relevant_field durch den Namen des Wertes, den Sie in Ihrer Tabelle überprüfen möchten

* Ersetzen Sie primaryKey durch den Namen der Primärschlüsselspalte in der Vergleichstabelle.

0
Matt