| Autor | Zpráva | ||
|---|---|---|---|
| Rfilip Profil |
#1 · Zasláno: 28. 7. 2015, 19:49:07
Mám potíže s optimalizací tohoto dotazu který trvá 13s:
SELECT `user_id`,`op`, COUNT(*) AS `count` FROM `imported_customer`
WHERE (`id` IN (
SELECT `imported_customer_id` FROM `history`
WHERE `date` < NOW() - INTERVAL 10 DAY
GROUP BY `imported_customer_id`
HAVING MAX(`event_type_id`)= 2
)
)
GROUP BY `op`
ORDER BY `user_id`, `op`Výstup EXPLAIN: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY imported_customer index op 35 1718 Using where; Using temporary; Using filesort 2 DEPENDENT SUBQUERY history index imported_customer_id 8 2 Using where SQLFiddle: http://sqlfiddle.com/#!9/02d37/2/0 -(bez nepoužitých sloupců tabulek) kvůli malému množství dat je dotaz rychlý ale explain je stejný Poddotaz je v pořádku, trvá jen 38ms, a výsledný seznam pro in operátor má jen kolem 300 čísel což dle dokumentace je rozumný počet. Jediné co zbývá je count(*), s GROUP a ORDER v primárním dotazu. Explain pro ten dotaz říká "Using where; Using temporary; Using filesort", což signalizuje neoptimální dotaz nebo špatné indexy. Na tabulce imported_customer jsou indexy pro id, user_id, op, a spojený user_id a op Ale ten dotaz je pořád pomalý,nevíte jak ho zrychlit? |
||
| Alphard Profil |
#2 · Zasláno: 28. 7. 2015, 21:48:53
Rfilip:
„a výsledný seznam pro in operátor má jen kolem 300 čísel“ id v imported_customer je primární klíč, tj. unikátní. Takže po fázi select ... where by se mělo pracovat se sadou maximálně 300 záznamů. Nezdá se mi, že by tohle server zpracovával 13 s. To musí zvládnout řádově rychleji i bez indexů.
Zkus, jak dlouho počítá SELECT `user_id`,`op` FROM `imported_customer`
WHERE (`id` IN (
SELECT `imported_customer_id` FROM `history`
WHERE `date` < NOW() - INTERVAL 10 DAY
GROUP BY `imported_customer_id`
HAVING MAX(`event_type_id`)= 2
)
) a pokud to trvá dlouho, dosaď za ten poddotaz SELECT `user_id`,`op` FROM `imported_customer` WHERE (`id` IN (1, 2, 3)) Mně se totiž v tom explainu nelíbí to DEPENDENT SUBQUERY. (I rychlý poddotaz, mnohokrát opakovaný, bude dělat problémy.)
|
||
| Keeehi Profil |
#3 · Zasláno: 28. 7. 2015, 22:45:56
To WHERE IN se dá přepsat jako INNER JOIN
SELECT `user_id`,`op`, COUNT(*) AS `count` FROM (
SELECT `imported_customer_id` AS `id` FROM `history`
WHERE `date` < NOW() - INTERVAL 10 DAY
GROUP BY `imported_customer_id`
HAVING MAX(`event_type_id`)= 2
) AS t
INNER JOIN `imported_customer`
USING(`id`)
GROUP BY `op`
ORDER BY `user_id`, `op`Nejsem databázový expert ale pokud se ten select opravdu vyhodnocuje pro každý řádek (nevidím důvod proč ale dejme tomu) tak pokud se to přepíše takto, tak už by se to dít nemělo. |
||
| Kajman Profil |
#4 · Zasláno: 28. 7. 2015, 22:49:35
Souhlasím s Keeehi, v mysql se také většinou snažím vyhnout konstrukcím
where id in (poddotaz) join (poddotaz) p on id=p.imported_customer_id |
||
| Rfilip Profil |
#5 · Zasláno: 28. 7. 2015, 23:29:44
Díky všem, přepsání WHERE IN na JOIN pomohlo, čas na dotaz klesnul z 13s na neznatelných 10ms
|
||
| Keeehi Profil |
#6 · Zasláno: 28. 7. 2015, 23:50:31
Kajman:
Víš, proč to ten select uvnitř where počítá pro každý řádek? Nevidím tam nic závislého. Jediné, co se může měnit je výstup z funkce now(). Pokud je to tím, určitě by pomohlo tu hodnotu si tam dostadit už při generováníí dotazu (předpokládám asi v PHP). Pokud bychvšak chtěl jen čistě db řešení, pomohlo by, kdybych si jakoprvní tu hodnotu uložil do proměnné a v dotazu používal jen už tu proměnnou? |
||
| Alphard Profil |
http://stackoverflow.com/a/25838054
http://plosquare.blogspot.cz/2012/10/uncorrelated-subqueries-treated-as.html http://dev.mysql.com/doc/refman/5.6/en/subquery-restrictions.html http://sarahdba.blogspot.cz/2009/08/examples-of-bad-queries.html Je to prostě chyba. Ale zajímalo mě, proč se objevila tady, když http://sqlfiddle.com/#!9/02d37/2/0 je ok. Jiná verze (tam je 5.6)? Něco zásadního při zjednodušování? Proto jsem to taky chtěl trochu pitvat a neuvedl jsem hned zřejmé řešení :-) Pro verzi 5.5 už je tam také DEPENDENT SUBQUERY - http://sqlfiddle.com/#!2/02d37/1. Když jsem psal [#2], tak jsem to nevěděl.
|
||
|
Časová prodleva: 9 let
|
|||
0