Autor Zpráva
bukaJ
Profil *
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
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
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 *
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 *
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 *
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 *
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 *
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 *
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
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 *
Ř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.

Vaše odpověď


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: