Autor | Zpráva | ||
---|---|---|---|
bukaJ Profil * |
#1 · Zasláno: 13. 3. 2009, 01:32:25
Mám tabulku Shops ve které jsou různé informace o obchodech.
Řekněme, že obsahuje: id (INT-U, PK, AI), name (VAR(255), NN), ... Pak mám tabulku Flag_types, která obsahuje různé vlastnosti, které tyto obchody mohou mít (např. platby kartou, klimatizace, bezbariérový přístup... atd.). Sloupce tabulky: id (INT-U, PK, AI), name (VAR(255), NN), ... A nakonec třetí tabulku Flags, která tyto dvě spojuje jako cizí klíče, takže se tím řekne, které vlastnosti daný obchod má. shop_id (INT-U, PK), flag_id (INT-U, PK) Problém je, že nejčastější dotaz bude muset najít všechny obchody které mají určité příznaky. Vzhledem k tomu, že tabulka Shops bude obsahovat 5000 záznamů a každý obchod bude moci mít kolem 200 různých flagů není možné pro tyto flagy připravit vlastní sloupce. Situace je snažší tím, že v době vyhledávání už budu znát ID požadovaného flagu, takže vyhledání mi postačuje hledat daná ID. Napadlo mě jen dát dotaz: SELECT s.id, s.name FROM shops s JOIN flags f ON f.shop_id = s.id AND flag_id IN (2,12,65,67,82,83,84,150,151,170,190) GROUP BY s.id HAVING count(f.id) = 11; Kde 11 je počet vyhledávaných flagů. Ale efektivita tohoto dotazu asi nebude závratná - zejména v MySQL. Jak tuto situaci nejlépe vyřešit v rámci MySQL? |
||
Joker Profil |
#2 · Zasláno: 13. 3. 2009, 07:34:43
bukaJ
Ale efektivita tohoto dotazu asi nebude závratná - zejména v MySQL. Proč? Samozřejmě jde si ten dotaz vyzkoušet na nějakých testovacích tabulkách, nicméně nevidím důvod, proč by měl být nějak extrémně pomalý. |
||
Mastodont Profil |
#3 · Zasláno: 13. 3. 2009, 07:54:32 · Upravil/a: Mastodont
Nějak nechápu, proč tam je to GROUP BY a HAVING.
bukaj: Ty Chceš asi najít všechny shopy, které mají přiřazeno těch 11 tagů, ale tento dotaz funguje úplně jinak, ten vrátí všechny shopy, které mají 11 libovolných tagů, z nichž nejméně 1 spadá do množiny uvedené za IN. Klauzule IN totiž funguje jako OR, ne AND. |
||
Kajman_ Profil * |
#4 · Zasláno: 13. 3. 2009, 08:47:05
Mastodont
Však ano. Bude více řádků díky or - na ty se dá group a přes having count se vyberou jen ty, jde jich bylo 11 jako čísel v závorce. bukaJ Snad jedině dělat ten group by having jen na tabulce flags a až výsledek joinout s shops ale moc velký rozdíl tam asi nebude. Ono si to mysql stejně asi tak naplánuje (možná bude lepší group by f.shop_id). Mrkněte na explain. |
||
bukaJ Profil * |
#5 · Zasláno: 13. 3. 2009, 10:05:48
Kajmane, díky, zkusím to ještě promyslet.
Databáze je ve fázi návrhu, takže je možno s návrhem cuknout a klidně dost rapidně. Podmínkou tedy je, aby tedy každý záznam v tabulce shops mohl mít mít navoleny libovolné vlastnosti vyjádřené právě spojením přes tabulku flags a musí to být dimenzováno k tomu, že počet možných flagů výrazně poroste, proto samostatné sloupce nepřipadají v úvahu. Je také možné zřídit cachování, ale vzhledem k množství dat nevím, co a jak efektivně cachovat. Provedl jsem benchmark. Do tabulky shops jsem dal postupně 25000 záznamů a každému jsem dal 1-200 flagů náhodně vybraných v rozsahu 300 hodnot. Pak jsem hledal pro každé množství dat v DB záznamy, které mají u sebe 5 - 150 flagů a to celé 3×. Vyšla mi tedy tabulka 750 hodnot, které znázorňují situaci. To samé v Excelu (odkaz nebude platný věčně, za pár měsíců to smažu a tak se omlouvám všem kteří čtou tento článek po roce 2009) Zdá se, že je to rychlé, ale myslete na to, že to není konečný dotaz a taky to běželo na výkonné naprosto nezatížením serveru a optimalizovanými daty. Reál bude horší. Proto bych rád dostal dotaz řekněmě 50 flagů na 10000 položkách pod 0,3 s. |
||
Kajman_ Profil * |
#6 · Zasláno: 13. 3. 2009, 10:57:58
Myslím, že by to mohlo urychlit prohození pořadí sloupečků v primárním klíči (flag_id, shop_id). Ale pak si udělat ještě pomocný index na shop_id, pro jiné dotazy.
|
||
Kajman_ Profil * |
#7 · Zasláno: 13. 3. 2009, 11:01:05
SELECT s.id, s.name FROM shops s JOIN (select shop_id from flags where flag_id IN (2,12,65,67,82,83,84,150,151,170,190) GROUP BY shop_id HAVING count(*) = 11) f ON f.shop_id = s.id |
||
bukaJ Profil * |
#8 · Zasláno: 14. 3. 2009, 19:54:56
Zkusil jsem prohodit ty indexy a opravdu, výsledek je časově sice pro mě stále trochu nepřívětivý, ale zlepšení je přesto výrané a hlavně teď databáze chová mnohem předvídatelněji a průběh zatížení je mnohem více úměrný obtížnosti dotazu a dat a zmizely ty podivné extrémy.
Test probíhal za tentokrát během dne, kdy byl server trochu zatížen, ale měření se opakovalo 5×. To samé v Excelu (odkaz nebude platný věčně, za pár měsíců to smažu a tak se omlouvám všem kteří čtou tento článek po roce 2009) Děkuji za podnětné rady. |
||
bukaJ Profil * |
#9 · Zasláno: 15. 3. 2009, 14:00:21
Až po třech pivech jsem pochopil genialitu pojetí Kajman_ova dotazu z [#7]. Je to tak prosté a nesmírně účinné!!! Mlátil bych se do hlavy, že jsem na to nepřišel sám.
A ty výsledky! To samé v Excelu (odkaz nebude platný věčně, za pár měsíců to smažu a tak se omlouvám všem kteří čtou tento článek po roce 2009) Děkuji! |
||
ninja Profil |
#10 · Zasláno: 15. 3. 2009, 14:10:07
bukaJ" muyes jeste ykusit otestovat dotaz:
SELECT s.id, s.name FROM flags f JOIN shops s ON f.shop_id = s.id AND flag_id IN (2,12,65,67,82,83,84,150,151,170,190) GROUP BY s.id HAVING count(f.id) = 11; Bych rekl ze by si to MySQL melo prechroustat stejne jako Kajmanuv subselect, tak bych rad videl realne vysledky. |
||
bukaJ Profil * |
#11 · Zasláno: 15. 3. 2009, 15:34:32
Řeení subselectem mi celkem vyhovuje, protože se mi k tomu budou mnohem znáze připojovat další podmínky. Dnes už jsem benchmarkové prostředí uzavřel, takže ale i tak veliký dík za tip.
Pokud si chceš projet vlastní Benchmark, nabízím volně k použití jeho neúplný zdroják: Benchmark a Tabulky v DB Uvedeným souborům neposkytuji podporu a upozornuji, že pro jejich spuštění bude třeba trochu kód pozměnit, protože se psán v prostředí, které není s tímto koukem kódu uvolněno. A jako vždy: odkaz nebude platný věčně, za pár měsíců to smažu a tak se omlouvám všem kteří čtou tento článek po roce 2009. |
||
Časová prodleva: 15 let
|
0