Dotaz: MySQL a PHP - jak jsou rychlé poddotazy

[tobik]

Mám následující situaci: tabulka s článkama, každý článek má id (primary key, auto_increment) a je zařazen do seriálu (sloupec serie). Mám vybraný článek, jeho id a id seriálu, do kterého je zařazen. A nyní potřebuju vypsat z databáze řekněme 2 články před a 2 články po daném článku včetně daného ze seriálu. Celkem 5, přičemž pokud bude vybraný článek první či poslední, budou vypsané jen 3, logicky. Něco podobného má třeba root.cz.

Sesmolil jsem následující SQL dotaz, který to udělá:

SELECT id, time, title,
(SELECT COUNT(*) FROM articles as pocitadlo WHERE serie=$serie_id AND articles.id>=pocitadlo.id) as poradi
FROM articles as articles
WHERE serie=$serie_id
HAVING poradi<=(SELECT COUNT(*) FROM articles as pocitadlo WHERE pocitadlo.id<=$article_id AND serie=$serie_id)+2 AND
poradi>=(SELECT COUNT(*) FROM articles as pocitadlo WHERE pocitadlo.id<=$article_id AND serie=$serie_id)-2
ORDER BY poradi ASC

Pokud jste si všimli, tak pro každý článek ze seriálu se vykoná poddotaz. Tedy pro n řádků n poddotazů (v praxi se bude počet pohybovat kolem 10-20). Jelikož nerozumím databázím zevnitř, jak jsou optimalizované, tak by mě zajímalo, jestli by nebylo třeba výkonově výhodnější vypsat jednoduchým dotazem všechny články seriálu a zbytek dopočítat pomocí PHP.

Samozřejmě uvítám, pokud někdo přijde s elegantnějším řešením

Odpovědi

[Josef Kufner]

Koukni se k cemu slouzi LIMIT a jak funguje.

Cislovani radku vysledku pak případně dělej až v aplikaci při načítání výsledku (ale obvykle to není potřeba a pokud chceš identifikovat řádek, je lepší tam nechat ID).

[Josef Kufner]

Aha, jsem to nečetl moc pozorně...

Zkus to buď rozdělit na dva jednoduché selecty, kde jeden vrátí předchozí a druhý následující:

SELECT a.*
FROM articles a
WHERE a.date >= (SELECT c.date WHERE c.id = $article_id)
   AND a.serie = $serie
ORDER BY a.date ASC
LIMIT 2

SELECT a.*
FROM articles a
WHERE a.date < (SELECT c.date WHERE c.id = $article_id)
   AND a.serie = $serie
ORDER BY a.date DESC
LIMIT 2

Nebo mít díly očíslované (sloupeček s pořadovým číslem dílu v seriálu) a pak použít jednoduchý (i když trošku neobvyklý) join:

SELECT a.*
FROM articles a, articles c
WHERE a.serie = $serie AND c.id = $article_id
   AND (a.poradi BETWEEN c.poradi - 2 AND c.poradi + 2)

[tobik]

Jasně, limitem to nejde, jinak bych se neptal.

Obojí řešení jsou použitelná a obojí jsem zvažoval. Obecně by mě zajímalo, zda je lepší psát složitější příkazy a provádět je najednou a nebo je dělit na jednodušší a provádět zvlášť. Dosud jsem se snažil omezovat počet přístupů k SQL co nejméně (i proto, že výsledný php kód je pak přehlednější), proto jsem udělal tu dlouhou verzi. Samozřejmě, pokud těch článků bude hodně, tak se vyplatí ty dva, ale co je hodně? 10, 100 nebo 1000 řádků? To je jedna z věcí, která by mě zajímala.

Jinak druhý způsob by znamenal změnit zapisování článků do databáze. Při každém přesunu článku či smazání by se muselo pořadí přepočítávat, což mi přišlo zbytečně komplikované.

[Josef Kufner]

Ono je nejlepší si na to pustit explain a kouknout se. To co tam máš ty znamená spustit subselect pro každý řádek. Ten můj se spustí jen jednou pro celý dotaz (nezávisí to na řádku, je to konstanta).

Složitější příkazy se občas vyplatí a občas ne. Něco je lepší udělat nadvakrát pomocí triviálních dotazů než vyrábět cosi komplikovaného. Jiné věci se vyplatí poskládat v DB, protože by se to jinde skládalo těžko.

Taky je potřeba brát v úvahu kešování -- pokud se část složitého dotazu mění a část je stále stejná, vyplatí se to rozdělit a umožnit tak kešování aspoň části dotazu. Pokud máš složité joiny nad spoustou dat a použiješ LIMIT, vyplatí se LIMIT schovat do subselectu a joinovat k olimitovanému mezivýsledku. Atd, atd,... Úspory po takovýchto úpravách jsou i ve stovkách procent.

Přepočítávání při přesunu sice je trošku složitější, ale ušetří práci při zobrazování, což je daleko častější operace.

[tobik]

Explain zkusím, děkuji za tip.

Zajímalo by mě, kolik času (procentuálně) zabere volání dotazu pomocí php. Pokud by odeslání dotazu zabralo řekněme 80% času a samotné vykonání v mysql jen 20%, tak by se do určité míry vyplatilo používat spíš poddotazy, než volat 2x mysql_query.