Autor Zpráva
Rfilip
Profil
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
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
      )
) 
(tohle, pokud se nemýlím, může vysypat maximálně 300 záznamů)

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
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
Souhlasím s Keeehi, v mysql se také většinou snažím vyhnout konstrukcím
where id in (poddotaz)
se kterými má občas plánovač výknostní problémy a raději to píšu jako
join (poddotaz) p on id=p.imported_customer_id
Rfilip
Profil
Díky všem, přepsání WHERE IN na JOIN pomohlo, čas na dotaz klesnul z 13s na neznatelných 10ms
Keeehi
Profil
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.

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: