MySQL - SELECT WHERE field IN (subquery) - Ekstremalnie powolne dlaczego?

Mam kilka duplikatów w bazie danych, które chcę sprawdzić, więc co zrobiłem, aby zobaczyć, które są duplikatami, zrobiłem to:

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

W ten sposób otrzymam wszystkie wiersze z odpowiednim_polem występującym więcej niż raz. Wykonanie tego zapytania zajmuje milisekundy.

Teraz chciałem sprawdzić każdy z duplikatów, więc pomyślałem, że mogę WYBRAĆ każdy wiersz w some_table z odpowiednim_polem w powyższym zapytaniu, więc zrobiłem tak:

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

Z jakiegoś powodu okazuje się to niezwykle powolne (zajmuje to kilka minut). Co dokładnie się tu dzieje, że jest tak powolny? odpowiednie_field jest indeksowane.

Ostatecznie spróbowałem utworzyć widok „temp_view” na podstawie pierwszego zapytania (SELECT relevant_field FROM some_table GROUP BY relevant_field HAVING COUNT(*) > 1), a następnie wykonałem drugie zapytanie w ten sposób:

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

I to działa dobrze. MySQL robi to w kilka milisekund.

Czy są tu jacyś eksperci SQL, którzy mogą wyjaśnić, co się dzieje?

Przepisz zapytanie do tego

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

Myślę, że st2.relevant_fieldmusi być w selekcji, bo inaczej havingklauzula da błąd, ale nie jestem w 100% pewien

Nigdy nie używaj INz podzapytaniem; jest to notorycznie powolne.
Używaj tylko INze stałą listą wartości.

Więcej podpowiedzi

1. Jeśli chcesz, aby zapytania były szybsze, nie SELECT *wybieraj tylko tych pól, których naprawdę potrzebujesz.
2. Upewnij się, że masz włączony indeks, relevant_fieldaby przyspieszyć sprzężenie równe.
3. Upewnij się, że group byklucz podstawowy.
4. Jeśli korzystasz z InnoDB i wybierasz tylko indeksowane pola (a rzeczy nie są zbyt skomplikowane), MySQL rozwiąże twoje zapytanie przy użyciu tylko indeksów, przyspieszając wszystko.

Ogólne rozwiązanie dla 90% Twoich IN (select zapytań

Użyj tego kodu

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) 

Podzapytanie jest uruchamiane dla każdego wiersza, ponieważ jest to zapytanie skorelowane. Można przekształcić zapytanie skorelowane w zapytanie nieskorelowane, wybierając wszystko z podzapytania, na przykład:

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

Ostateczne zapytanie wyglądałoby następująco:

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
)

Podzapytania a sprzężenia

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;

Wypróbowałem zapytanie w jednej z moich baz danych, a także przepisałem je jako sprzężenie z zapytaniem podrzędnym.

To działało dużo szybciej, spróbuj!

Spróbuj tego

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;

Sformatowałem Twoje powolne zapytanie sql z adresem www.prettysql.net

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

Używając tabeli zarówno w zapytaniu, jak i podzapytaniu, należy zawsze używać aliasów do obu, na przykład:

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

To pomaga?

Po pierwsze, możesz znaleźć zduplikowane wiersze i znaleźć liczbę wierszy używaną ile razy i uporządkować według liczby w ten sposób;

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
) 

następnie utwórz tabelę i wstaw do niej wynik.

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
) 

Na koniec usuń wiersze do opublikowania. Numer zaczyna się od 0. Z wyjątkiem pierwszego numeru każdej grupy usuń wszystkie wiersze do opublikowania.

delete from  CopyTable where No!= 0;

czasami, gdy dane rosną, mysql WHERE IN może działać dość wolno z powodu optymalizacji zapytań. Spróbuj użyć STRAIGHT_JOIN, aby powiedzieć mysql, aby wykonywał zapytanie tak, jak jest, np

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

ale uwaga: w większości przypadków optymalizator mysql działa całkiem nieźle, więc polecam go używać tylko wtedy, gdy masz tego rodzaju problem

Jest to podobne do mojego przypadku, w którym mam tabelę o nazwie tabel_buku_besar. To, czego potrzebuję, to

1. Patrząc na zapis, że mają account_code='101.100'w tabel_buku_besarktórych companyarea='20000'a także IDRjakocurrency

2. Muszę pobrać wszystkie rekordy, z tabel_buku_besarktórych kod_konta jest taki sam jak w kroku 1, ale transaction_numberwynik w kroku 1

podczas używania select ... from...where....transaction_number in (select transaction_number from ....)moje zapytanie działa bardzo wolno i czasami powoduje przekroczenie limitu czasu żądania lub powoduje, że moja aplikacja nie odpowiada ...

Próbuję tej kombinacji i efekt ... nieźle ...

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

Uważam, że jest to najbardziej efektywne w wyszukiwaniu, czy wartość istnieje, logikę można łatwo odwrócić, aby znaleźć, czy wartość nie istnieje (tj. JEST NULL);

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

* Zastąp odpowiednie_pole nazwą wartości, którą chcesz sprawdzić, czy istnieje w Twojej tabeli

* Zastąp primaryKey nazwą kolumny klucza podstawowego w tabeli porównawczej.