Optimalizace pro vyhledávání záznamu podle vlastností z jiné

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:

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?

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.

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.

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!

Ř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.