Zajímavé. Já bych toto řešení preferoval i z jiného důvodu - možnost použití Dependency Injection. Jinak se navíc závislost na čase rozloží zbytečně do dvou míst.
replied by [3] Dundee
MySQL: úskalí použití časových funkcí v dotazech
Při práci jsem narazil na jednu velmi nepěknou vlastnost časových funkcí v MySQL. Jelikož se návratová hodnota těchto funkcí v čase liší, neukládá si MySQL výsledky dotazů s těmito funkcemi do query cache.
A to ani v případě, že použijeme funkci, která mění výstup jen jednou denně (CURDATE). Pokud použijeme časovou funkci ve složitějším dotazu (např. se seskupováním) dočkáme se razantního poklesu výkonu aplikace.
Toto je dotaz, kterým jsem načítal články na titulku blogu:
SELECT p.*, c.`name` AS cathegory_name, count(`id_comment`) AS num_of_comments FROM `nors4_post` AS p LEFT JOIN `nors4_cathegory` AS c USING (`id_cathegory`) LEFT JOIN `nors4_comment` AS co USING (`id_post`) WHERE p.`active` = 1 AND p.`date` <= CURDATE() GROUP BY `id_post` ORDER BY `date` DESC LIMIT 0,5
Samotný dotaz trvá celých 0,9133 sekundy. Z toho pak vyplývá i vyřízení pouhých 1,66 dotazu za sekundu a celkový běh skriptu 0,9555 sekundy.
Pokud ale nahradíme MySQL funkci za PHP funkci, která bude měnit výstup jen jednou za den, bude se moci query cache opět použít a výkon opět vzroste.
"SELECT p.*, c.`name` AS cathegory_name, count(`id_comment`) AS num_of_comments
FROM `nors4_post` AS p
LEFT JOIN `nors4_cathegory` AS c USING (`id_cathegory`)
LEFT JOIN `nors4_comment` AS co USING (`id_post`)
WHERE p.`active` = 1 AND p.`date` <= '" . date("Y-m-d") . "'
GROUP BY `id_post`
ORDER BY `date` DESC
LIMIT 0,5"
Upravený dotaz bude při druhém dotazu běžet pouhých 0,0002 vteřiny, a tak maximální počet dotazů vzroste na příjemných 41,57 za sekudnu a celková doba běhu aplikace spadne na 0,0388s.
evaluation
comments
2009-02-08 12:14:39
2009-02-08 13:26:40
#1 naniccz: Prepared statements na tom obecně nejsou s výkonem moc dobře. Vyplatí se pouze z pohledu bezpečnosti. Viz http://blog.milde.cz/post/213-mysql-bindovat-ci-nebindovat-/
#2 v6ak: Dependency injection? Mohl bys to přiblížit? Myslel jsem, že dependency injection spočívá v podstrčení komponenty, která má neočekávanou implementaci.
2009-02-08 16:20:57
V uvedeném příkladě je ale jeden zásadní problém, ne zřídka bývá stroj (rozuměj počítač, server) zpracovávající HTTP požadavky a vykonávající PHP skripty fyzicky jiným strojem než je stroj poskytující MySQL. V tu chvíli se časové údaja na obou z nich mouhou lišit. I v případě že se liší jen o několik vteřin to může být dosti zásadní problém, pokud spustíte operaci spoléhající na přesné datum (například generování nějakých statistik o půlnoci), pak datum CURDATE() a date("Y-m-d") může mít na obou strojích úplně jiné hodnoty tím mohou vzniknout nemalé problémy.
Jediné řešení je spolehnout se buď pouze na PHP nebo pouze ma MySQL - kombinace obou je špatná a potenciálně nebezpečná!
replied by [5] Dundee
2009-02-08 16:33:34
2009-02-08 16:58:12
#5 Dundee: Snažím se jen upozornit na to, že mnohdy nejde jen o výkon ale i o bezpečnost správnou funkcionalitu.
2009-02-08 18:27:20
Není skoro jedna vteřina trochu moc na takový dotaz? Jsou tam nastavené indexy?
replied by [10] Dundee
2009-02-08 19:50:12
ad dependency injection - jde o to, ze pri pouziti curdate() se bude tezko zkouset, jak se dotaz zachova pro nejaky konkretni den (ne jen pro "dnesek") - jakmile to volani curdate() nahradime nejakym menitelnym parametrem, muzeme volani lepe testovat - o tom cele DI je
ad rychlost - dotaz je postaveny nestastne, protoze pro kazdy radek toho joinu se "vypocitava" aktualni datum znovu, proto to tak dlouho trva - navic je to spatne, protoze kazde porovnani vlastne probiha s trosku jinym datem - kdyby ten dotaz bezel 5 minut, byly by prvni hodnoty porovnavany proti 00:00 a posledni proti 00:05 - v tomhle pripade to samozrejme nevadi, ale koncepcne je to chyba - vztazne datum by melo byt konstantni = parametr
btw slovo "cathegory" neexistuje ;)
replied by [10] Dundee
replied by [12] Jakub Vrána
2009-02-08 21:55:56
to Marek:
To se me nejak nezda, ze by pro kazdy radek mysql znovu vracelo CURDATE(). Takovou zakladni optimalizaci pri predzpracovani dotazu by snad mel DB stroj mit, ze fci, ktera vraci "konstantu", proste nahradi hodnotou. A pak teprve dotaz provede. Pokud to tak neni, tak me vyvedte z omylu. Pravdou je, ze ten temer sekundovy rozdil tomu napovida.
replied by [10] Dundee
2009-02-08 22:35:50
#7 Bohdan: Ano, indexy tam jsou.
#8 Marek: #9 ToM: Dotaz trvá takto dlouho překvapivě kvůli závěrečnému třídení. A to i přesto, že je nad sloupcem date index.
replied by [11] Dundee
2009-02-08 22:59:58
#10 Dundee: Oprava: Kvůli třídění a seskupení. Pokud dotaz obsahuje alespoň jedno z toho, neskončí pod 0,9s.
2009-02-17 22:00:25
#8 Marek: Datové funkce se vyhodnocují jen jednou za dotaz, jak je jasně uvedeno na http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
Velmi podobný příklad je z pohledu indexů rozebrán na http://php.vrana.cz/ukazka-pouziti-indexu.php
replied by [13] Dundee
2009-02-24 01:12:06
#12 Jakub Vrána: Díky za trknutí Jakube, po přidání dalšího indexu se dotaz desetkrát zrychlil.
Zajímalo by mne, jak by se to chovalo při použití prepared statementu. (jestli při použití CURDATE by to bylo také pomalé)
díky