Efektívne na MySQL, časť druhá

Dnes by sme vám radi priniesli pokračovanie nášho minulého článku Efektívne na MySQL. V tejto druhej časti sa budeme venovať použitiu subquery, ktorý je niekedy dobrou alternatívou k JOIN. Práve nevhodné použitie subquery môže niekedy veľmi ovplyvniť načítavanie dát z vašej databázy.

Subquery sa dá podobne ako JOIN použiť na výber z viacerých tabuliek, avšak často sa stretávam s tým, že je použité na mieste, kde by oveľa lepšie fungoval JOIN.

O čo konkrétne ide?

Syntax subquery je oveľa zrozumiteľnejšia pre “voľné oko“, avšak MySQL nižších verzií má často problém takúto query optimalizovať a vo výsledku vykonáva zbytočne veľa úkonov. Dajme si pre ilustráciu takúto situáciu:

Máme tabuľku pivo, kde je číslo identifikujúce to-ktoré pivo a názov piva. V druhej tabuľke s názvom bar máme záznamy o tom, aké pivá majú v rôznych baroch.

Čiže v databáze sú dve tabuľky: tabuľka pivo so stĺpcami pivo.id a pivo.názov a tabuľka foo so stĺpcami foo.id , foo.bar_name, foo.pivo_známka a foo.pivo_id. Samozrejme, pre väčší počet operácií sa nám neoplatí porovnávať reťazce znakov, keď môžeme hľadať podľa čísla. Hodnoty v stĺpci pivo.id teda korešpondujú s hodnotami foo.pivo_id a poskytujú prepoj medzi tabuľkami.

Ak chceme vybrať všetky bary, ktoré majú pivo s názvom ‚Pivo konzumné‘, máme na výber viacero možností. Syntax príkazu JOIN je na prvý pohľad mierne mätúca, preto je bližšia intuitívna možnosť so subquery.

SELECT foo.bar_name, foo.pivo_známka FROM bar WHERE foo.pivo_id IN ( SELECT id FROM pivo WHERE pivo_nazov = 'Pivo konzumne' );

Alebo vyber všetky bary a známku, ktorú ich pivo dostalo, také, že majú id také isté ako tie, v ktorých majú pod týmto id aj ‚Pivo konzumné‘. Výsledok je taký istý, ako keby sme zavolali:

SELECT foo.bar_name, foo.pivo_znamka FROM foo
INNER JOIN pivo ON foo.pivo_id=pivo_id
WHERE pivo.nazov='Konzumne pivo' ;

V čom je teda rozdiel?

Pri použití subquery sa použije iný prístup: MySQL v takomto prípade musí vykonávať subquery (t.j. zistiť, aký názov zodpovedá pivo_id ) pre každý jeden záznam. V tomto prípade by situácia nemusela byť taká katastrofálna, MySQL je na prehľadávanie veľkého objemu relačných dát stavaná, preto takýchto operácií zvláda naraz obrovský počet bez toho, aby sa muselo na výsledok čakať. Horšie by napr. bolo, ak by sme vyhľadávali opačne, t.j. hľadali by sme podľa názvu piva v tabuľke pivo jeho pivo_id. Ak by sme napríklad museli porovnávať reťazec dlhý 10 znakov, už by query prebehla znateľne pomalšie.

Pri použití syntaxe typu JOIN za použitia indexu si vie jeho vstavaný query optimizer zjednodušiť prácu tak, že nevyhľadáva z karteziánskeho súčinu, (t.j. tabuľka 2 riadky, 2 stĺpce s tabuľkou 3×4 dá na spracovanie tabuľku 6×8), ale z indexov, a tak sa operácia zjednoduší na malý počet SELECT-ov medzi 2-stĺpcovými medzitabuľkami, a výsledné prehľadávanie zaberie rádovo menej operácií. Pri tabuľke s 50 záznamami to zrejme nepocítite, avšak počet operácií s rozsahom tabuľky exponenciálne rastie.

Väčšina CMS či iných webových aplikácií, vyvíjaných profesionálnym tímom, už podobné muchy dávno vychytala, to však nemusí platiť o tvorcoch pluginov pre ne. Tak môže napr. widget, ktorý ponúka náhodných 5 produktov z ponuky pri rozšírení portfólia e-shopu, spomaliť načítanie aj o niekoľko sekúnd. Skillerom je isto jasné, že tieto rady treba brať s rezervou, sú iba dobrým „rule of thumb“. Budeme však radi, keď vám tieto rady aspoň trochu pomôžu pri vylaďovaní vašej databázy.