MySQL-SELECT WHERE field IN (subquery) - Extremely slow why?
Mam kilka duplikatów w bazie danych, które chcę sprawdzić, więc co zrobiłem, aby zobaczyć, które są duplikaty, zrobiłem to:
SELECT relevant_field
FROM some_table
GROUP BY relevant_field
HAVING COUNT(*) > 1
W ten sposób uzyskam wszystkie wiersze z relevant_field występujące 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 relevant_field 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
)
To okazuje się być bardzo powolne z jakiegoś powodu (zajmuje to kilka minut). Co tu się dzieje, żeby było tak wolno? relevant_field jest indeksowane.
W końcu próbowałem stworzyć widok "temp_view" z pierwszego zapytania (SELECT relevant_field FROM some_table GROUP BY relevant_field HAVING COUNT(*) > 1)
, a następnie zrobić 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.
Jacyś eksperci SQL, którzy mogą wyjaśnić o co chodzi?
10 answers
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_field
musi być w select, ponieważ w przeciwnym razie klauzula having
Da błąd, ale nie jestem w 100% pewien
Nigdy nie używaj IN
z zapytaniem podrzędnym; jest to notorycznie powolne.
Używaj tylko IN
ze stałą listą wartości.
Więcej porad
- Jeśli chcesz szybciej wykonywać zapytania,
nie rób
SELECT *
tylko wybierz pola, których naprawdę potrzebujesz. - upewnij się, że masz indeks na
relevant_field
, aby przyspieszyć equi-join. - upewnij się, że
group by
na głównym kluczu. - Jeśli korzystasz z InnoDB i wybierasz tylko zindeksowane pola (a sprawy nie są zbyt skomplikowane) , to MySQL rozwiąże Twoje zapytanie używając tylko indeksów, przyspieszając sprawy.
Ogólne rozwiązanie dla 90% zapytań IN (select
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)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-05-26 09:59:32
Zapytanie podrzędne jest uruchamiane dla każdego wiersza, ponieważ jest to zapytanie skorelowane. Można utworzyć skorelowane zapytanie w nie-skorelowane zapytanie, wybierając wszystko z zapytania podrzędnego, jak TAK:
SELECT * FROM
(
SELECT relevant_field
FROM some_table
GROUP BY relevant_field
HAVING COUNT(*) > 1
) AS subquery
Ostateczne zapytanie wyglądałoby tak:
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
)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-05-27 21:18:27
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:18:23
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 Twoje zapytanie w jednej z moich baz danych, a także próbowałem przepisać je jako połączenie do Pod-zapytania.
To działało dużo szybciej, spróbuj!
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-05-26 09:08:05
Sformatowałem Twoje powolne zapytanie sql z www.prettysql.net
SELECT *
FROM some_table
WHERE
relevant_field in
(
SELECT relevant_field
FROM some_table
GROUP BY relevant_field
HAVING COUNT ( * ) > 1
);
Gdy używasz tabeli zarówno w zapytaniu, jak i zapytaniu podrzędnym, powinieneś zawsze używać aliasów obu, w następujący sposób:
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
);
Czy to pomaga?Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-05-26 08:23:10
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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-04-04 10:33:40
Czasami, gdy dane rosną większe mysql, gdzie IN może być dość powolny z powodu optymalizacji zapytań. Spróbuj użyć STRAIGHT_JOIN, aby powiedzieć mysql, aby wykonać zapytanie tak, jak jest, np.
SELECT STRAIGHT_JOIN table.field FROM table WHERE table.id IN (...)
Ale uwaga: w większości przypadków mysql optimizer działa całkiem dobrze, więc polecam go używać tylko wtedy, gdy masz tego rodzaju problem
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-03-10 12:39:40
Jest to podobne do mojego przypadku, gdzie mam tabelę o nazwie tabel_buku_besar
. What I need are
Szukamy rekordów, które mają
account_code='101.100'
wtabel_buku_besar
które mającompanyarea='20000'
a takżeIDR
jakocurrency
-
Muszę pobrać wszystkie rekordy z
tabel_buku_besar
, które mają account_code taki sam jak krok 1, ale mają {[8] } w wyniku kroku 1
Podczas korzystania z select ... from...where....transaction_number in (select transaction_number from ....)
moje zapytanie działa bardzo wolno i czasami powoduje przerwę w żądaniu lub sprawia, że moja aplikacja nie odpowiadam...
Próbuję tej kombinacji i wyniku...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`
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-09-25 04:08:07
Uważam, że jest to najskuteczniejsze do znalezienia, jeśli wartość istnieje, logika może być łatwo odwrócona, aby znaleźć, jeśli wartość nie istnieje (ie 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
*Zamień relevant_field na nazwę wartości, którą chcesz sprawdzić w tabeli
*Zastąp klucz podstawowy nazwą kolumny klucza podstawowego w tabeli porównawczej.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-11-30 11:51:48
Najpierw można znaleźć zduplikowane wiersze i znaleźć liczbę wierszy jest używana ile razy i uporządkować ją 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ń dublicate rows.No to początek 0. Oprócz numeru pięści każdej grupy usuń wszystkie wiersze dublicate.
delete from CopyTable where No!= 0;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-08-21 12:53:13