Pomoc se strukturou databáze

[Vláďa J]

Kit> Vnořený dotaz vím, co je, ale nevím, jak ho použít. Mohl bys mi prosím poradit?

Pro zjednodušení uvedu následující příklad. Mám 3 tabulky - žánr, interpret a skladby. Tabulka žánr má sloupce žánr, žánr_id analogicky tabulka interpret má sloupce interpret a interpret_id. Mezi nima je asociační tabulka. Tedy stejně jako jsem to psal v mém včerejším příspěvku.

Na to je ale ještě napojená tabulka se skladbami, která má sloupce název, hodnocení, žánr_ref a interpret_ref. Poslední dva sloupce jsou cizí primární klíče odkazující na první dvě tabulky.

Jak z těchto tří tabulek co nejrychleji zjistím všechny interprety, kteří hrají žánr "Rock" a z tohoto žánru mají alespoň jednu skladbu s hodnocením vyšším než 3?

Když jsem měl vše pouze v jedné tabulce, stačil naprosto triviální dotaz

Kód: [Vybrat]

SELECT DISTINCT artist FROM Library WHERE genre = "Rock" AND rating > 3
Takový dotaz byl i na UMPC s Atomem provedený nejpozději do vteřiny. Pokud použiju nyní s více tabulkami analogický dotaz, trvá na hodně rychlém počítači více než 10s. Takže místo urychlení jsem to mnohonásobně zpomalil. :-(

Celý problém je o to komplikovanější, že SQL dotazy se musí generovat automaticky podle toho, co si uživatel přeje vyfiltrovat.

[Reklama]

[Kit]

http://forum.root.cz/index.php?topic=3418.msg28782#msg28782

Něco takového netestovaného:
SELECT DISTINCT artist FROM Library WHERE genre_id IN (SELECT genre_id FROM genre_tab WHERE genre="Rock");

Nejprve uděláš SELECT žánru a teprve potom ho JOINem spojíš s ostatními tabulkami. Abys zvýšil rychlost, budeš asi muset indexovat i cizí klíč, tedy genre_id v tabulce artist.

Normalizace databází je hezká věc, na takové úloze se to můžeš dobře naučit. V tom má Logik pravdu. Otázkou je, zda se to v takové aplikaci vyplatí, zda její složitost nepřekročí únosnou míru pro další udržování kódu. Dotazy jsou totiž komplikovanější. Vím, že fulltext popírá normalizaci a má své nevýhody, ale v uvedeném případě bych ho použil. A už mlčím, abych to od Logika zase neschytal.

[Vláďa J]

Díky za nakopnutí. Už mi to do sebe trochu zapadá. V mém případě je to trochu složitější. Nakonec tenhle ód vypadá slibně:

Kód: [Vybrat]

SELECT artist FROM artist_table WHERE artist_id IN
    (SELECT DISTINCT artist_ref FROM Library WHERE rating >= 3 AND genre_ref IN
        (SELECT genre_id FROM genre_table WHERE genre <> 'Speech'))
je v podstatě stejně rychlý jako původní

Kód: [Vybrat]

SELECT DISTINCT artist_ref FROM Library WHERE rating >= 3 AND genre_ref <> 0
Oba na mém stolním počítači zaberou 0,015s při 22 000 záznamech, což je OK. Teď jenom vymyslet, jak takový kód vygenerovat automaticky. To bude, obávám se, solidní oříšek.

[BoneFlute]

Nezaznělo to tady. Ale když použiješ distinc, tak se tlačí všechna data, a pak se zahodí. Když použiješ agregaci, tak se jede po indexech. Tedy toto, jak jsem to upravil by mělo být rychlejší. Tak zní teorie. Zkus to, a napiš, jaký je v tom rozdíl.

Kód: [Vybrat]

SELECT artist FROM artist_table WHERE artist_id IN
    (SELECT artist_ref FROM Library WHERE rating >= 3 AND genre_ref IN
        (SELECT genre_id FROM genre_table WHERE genre <> 'Speech')
    GROUP BY artist_ref
    )


Další optimalizace by mohla být ta, že nepoužiješ IN, ale JOINování. Asi to nebude vypadat tak hezky, ale některé databáze mají s optimalizací IN problémy.

Kód: [Vybrat]

SELECT artist
FROM artist_table AS c
LEFT JOIN (
  SELECT a.artist_ref
  FROM library AS a
  LEFT JOIN genre_table AS b ON b.genre_id = a.genre_ref AND b.genre <> 'Speech'
  GROUP BY a.artist_ref
) d ON d.artist_ref=c.artist_id


[Vláďa J]

Použití GROUP BY místo DISTINCT nemá na rychlost vůbec žádný vliv. Pokud použiju JOIN tak, jak je to v druhém případě (a ještě doplním AND rating >= 3, aby to odpovídalo), tak se výpi naopak cca 3x zpomalí.

Takže asi zůstanu u použití "IN".

Jenom mě ještě napadla jedna věc. Jak co nejrychleji vypíšu všechny stopy (tedy celou tabulku Library) tak, aby ve výpisu byly všechny sloupce tabulky Library, ale například artist_ref se nahradilo sloupci artist a artist_picture a analogicky se sloupci genre_ref a album_ref.

[Reklama]

[Logik]

- to s tim rozdílem mezi distinct a group by bych se neučil, sice to asi byla alespoň v nějaký verzi mysql pravda (dělali tam nějaký optimalizace ohledně toho), ale obecně je to čunačina - dobře napsanej optimalizátor by měl umět užít index i u distinct - a naopak optimalizátory často selhávaj i u group by.

- pokud děláte vnořenej dotaz, tak žádný distinct (a už vůbec ne group by) nepotřebujete! Umělec je přeci v tabulce umělců pouze jednou! To, že je jeho číslo ve vnořenym dotazu víckrát je irelevantní.

- s tím, že JOIN je někdy rychlejší souhlasím, ale tuším, že všechny databáze co s nima mam větší zkušenosti z hlediska výkonu (postgresql, mysql, firebird) to už zvládaj dobře. Mysql 4 s tím měla velký problémy a Firebird 1 taky, tam jsem joinoval jak divej.

- osobně bych udělal tabulku lidí a ty pak přiřazoval k albu (nebo ještě lépe písničce, ale k albu je to přijatelný zjednodušení) buď jako interprety, nebo jako autory.  tzn udělat tabulku:
 album_id, osoba_id, (autor|interpret)
popř. v jednoduššim provedení dát do písničky pole autor a interpret (ale to nedovolí dva interprety apod).

- Stejnětak žánr podle mne musíš přiřazovat k albům a ne lidem. Jinak Ti vypadne např. mezi folkrokem bethowenova devátá, protože tu hrála česká filharmonie, která taky hrála s čechomorem (viz Rok Ďábla). A takovejdle "multižánrovejch" je spousta.

- automaticky generovaný dotazy alá IN nejsou zas takovej problém: představ si každej poddotaz jako jednu podmínku ve where. Prostě místo
žánr = něco
tam je podmínka
žánr IN (něco).
A to něco postavíš stejně, jako ten celej dotaz.
Každej filtr ve vyhledávači pak přidá jednu podmínku za WHERE, pospojovanou AND. To, že to chceš mít v GUI hierarchický neznamená, že musí bejt hierarchická struktura DB ani že musíš do sebe nořit deset dotazů.

- Co se týče udržování kódu, tak samozřejmě něco jde v plochý struktuře lépe, něco hůře. Ale zrovna tydlety vnořený dotazy, když má člověk dobrou knihovnu, moc nestojí. Doporučuju si napsat nějakej objekt na postupný skládání dotazů, z pythonu např. jsem za pět sekund našel sqlpuzzle3k. Ale nevím, jak se to používá, já používám bazmek vlastní výroby, je to pár řádek

result=new SQLurey('pisnika')
a každej filtr by vracel nějakej "filtr",
result.addWhere(filtr)
a ten filtr bych třeba na všechny písničky od Evy a Vaška udělal takhle:
filtr=new SQLInQuery('id', 'SELECT pisnicka_id FROM pisnickyautori pa INNER JOIN autori a ON (pa.autor_id = autor.id) WHERE autor.jmeno = "Eva a Vašek")
až na to, že tydle písničky bych spíš nechtěl, takže bych udělal
filtr=filtr.not()
a pak bych chtěl třeba ještě jen nový písničky
result.addWhere("rok > 2009")

takovádle knihovna ušetří hodně práce a zároveň zpřehlední to automatický generování SQL - jakože většinou se generuje automaticky.

[Vláďa J]

Logik> Všechno tohle jsem zkoušel. Funkci na automatický generování SQL dotazu tam samozřejmě mám už teď k plochý tabulce.
Zkoušel jsem jí předělat na použití se normalizovanou databází. Jednoduchý dotazy fungují dobře, jenže to začíná být značně komplikovaný v okamžiku, kdy použiju dynamický playlisty. Jako příklad jednoho běžnýho playlistu jsem uvedl hudba s hodnocením 3 a vyšším. Tedy dynamický playlist má podobu rating >= 3 AND genre <> "Speech". Tohle je samozřejmě nepoužitelné. Sloupec rating je v tabulce se všemi písničkami, genre je naopak v samostatné tabulce.

Takže by asi bylo nutné ten dynamický playlist vytvořit už tak, aby s tímhle počítal. To by asi nějak šlo.

Vztah jednotlivých sloupců je samozřejmě M:N. Každé album může mít víc autorů i žánrů, ke každému interpretovi patří několik žánrů atd. Udělal jsem tedy tabulky žánr, interpret a album a další 3 asociační tabulky mezi každou z nich. V hlavní tabulce s názvem "Library" jsou potom reference do všech 3 předchozích tabulek.

Ale zasekl jsem se na jiné věci. Pořád se trochu motám v tom, jak použít JOIN. Zkusím lehce shrnout mé myšlenkové pochody:

Dejme tomu, že použiju můj již zmíněný dynamický filtr a formuluji jej jako podmínku

Kód: [Vybrat]

SELECT artist FROM artist_table WHERE artist_id IN
    (SELECT artist_ref FROM Library WHERE rating >= 3 AND genre_ref IN
        (SELECT genre_id FROM genre_table WHERE genre <> 'Speech')
    )
Jenže tohle mi vyhodí opakující se autory, jak tento dotaz přepíšu tak, aby vypadly pouze unikátní záznamy bez použití DISTINCT / GROUP BY?

Dál potom mohu chtít vyjet seznam písniček pro playlist. To bude dvourozměrné pole se všemi dostupnými informacemi ke každé písničce. Můžu analogicky s předchozím dotazem získat toto:

Kód: [Vybrat]

SELECT * FROM Library WHERE rating >= 3 AND genre_ref IN
        (SELECT genre_id FROM genre_table WHERE genre <> 'Speech')
    )
Jenže místo autora budu mít autor_id, místo alba bude album_id atd. Jak upravím dotaz tak, abych získal i sloupce autor, obrázek_autora, album, autor_alba atd.? Zkoušel jsem to a výsledkem byla vždycky nějaká hrůza se spoustou JOINů a podmínek ON a výpis trval nekonečně dlouho. Přitom by mělo jít o triviální věc (z hlediska logiky databáze). Na tohmle jsem se zasekl a nevím, jak dál. Všude jsem našel pouze příliš jednoduché příklady, které obsahovaly maximálně 2 tabulky.

Zatím jsem po vzoru Járy Cimrmana průkopníkem slepých uliček. Až si někdy říkám, jestli mi za to ta vteřina, o kterou to urychlím, stojí. Jenže já blbec jsem prefekcionista a pokud to jde udělat líp, tak to tak udělat prostě chci.

[Mareg]

Přiznám se, že nevím jaký to bude mít dopad na výkon a jestli tam nebudou ty záznamy vícekrát (nechce se mi dělat ty TB), takže je ta správná cesta použítí INů, ale pro MySQL bych to za pomoci JOINtů napsal takto:

Kód: [Vybrat]

SELECT artist FROM artist_table
  LEFT JOIN Library ON artist_id = artist_ref
  LEFT JOIN genre_table ON genre_ref = genre_id
WHERE rating >= 3
  AND genre <> 'Speech'
Případně tam můžete přidat GROUP BY na potřebné sloupce.

A mimochodem, přijdem mi zbytečné pojmenovávat tabulky "tabulky", takže podle mě by to vypadalo následovně:
artist_table  - artists
artist_table.artist_id - artists.id
Library.artist_ref - library.artist_id

Zkuste si zavést pravidla pro pojmenovávání TB i sloupců, nemusíte pak přemýšlet nad tím jestli se ten klíč jmenuje artist_id nebo artist_ref.
Primární klíč je vždy id, vazba na PK je <nazev_tb>_id, atd.

Kód: [Vybrat]

SELECT artist FROM artists a
  LEFT JOIN library l ON a.id = l.artist_id
  LEFT JOIN genres g ON l.genre_id = g.id
WHERE g.rating >= 3
  AND g.type <> 'Speech'


Jenže místo autora budu mít autor_id, místo alba bude album_id atd. Jak upravím dotaz tak, abych získal i sloupce autor, obrázek_autora, album, autor_alba atd.? Zkoušel jsem to a výsledkem byla vždycky nějaká hrůza se spoustou JOINů a podmínek O

No to je právě ta "krása" normalizace DB, že pak pro každý údaj musíte sáhnout do správné TB, což se dá udělat buď v kódu programu, nebo tím JOINtem  . Ale i přesto si myslím, že normalizace a syntetické PK typu int jsou tou správnou cestou, tak ja radí Logik.

[Kit]

A mimochodem, přijdem mi zbytečné pojmenovávat tabulky "tabulky", takže podle mě by to vypadalo následovně:
artist_table  - artists
artist_table.artist_id - artists.id
Library.artist_ref - library.artist_id

Zkuste si zavést pravidla pro pojmenovávání TB i sloupců, nemusíte pak přemýšlet nad tím jestli se ten klíč jmenuje artist_id nebo artist_ref.
Primární klíč je vždy id, vazba na PK je <nazev_tb>_id, atd.

S tím mohu souhlasit a sám to tak používám.

Je ještě jedna alternativa, která je hojně používána a prosazována. Aby názvy cizího klíče v jedné a příslušného primárního klíče v druhé tabulce byly shodné. Tedy tabulka artist, v ní primární klíč artist_id. V ostatních tabulkách cizí klíč artist_id. Má to tu výhodu, že je možné v JOINech používat USING a jmenovat ten sloupec pouze jednou. Dotazy se tím zpřehlední.

Dále bych si dal velký pozor, aby nebyl někde stejný název tabulky a nějakého sloupce třeba i v jiné tabulce. Některé chyby v SQL dotazech se pak špatně hledají. Třeba sloupec `artist` v tabulce `artist` vypadá hodně divně. Přitom v tom sloupci je _jméno_ zpěváka, proto by se ten sloupec měl jmenovat třeba `jmeno` nebo `name`.

[Logik]

- ad pojmenování:
doporučuju tabulku v jednotném čísle. Název cizího klíče <table>_id. Název primárního klíče buďto shodný s cizím klíčem, nebo jen id, obé má své výhody a nevýhody. A cíleně se vyhýbat duplicitám v jménech sloupců spíš nedoporučuju: ono to ve větší db ani nejde. Spíše je lepší si zvyknout vždy psát plně kvalifikované názvy sloupců, tzn tabulka.sloupec.

- ad GROUP BY - pokud někde musíte použít GROUP BY, kde přímo nepotřebujete agregaci, asi je dotaz špatně

- jak dělat filtry
píšeš, že všechny tabulky máš M:N. Tak to je skvělé, stačí Ti jeden mustr, kterej půjde aplikovat všude. Představ si teď filtr jako blackbox , kterej Ti hodí všechny možný čísla písniček. Takovej filtr poskládáš snadno, třeba u autora:

Kód: [Vybrat]

(SELECT pisnicka_id FROM pisnicka_autor WHERE autor_id IN (SELECT id FROM autor WHERE jmeno = "Cimrman")
Podmínka ve where může bejt různá, např. WHERE autor_id = 1 nebo autor_id IN (1,2,3,4), podle toho, jak autory vybíráš.
Úplně stejnou podmínku můžeš udělat pro žánry, alba, co tě napadne.
No a teď vem tendle kus dotazu jakko blackbox kdekoli potřebuješ vybrat písničky:

SELECT * FROM pisnicka WHERE id IN <blackbox>

Nebo kde potřebuješ vybrat <entitu> (např. žánr, autora, atd...) z daných <entit>, které jsou možné pro již vybrané písničky

SELECT * FROM <entita> WHERE id IN (SELECT <entita>_id FROM <entita>_<pisnicka> WHERE <pisnicka_id> IN <blackbox>

V podstatě todle jsou všechny dotazy, které budeš potřebovat.

- ad opakující autoři:
Pokud Ti ten dotaz

Kód: [Vybrat]

SELECT artist FROM artist_table WHERE artist_id IN (...subselect...)vyhodí opakující se autory, je to čistě proto, že v té tabulce opakující autoři prostě jsou :-). Řešení tedy není expost group by, ale už při plnění tabulky se duplicit vyvarovat. Nejlépe tak, že hodíš na sloupce, které by neměli být duplicitní omezení (constraint) UNIQUE (zároveň slouží jako index) a tabulku znova naplníš. Samo Ti to zařve v okamžiku, kdy tam budeš chtít vložit druhého stejného autora a budeš moct to opravit tak, aby to místo toho použilo již toho co tam je.

-ad jak dostat autora, album atd....
No jsou dvě možnosti: buďto opravdu přidat pro každou subtabulku, nebo si ty záznamy ze subtabulek vypsat vedle, strčit do nějakýho hashpole (v pythonu asi dictionary) a tahat je v případě potřeby. První řešení se vyplatí, pokud se tahá relativně málo písniček, druhý řešení se hodí, pokud se tahá hodně písniček a počet písniček značně převyšuje počet autorů/alb atd...

Pokud jsi tam Ty joiny dal a bylo to pomalý, pak je to možná tím problémem z předchozího odstavce, popř. je také možné, že Ti někde schází indexy... anebo tam např. někde schází nějaká potřebná podmínka. Zkus sem až ten předchozí problém vyřešíš dát ten dotaz, co je pomalej, a zároveň ještě jeho EXPLAIN (spusť ten samý dotaz, ale před něj napiš EXPLAIN, tzn. EXPLAIN SELECT ...........

- Jinak Ti doporučuju na to nezanevřít - mkožná to je opravdu overkill, ale na to, abys pro příště poznal, co už je overkill a co ne bys měl ten "overkill" zvládat a to se nenaučíš líp, než tak, že to prostě napíšeš.

[Vláďa J]

Po dlouhé době jsem se konečně dostal k tomu, abych začal pracovat na optimalizaci mé databáze. Začal jsem komplet od nuly a tentokrát to, zdá se, funguje. Akorát některé dotazy trvají dle mého názoru neúměrně dlouho a já netuším proč. Je něco špatně na tomto dotazu? Dal by se nějak urychlit?

Kód: [Vybrat]

SELECT title, id, track FROM Library WHERE id IN (SELECT title_ref FROM genre__title WHERE genre_ref = 3) AND id IN (SELECT title_ref FROM artist_album_artist__title WHERE artist_album_artist_ref = 366) AND id IN (SELECT title_ref FROM album__title WHERE album_ref = 236) ORDER BY track ASC
Ono je to z toho celkem vidět, ale vybírám skladby podle žánru, autora a alba. Které skladby to splňují vyhledávám přes asociativní tabulky. Výsledkem jsou pouze 2 skladby, přesto dotaz zabere na pomalejším počítači až desetiny sekund. Nerozumím tomu proč. Očekával bych, že databáze nejprve vyfiltruje vše podle první podmínky, kdy odpadne přes 90% záznamů a potom už porovnává jenom malý množství řádek a další filtry budou mnohem rychlejší. Jenže podle mých pokusů to tak nefunguje. Kde může být problém?