Spojování tabulek
Školení, která pořádám 
Článek vyšel v rámci PHP okénka na serveru Root.cz.
Důležitým obratem při práci s databází je spojování tabulek. Jak už název napovídá, spojování tabulek slouží ke spojení několika tabulek, které jsou v nějakém vztahu (např. master-detail) podle zadaného kritéria.
Dejme tomu, že máme tabulku s výrobky a tabulku se skupinami, do kterých tyto výrobky patří. A dejme tomu, že chceme vypsat přehled všech výrobků spolu se skupinou, do které patří (např. jako výsledek vyhledávání). Na vyřešení tohoto úkolu se ideálně hodí spojování tabulek.
<?php // odstrašující řešení $result = mysql_query("SELECT id, skupina, nazev FROM vyrobky WHERE podminka"); while ($row = mysql_fetch_assoc($result)) { $skupina = mysql_result(mysql_query("SELECT nazev FROM skupiny WHERE id = $row[skupina]"), 0); echo "<a href='?id=$row[id]'>$row[nazev]</a> ($skupina)<br />\n"; } mysql_free_result($result); // za určitých okolností přijatelné řešení $skupiny = array(); // array(id => nazev) $result = mysql_query("SELECT id, nazev FROM skupiny"); while ($row = mysql_fetch_assoc($result)) { $skupiny[$row["id"]] = $row["nazev"]; } mysql_free_result($result); $result = mysql_query("SELECT id, skupina, nazev FROM vyrobky WHERE podminka"); while ($row = mysql_fetch_assoc($result)) { echo "<a href='?id=$row[id]'>$row[nazev]</a> (" . $skupiny[$row["skupina"]] . ")<br />\n"; } mysql_free_result($result); // správné řešení $result = mysql_query(" SELECT vyrobky.id, vyrobky.nazev, skupiny.nazev AS skupina_nazev FROM vyrobky INNER JOIN skupiny ON vyrobky.skupina = skupiny.id WHERE podminka "); while ($row = mysql_fetch_assoc($result)) { echo "<a href='?id=$row[id]'>$row[nazev]</a> ($row[skupina_nazev])<br />\n"; } mysql_free_result($result); ?>
První řešení je odstrašující proto, že v každém průchodu cyklem se znovu klade dotaz do databáze, což je drahá (pomalá) operace. Druhé řešení je ideální v případě, kdy se proměnná $skupiny využije i k jinému účelu (např. k výpisu všech skupin v navigaci).
Při spojování tabulek se vyplatí dodržovat několik zásad:
- Všechny sloupce by měly být pomocí tečkové notace uvozeny tabulkou, ke které se vztahují. Předejde se tím problémům při přidání stejnojmenného sloupce do druhé tabulky.
- Ze stejného důvodu je lepší se vyhnout zápisu
SELECT *. Pokud chcete získat většinu sloupců z jedné tabulky, můžete použítSELECT tabulka.*. - Pro spojení tabulek se dá používat i zápis
FROM vyrobky, skupiny WHERE vyrobky.skupina = skupiny.id. Tento zápis osobně nemám rád, protože dochází ke smíchání spojovacích a ostatních podmínek, což je nepřehledné obzvláště při větším množství spojovaných tabulek. - Existuje i typ spojení NATURAL JOIN, který spojí tabulky podle shodných hodnot stejnojmenných sloupců, ten ale vyžaduje volit pojmenování sloupců tak, aby vzájemně nekolidovaly (tedy např.
vyrobky(id_vyrobek, id_skupina, v_nazev),skupiny(id_skupina, s_nazev)), což názvy sloupců zbytečně komplikuje.
Kromě INNER JOIN existuje ještě typ spojení LEFT JOIN, které se liší tím, že pokud v pravé tabulce není nalezen žádný odpovídající řádek, tak se místo něj dosadí hodnoty NULL. Tento typ spojení se dá využít v situaci, kdy nemáme jistotu, že v pravé tabulce bude existovat alespoň jeden odpovídající záznam, tedy např. pokud výrobek nemusí mít nutně přiřazenou nějakou skupinu.
<?php $result = mysql_query(" SELECT vyrobky.id, vyrobky.nazev, skupiny.nazev AS skupina_nazev FROM vyrobky LEFT JOIN skupiny ON vyrobky.skupina = skupiny.id WHERE podminka "); while ($row = mysql_fetch_assoc($result)) { echo "<a href='?id=$row[id]'>$row[nazev]</a> (" . (isset($row["skupina_nazev"]) ? $row["skupina_nazev"] : "bez skupiny") . ")<br />\n"; } mysql_free_result($result); ?>
Spojování tabulek se dá využít i ve složitějších případech. Řekněme, že chceme vypsat postupně všechny skupiny se všemi jejich výrobky.
<?php // přehledné, ale neefektivní $result = mysql_query("SELECT * FROM skupiny ORDER BY nazev"); while ($row = mysql_fetch_assoc($result)) { echo "<h3>$row[nazev]</h3>\n"; $result1 = mysql_query("SELECT * FROM vyrobky WHERE skupina = '$row[id]'"); while ($row1 = mysql_fetch_assoc($result1)) { echo "<a href='?id=$row1[id]'>$row1[nazev]</a><br />\n"; } mysql_free_result($result1); } mysql_free_result($result); // náročné na paměť $vyrobky = array(); // array(skupina => array($row, ...), ...) $result = mysql_query("SELECT id, skupina, nazev FROM vyrobky ORDER BY skupina"); while ($row = mysql_fetch_assoc($result)) { $vyrobky[$row["skupina"]][] = $row; } mysql_free_result($result); $result = mysql_query("SELECT * FROM skupiny ORDER BY nazev"); while ($row = mysql_fetch_assoc($result)) { echo "<h3>$row[nazev]</h3>\n"; if (isset($vyrobky[$row["id"]])) { foreach ($vyrobky[$row["id"]] as $row1) { echo "<a href='?id=$row1[id]'>$row1[nazev]</a><br />\n"; } } } mysql_free_result($result); // méně přehledné, ale efektivnější $skupina_prev = 0; $result = mysql_query(" SELECT skupiny.*, vyrobky.id AS vyrobky_id, vyrobky.nazev AS vyrobky_nazev FROM skupiny LEFT JOIN vyrobky ON skupiny.id = vyrobky.skupina ORDER BY skupiny.nazev, skupiny.id "); while ($row = mysql_fetch_assoc($result)) { if ($skupina_prev != $row["id"]) { // začíná nová skupina, vypíšeme nadpis echo "<h3>$row[nazev]</h3>\n"; $skupina_prev = $row["id"]; } if (isset($row["vyrobky_id"])) { // byl nalezen odpovídající výrobek echo "<a href='?id=$row[vyrobky_id]'>$row[vyrobky_nazev]</a><br />\n"; } } mysql_free_result($result); ?>
Vzhledem k tomu, že u třetího způsobu není na první pohled vůbec patrné, co kód dělá, zvolil bych ve většině případů spíše první nebo druhý způsob (první pokud je málo skupin a hodně výrobků, druhý v opačném případě). Třetí případ bych tedy zvolil asi jen v situaci, kdy databázový server běží na jiném stroji než webový server a cena každého dotazu do databáze je opravdu vysoká.
Přijďte si o tomto tématu popovídat na školení Návrh a používání MySQL databáze.
Diskuse
RATMex B:
Čo sa týka zásady č. 1, tak nemôžem súhlasiť. V záujme efektívnej práce, prehľadnosti a rýchlosti by mala byť vždy databáza v normálnej forme. A v nej sú kolízie názvov stĺpcov vylúčené.Už len z toho dôvovdu, že by mi náhodou daná query vyhodila chybu viacznačnosti stĺpca tam názvy tabuliek neuvediem, aby som tak bol upozornený na nie normálnu formu DB a mohol vykonať normalizáciu.
Jakub Vrána 
:
To je pro mě novinka. Kolikátou normální formu máš na mysli a z jakého zdroje vycházíš? Zajistit bezkoliznost sloupců v důsledku znamená prefixovat je celým názvem tabulky (protože zvolit jednopísmenné prefixy jde jenom u databází s pár tabulkami), což je mnohem větší pakárna než použít tečkovou notaci pouze u dotazů s více tabulkami.
Jakub Vrána : RATMex B:
Samozrejme, že to nie je povinné, ale je to slušný nástroj, ako si kontrolu redundnatnosti vykonávať. Ked som neuviedol presnejšie tak som samozrejme prepokladal piatku normamálnu formu, ale z hľadiska objektu záujmu postačí na dané pravidlo aj Boyce-Codd normálna forma.Nie je vôbec nutné prefixovať každý stĺpec, ale iba tie, ktoré majú rovnaké názvy (datum a pod.), z toho titulu, aby boli jedinecne identifikovane. Iba vzhľadom na výhody tohto prístupu zavrhujem absolútnu vhodnosť bodu 1.
Jakub Vrána :
Považuji to za nesmysl z několika důvodů:
Dopředu nevím, co by třeba v budoucnu mohlo kolidovat, takže stejně budu muset prefixovat všechno.
Prefixy mi zbytečně znepřehlední i dotazy používající jedinou tabulku (INSERT, UPDATE, jednoduché SELECT).
Jediný rozdíl je v tom, že místo prefixu "tabulka." použiji "tabulka_".
Výhoda v tom, že tento způsob mi neumožní psát dotazy s více tabulkami bez prefixování sloupců, které by v budoucnu mohlu kolidovat, je malá. Navíc s příhlednutím k tomu, že stejně doporučuješ prefixovat jenom sloupce, které by mohly kolidovat.
Jakub Vrána : RATMex B:
Keď už tu zase musíš vyťahovať prehľadnosť (ako to máš vo zvyku), tak sa v tomto mýliš. Tvojim spôsobom budú neprehľadné všetky query, kde sa spájajú tabuľky, len aby si ušetril na qury ostatných avšak z mojich skúseností viem, že prvý spomínaný typ sa v projektoch vyskytuje omnoho častejšie. Spôsobom, ktorý som napísal ja, bude "neprehľadných" iba pár stpĺcov (minimum) v celej DB a teda vo všetkých query.Porovnaj si nasledujúce prípady (redukované iba na gro), kde sa v DB napr. duplikujú `login` (aj v tabuľke používateľov) a `date` (aj v tabuľke príspevkov diskusie):
Prípad A (Podľa Tvojich zásad):
Tabuľky:
user (id, login, privilege)
category (id, category)
thread (id, category, thread)
post (id, user, thread, date)
query 1:
SELECT id, login, privilege FROM user
query 2:
SELECT thread.id, category.category, thread.thread, MAX(post.post_date) AS last_date
FROM category
LEFT JOIN thread ON category.id = thread.category
LEFT JOIN post ON thread.id = post.thread
GROUP BY thread.id
Prípad B:
Tabuľky:
user (id_user, user_login, privilege)
category (id_category, category)
thread (id_thread, id_category, thread)
post (id_post, id_user, id_thread, post_date)
query 1:
SELECT id_user, user_login, privilege FROM user
query 2:
SELECT thread.id_thread, category, thread, MAX(post_date) AS last_date
FROM category
LEFT JOIN thread USING (id_category)
LEFT JOIN post USING (id_thread)
GROUP BY thread.id_thread
Dokonca je možné použiť aj spomínaný NATURAL JOIN, ak sa jedná o verziu MySQL, kde je už konečne podporovaný, teda:
SELECT thread.id_thread, category, thread, MAX(post_date) AS last_date
FROM category
NATURAL LEFT JOIN thread
NATURAL LEFT JOIN post
GROUP BY thread.id_thread
Apropo, prehľadnosťou sa zaoberá štábna kultúra, ba priam existuje preto, aby boli práce od cudzích/iných ľúdí čo najprehľadnejšie a hlavne čo najjednotnejšie. Ako existuje štábna kultúra na písanie kódu v C, či PHP, a in7ch jazykoch, tak existuje aj štábna kultúra na návrh databázového systému a teda aj pomenovania tabuliek a stĺpcov databáz čo je súčasťou daného procesu návrhu.
Čítal som Tvoj článok o návrhu databázy a nepadlo o tom ani slovo. To, že ju nedodržujes je tiež jasné aj z tohto článku a odporučení a tiež aj zo všetkých príkladov v ostatných článkoch.
Mimochodom, NATURAL JOIN existuje v databázových procesoroch hlavne kvôli tomu, že DB vytvorené touto štábnou kultúrou dodržujú konkrétne pravidlá, tie využíva a preto je možné ho použiť iba nad DB ktorá bola na základne týchto pravidiel vytvorená, aby bol zápis query ešte prehľadnejší.
Jakub Vrána :
Bez jakékoliv ironie - pěkný rozbor.
Důležitý moment podle mě nastává v okamžiku, kdy do existující aplikace chci přidat sloupec, který může kolidovat s nějakým již existujícím. Pak mám na výběr:
1. Prefixuji nový i stávající sloupec a předělám všechny dotazy, které ho využívají.
2. Prefixuji pouze nový sloupec, takže mi v aplikaci vznikne zmatek v tom, že některé sloupce prefixované jsou a jiné ne (zmatek už tam je, ale ještě se posílí v tom, že se budou lišit i sloupce stejného jména, takže si budu muset pamatovat, který sloupec v které tabulce prefix má a který ne).
3. Nechám mu kolizní jméno a v dotazech přidám tečkovou notaci.
Ani jedna možnost mě moc neláká. Když jsou naopak názvy sloupců bez jakýchkoliv prefixů a sloupce ve vícetabulkových dotazech důsledně prefixovány tečkovou notací, tak přidání sloupce nic nebrání.
Nebráním se samozřejmě kompromisnímu řešení v použití id_tabulka. To sice také není univerzálně použitelné pro NATURAL JOIN (např. odkaz na rodiče v diskusích se musí jmenovat jinak než primární klíč nebo odkaz na autora a posledního modifikátora článku nemůže být 2x id_user), ale popsanými nevýhodami netrpí.
Jakub Vrána : Jakub Vrána :
Mimochodem jsem rád, že jsi přešel na argumentování štábní kulturou a opustil od ohánění se BCNF případně 5NF, které s pojmenováním sloupců nemají nic společného.
Neměl bys odkaz na nějakou široce uznávanou štábní kulturu (v PHP je to třeba http://pear.php.net/manual/en/standards.php, v C zase http://pantransit.reptiles.org/prog/CodingStyle.html) třeba od nějaké standardizační organizace nebo výrobce databáze? Docela by mě to zajímalo.
Jakub Vrána : RATMex B:
V prípade pridávania kolízneho stĺpca do DB samozrejme nastáva možnosť 1, t.j. ak sa jedná o prvý výskyt kolízie a pôvodný stĺpec ešte prefix neobsahuje, tak treba prepísať všetky pôvodné query. S použitím správneho editora a techník je to otázka pár minút aj pre veľmi veľké projekty s veľkým množstvom súborov, vrátane zmeny bežiacej DB. Nakoľko sa taktéto operácie nad projektom vyskytujú iba veľmi občasne, tak je to menšie zlo raz za uhorský rok obetovať pár minút prepísaním všetkých skriptov ako mať celý čas kilometrové neprehľadné query a raz za uhorský rok tých pár minút ušetriť.Tu sa nejedná o argumentovanie štábnou kultúrou ale o podloženie niečoho čoho obhajujem. S prehľadnosťou si prišiel opäť Ty (ako vždy), ja sa iba prispôsobujem.
Vec sa má takto: BCNF je postačujúcou podmienkou na aplikáciu štábnej kultúry, teda štábna kultúra je nutnou podmienkou pre BCNF. Použitie štábnej kultúry považujem za samozrejmosť a preto o nej nikdy explicitne nehovorím. Avšak, ak mi nastane kolízia v názvoch stĺpcov, tak viem, že nastalo jej porušenie a teda nutne aj porušenie BCNF. V pôvodných príspevkoch som ten stredný krok vynechal.
Keď chceš odo mňa "odkaz na nějakou široce uznávanou štábní kulturu", tak Ťa sklamem. Už vôbec tým, že spájaš, pre mňa, dve nespojiteľné veci a hlavne nie presne definované. Niečo ako "široko uznávaná štábna kultúra" už v dnešnej dobe asi neexistuje a aj keby existovala, určite na ňu nenájdeš odkaz na internete. Problém je v počte ľudí a hlavne v charaktere ľudí, ktorý presadzujú každý nejakú vlastnú štábnu kultúru a tí najhorší z nich začínajú s "publikáciou" práve na internete. Stačí sa pozrieť autora kultúry v C, na ktorú si uviedol link v poslednom príspevku.
Moje návyky štábnej kultúry, ktoré som nadobudol, či už v C, Pascale, Lispe, JSM a iných a tiež aj v DB systémoch, poznám a som prebral ústnou, resp. osobnou formou od ľudí, ktorých hodnotím veľmi vysoko v daných oblastiach a ktorých rešpektujem na základe ich znalostí predovšetkým programátorských z hľadiska paradigmy a nie konkrétneho jazyku a tiež s ktorými som strávil veľa času, aby som si mohol danú mienku o nich vytvoriť. Mnohé z nich som tiež nabral v dobe, keď som ešte o internete ani nechyroval. Daní ľudia, aj kvôli veku, nabrali opäť svoje znalosti podobnou formou a hlavne z prostredia, ktoré bolo viac-menej ešte čisté a existovali v ňom iba praví programátori, v dobe keď si každý musel veľmi dobre uvedomovať presnú časovú aj pamäťovú náročnosť (nie, že dokúpim procák a RAM-ku a fičím, resp. veď komp to zvláda) a tiež dávať veľmi dobrý pozor na chyby (nie, že skopilujem, alebo spustím a chyba už vyskočí sama).
Ale to už trošku odbočujem. Záver je taký, že ak by si chcel vedieť akú štábnu kultúru používam pri návrhu DB systému, tak by si sa ju mohol dozvedieť jedine, tak, že by som Ti ju povedal ja, alebo ľudia, ktorí sú v tom istom "strome" ako ja, t.j. iný študenti človeka, od ktorého som to prebral, ako tiež on sám a jeho rovesníci, ktorí mali s nim spoločného učiteľa atď.
Jakub Vrána :
Můžeš mi uvést zdroj, který tvrdí, že štábní kultura je nutnou podmínkou pro BCNF?
A jaká přesně štábní kultura to má být, když ty sám tvrdíš, že žádný široce uznávaný standard neexistuje? Jak jsem uvedl, v PHP to jsou standardy PEAR, v C zase K&R případně zmiňované "Linux kernel coding style", jejichž autorem je pokud vím Linus - nevím, co jsi měl na mysli tou narážkou na autora. Pokud v SQL nic podobného neexistuje, tak tohle jsou tvá osobní pravidla a nemůžeš se jimi ohánět jako standardem nebo nutnou podmínkou pro BCNF.
Zdá se mi, že tvá tvrzení jsou ve sporu.
Jakub Vrána : RATMex B:
To vyplýva som samotného znenia danej štábnej kultúry.Tvrdím, že v dnešnej dobe už existuje asi ťažko široko uznávaný štadnard, aj kvôli tomu, že je to takmer nemožné zmonitorovať. Zastavám skôr pojem "široko uznávaný štardard konkrétnou skupinou ľudí". Čo sa týka PHP, tak tam sa jedná asi o výminku (potvrdzujúcu pravidlo), pretože tam hádam veľa kultúr neexistuje. Avšak strašne by ma zaujímalo, ako by si podložil "širokosť" uzávanosti Kernighan & Ritchie, či kultúry, na ktorý si poslal link (už len z pricípu, že sám si označil obe za šikoro uznáavaný štandard).
Inak, ja nehovorím o štandarde pre SQL, ale o štábnej kultúre pre názvoslovie pre databázové systémy.
NoiseR:
Zajima ma v souvislosti s timto tematem nasledujici - existuje nejaky nastroj, funkce, cokoliv, abych mohl vypsat spojene tabulky pomoci outer join v PHP tak, ze kdyz pripojuji k zaznamu z tab1 zaznamy v tab2 a v tab2 je na jeden zaznam vyhovujicich 5 zaznamu, ze bych je dostal na jeden radek? Ted mam na mysli graficky... Napriklad mam v tab1 jmena a v tab2 ke jmenum prirazene pujcene knihy. A ja chci vypsat prehled pujcenych knih podle osob tak, aby se jmeno osoby neopakovalo, tedy pokud bude mit jeden clovek pujceno pet knih, jeho jmeno tam bude jen jednou :) Uf, snad je to pochopitelne. Diky moc. Jakub Vrána :
Hledáš nejspíš funkci GROUP_CONCAT: http://php.vrana.cz/vypis-dat-ze-zavislych-…#group_concat.
Jakub Vrána : NoiseR:
Diky MOC, prozkoumam to.Štěpán Svoboda:
Ahoj Jakube,Po několika úmorných hodinách hledání a zkoušení bych Tě chtěl poprosit o malou radu při řešení následujícího problému:
Potřebuji spojit tři tabulky (discussion, comment, user) následujícím způsobem - přeloženo do lidské řeči:
Vyber diskuse, k nim připoj vždy *poslední* komentář a jak k diskusím tak ke komentáři připoj jména uživatelů. Umím bez problému spojit tyto tabulky do požadované podoby za pomoci aliasů:
SELECT discussion.*, user.Name AS UserName,
MAX(comment.Created) AS LastCommentCreated,
comment.Id AS LastCommentId,
comment.OwnerId AS LastCommentUserId,
user2.Name AS LastCommentUserName
FROM discussion
INNER JOIN user ON discussion.OwnerId = user.Id
LEFT JOIN comment ON discussion.Id = comment.DiscussionId
LEFT JOIN user user2 ON comment.OwnerId = user2.Id
WHERE discussion.SectionId = 1
GROUP BY discussion.Id
ORDER BY discussion.Modified DESC
LIMIT 5
Takto získám požadované diskuse včetně časů posledního příspěvku - MAX(comment.Created) - kvůli tomu se to také groupuje jinak to vrací každou diskusi tolikrát kolik je v ní komentářů. Zbylá tři pole (comment.Id, comment.OwnerId...) to vezme první z tabulky komentářů.
Pokud dám pryč MAX() a GROUP BY vrátí to také čas prvního komentáře, který potom koresponduje s hodnotami ostatních tří komentářových sloupců.
Doufám, že jsem to popsal přesně. Jde mi o to připojit k diskusi komentář s nejvyšším Created.
Laboroval jsem také s nějakými poddotazy ale nezdálo se mi to jako cesta správným směrem. Můžeš mne, prosím, nějak nakopnout?
Jakub Vrána :
Jedná se o tento problém: http://php.vrana.cz/ziskani-radku-s-maximalni-hodnotou.php
Poddotaz je správná cesta. Uživatele zanedbám, ti se dají snadno doplnit:
SELECT discussion.*, comment.OwnerId
FROM discussion
LEFT JOIN comment ON discussion.Id = comment.DiscussionId AND comment.Created
= (SELECT MAX(Created) FROM comment WHERE DiscussionId = discussion.Id);
Poddotaz vybírá MAX(Created) z komentářů dané diskuse. Protože však tato hodnota není unikátní, může vnější dotaz pro každou diskusi vrátit více řádek. To je potřeba ošetřit při výpisu. Pokud by se to mělo ošetřit už na úrovni SQL, přibyl by ještě jeden vnořený poddotaz.
Jakub Vrána : Štěpán Svoboda:
Tak se mi to zdařilo / nezdařiloUž dokážu dostat potřebná data k sobě ovšem přesně jak píšeš, vrací mi pro jednu jedno téma více max() záznamů (podle toho kolik je v tématu diskusí).
Napadlo mě GROUP BY theme.Id ovšem nedokážu přinutit databázi aby při groupnutí vzala ten nejnovější komentář z nejnovějších. Potřeboval bych záznamy setřídit ještě před groupnutím jestli mi rozumíš.
Už jsem přemýšlel i o tom ukládat si ThemeId přímo do komentáře. Potom by se nejnovější komentář z tématu našel snadno jedním joinem
SELECT theme.Id, comment.Id AS CommentId FROM theme
LEFT JOIN comment ON theme.Id = comment.ThemeId
AND comment.Created =
(
SELECT MAX(Created) FROM comment, theme
WHERE comment.ThemeId = theme.Id
)
Any ideas?
Jakub Vrána :
Ujasni si nejprve prosím terminologii. Tabulce discussion najednou říkáš téma a nazýváš ji theme, záznamům v tabulce comment říkáš diskuse.
Ukládat ThemeId ke komentáři nepovažuji za zrovna nejšťastnější. Jak jsem psal - dá se to vyřešit buď dalším poddotazem, ale vzhledem k tomu, že duplicitních záznamů bude minimum, tak mi přijde lepší si získat ve všechno a duplicity odstranit až při výpisu:
<?php
$last_discussion = 0;
while ($row = fetch()) {
if ($last_discussion == $row["id"]) {
continue;
}
$last_discussion = $row["id"];
}
?>
Jakub Vrána : 
Aitom:
Mám problém s rychlostí výběru dat při použití left join a větším počtu podmínek.
Dotaz:
SELECT items . * , itemsbelong . * , ta.text AS description, tb.text AS shortDescription
FROM items
INNER JOIN `itemsbelong` ON items.idItem = itemsbelong.idItem && itemsbelong.lang = 'cs' && itemsbelong.web = 'ithelp' && ( itemsbelong.category = 'sitove-prvky' || itemsbelong.category = 'sitove-prvky' || itemsbelong.category = 'adaptery' || itemsbelong.category = 'pci' || itemsbelong.category = 'pcmcia' || itemsbelong.category = 'power-line' || itemsbelong.category = 'prislusenstvi-k-adapterum' || itemsbelong.category = 'usb' || itemsbelong.category = 'anteny' || itemsbelong.category = '2-4ghz-sektorove' || itemsbelong.category = '2-4ghz-smerove' || itemsbelong.category = '2-4ghz-vsesmerove' || itemsbelong.category = '5-0ghz-sektorove' || itemsbelong.category = '5-0ghz-smerove' || itemsbelong.category = '5-0ghz-vsesmerove' || itemsbelong.category = 'kabely-pigtaily' || itemsbelong.category = 'konektory-bleskojistky' || itemsbelong.category = 'bezdratove-site' || itemsbelong.category = 'bluetooth' || itemsbelong.category = 'wi-fi-108mbit-2-4ghz' || itemsbelong.category = 'wi-fi-10mbit-5-8ghz' || itemsbelong.category = 'wi-fi-11mbit-2-4ghz' || itemsbelong.category = 'wi-fi-54mbit-2-4ghz' || itemsbelong.category = 'wi-fi-54mbit-5-8ghz' || itemsbelong.category = 'wi-fi-802-11n' || itemsbelong.category = 'dsl-a-isdn' || itemsbelong.category = 'dsl-a-isdn-modemy' || itemsbelong.category = 'gateway-router' || itemsbelong.category = 'dsl-ostatni' || itemsbelong.category = 'firewall' || itemsbelong.category = 'huby' || itemsbelong.category = 'kamery-a-multimedia' || itemsbelong.category = 'kamery' || itemsbelong.category = 'multimedia' )
LEFT JOIN `text` ta ON items.idItem = ta.code && ta.lang = 'cs' && ta.type = 'item-description'
LEFT JOIN `text` tb ON items.idItem = tb.code && tb.lang = 'cs' && tb.type = 'item-shortDescription'
WHERE items.availability = 'y'
LIMIT 0 , 10
Tento dotaz trvá zhruba 0.2s (už to je nic moc).
Katastrofa nastane v okamžiku, kdy k dotazu přidám ORDER BY podle sloupce, který se nenachází v tabulce "items".
Pak trvá dotaz přes 8s. Pokud řadím podle něčeho z items, moc se doba neprodlouží.
Bez LEFT JOIN na popis položky se dotaz provádí 0.03s.
Texty z tabulky "text" vybírat musím už při tom dotazu z důvodů možnosti vyhledávání v textu pomocí LIKE.
Jednoduché indexy jsou nastavené (zkoušel jsem i různé šílené kombinace, ale nic nepomohlo).
Celá funkce databáze je ta, že v tabulce "items" jsou uloženy produkty, které mohou náležet určitému webu,jazyku,kategorii (v různých kombinacích). To je uloženo v tabulce "itemsbelong". Texty produktu jsou uloženy v tabulce "text", kde jsou rozlišeny typem a kódem. V tabulce textů jsou i jiné texty, než ty k produktům.
Takže potřebuji vybrat výrobky, které odpovídají danému jazyku, webu, a ktegoriím a k nim načíst dva textové údaje. To v vše v jednom dotazu a určitém pořadí, které je závislé na volbě uživatele. Takže i třeba podle připojovaných textů.
Podle textů se budou v dotazu záznamy i dále filtrovat pomocí LIKE (vyhledávání).
V tabulce items a itemsbelong je zhruba 2000 záznamů, v tabulce text 2x tolik.
Pokud někoho napadá, proč je to tak pomalé, prosím o nějakou radu.
Bohužel jsem nikde nedohledal nic k problematice pomalosti LEFT JOIN v závislosti na řazení výstupu podle napojovaných tabulek.
Děkuji
Aitom: Jakub Vrána :
Zkus pomocí EXPLAIN zjistit, co se děje.
Jakub Vrána : Aitom:
S řazením podle sloupce z items tohle
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE items ALL PRIMARY NULL NULL NULL 1238 Using where; Using filesort
1 SIMPLE itemsbelong ref idItem,category,web,lang idItem 4 ithelpsql1.items.idItem 1 Using where
1 SIMPLE ta ref code,type,lang type 302 const 1598
1 SIMPLE tb ref code,type,lang type 302 const 1664
S řazením podle sloupce v jiné tabulce tohle
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE itemsbelong ref idItem,category,web,lang web 302 const 848 Using where; Using temporary; Using filesort
1 SIMPLE items eq_ref PRIMARY PRIMARY 4 ithelpsql1.itemsbelong.idItem 1 Using where
1 SIMPLE ta ref code,type,lang type 302 const 1598
1 SIMPLE tb ref code,type,lang type 302 const 1664
To Using temporary; se mi tam moc nelíbí. Ale už nevím víc co s tím. Neexistuje někde nějaký článek nebo seriál, který by se zabýval složitějšími dotazy a tím, co se vlastně v mysql při tom děje?
Jediné řešení, který bylo standardně rychlé (0.05s), jsem zatím vymyslel tak, že ve vazební tabulce itemsbelong ukládám i ID z tabulky textů pro oba načítané texty. Pak se nemusí tabulka textů napojená pomocí LEFT JOIN prohledávat.
Dotaz je pak takový:
SELECT items . * , itemsbelong . * , ta.text AS description, tb.text AS shortDescription
FROM items
INNER JOIN `itemsbelong` ON items.idItem = itemsbelong.idItem && itemsbelong.lang = 'cs' && itemsbelong.web = 'ithelp' && ( itemsbelong.category = 'sitove-prvky' || itemsbelong.category = 'sitove-prvky' || itemsbelong.category = 'adaptery' || itemsbelong.category = 'pci' || itemsbelong.category = 'pcmcia' || itemsbelong.category = 'power-line' || itemsbelong.category = 'prislusenstvi-k-adapterum' || itemsbelong.category = 'usb' || itemsbelong.category = 'anteny' || itemsbelong.category = '2-4ghz-sektorove' || itemsbelong.category = '2-4ghz-smerove' || itemsbelong.category = '2-4ghz-vsesmerove' || itemsbelong.category = '5-0ghz-sektorove' || itemsbelong.category = '5-0ghz-smerove' || itemsbelong.category = '5-0ghz-vsesmerove' || itemsbelong.category = 'kabely-pigtaily' || itemsbelong.category = 'konektory-bleskojistky' || itemsbelong.category = 'bezdratove-site' || itemsbelong.category = 'bluetooth' || itemsbelong.category = 'wi-fi-108mbit-2-4ghz' || itemsbelong.category = 'wi-fi-10mbit-5-8ghz' || itemsbelong.category = 'wi-fi-11mbit-2-4ghz' || itemsbelong.category = 'wi-fi-54mbit-2-4ghz' || itemsbelong.category = 'wi-fi-54mbit-5-8ghz' || itemsbelong.category = 'wi-fi-802-11n' || itemsbelong.category = 'dsl-a-isdn' || itemsbelong.category = 'dsl-a-isdn-modemy' || itemsbelong.category = 'gateway-router' || itemsbelong.category = 'dsl-ostatni' || itemsbelong.category = 'firewall' || itemsbelong.category = 'huby' || itemsbelong.category = 'kamery-a-multimedia' || itemsbelong.category = 'kamery' || itemsbelong.category = 'multimedia' )
LEFT JOIN `text` ta ON itemsbelong.idTextDesc = ta.idText
LEFT JOIN `text` tb ON itemsbelong.idTextSDesc = tb.idText
WHERE items.availability = 'y'
ORDER BY itemsbelong.category, items.idItem ASC
LIMIT 0 , 10
a explain tako:
1 SIMPLE items ALL PRIMARY NULL NULL NULL 1219 Using where; Using temporary; Using filesort
1 SIMPLE itemsbelong ref idItem,category,web,lang idItem 4 ithelpsql1.items.idItem 1 Using where
1 SIMPLE ta eq_ref PRIMARY PRIMARY 4 ithelpsql1.itemsbelong.idTextDesc 1
1 SIMPLE tb eq_ref PRIMARY PRIMARY 4 ithelpsql1.itemsbelong.idTextSDesc 1
Dotaz trva 0.05s, což už je v poho.
Takže ukládat idTextu do vazební tabulky bude asi možné řešení. Při nastavení relací by se snad měl i v udřžet pořádek v situaci, kde se záznam z tabulky text odstraní.
Aitom: Gifi:
<?php $q = mysql_query(
"SELECT zajezdy.id,
zajezdy.misto,
zajezdy.oblast,
zeme.zem AS 'zeme'
FROM zajezdy
INNER JOIN zeme ON zajezdy.zeme = zeme.id") or die("dotay nevzkonan");
while ($zaznam = mysql_fetch_array($q)):
echo $zaznam['zeme'].' '.$zaznam['oblast'].' '.$zaznam['misto']; ?><a href="?page=upravit&co=zemi&id=<?php echo $zaznam['id']?>">upravit nazev</a> <a href="?page=smazat&co=zemi&id=<?php echo $zaznam['id']?>">smazat zaznam</a><br />
<?php
endwhile;
?>
nevim proc nechce vybrat. nazvy tabulke a jednotliva pole jsou spravne.
zjistil jsem ze whilem to ani neprojde
Pavel:
Resim problem, ktery asi souvisi s timto clankem. Pridrzim se prikladu s vyrobky a kategorii vyrobku. Cele to jeste rozsirim o to, ze vyrobek bude moci spadat do vice skupin, nebo mu taky nemusi byt prirazena zadna skupina. Tudiz asi bude nutne zavest treti tabulku, ktera bude obsahovat info o tom, ktery vyrobek do ktere skupiny patri.Takze tabulky:
vyrobky (id, nazev)
skupiny (id, nazev)
vyrobky_skupiny_link (id_vyrobku, id_skupiny)
No a ja bych chtel ted nejak elegantne
a) pouze zjistit pocet vyrobku, ktere nemaji urcenou zadnou skupinu
b) vypsat vyrobky, ktere nemaji urcenou zadnou skupinu.
Dik.
PS: omlouvam se za diakritiku, ale jsem v cizine...
Jakub Vrána :
Druhý dotaz bych v NotORM napsal takhle (převedeno na ukázkovou databázi):
<?php
$software->application("id NOT", $software->application_tag()->select("application_id"))
?>
První za to přidá ->count("*").
Jakub Vrána : Diskuse je zrušena z důvodu spamu.
© 2005-2024 