Složitější dotaz

Ahoj všem,

mám dvě tabulky v eshopu. Jedna tabulka obsahuje id produktu název cenu skupinu. Druhá tabulka obsahuje id produktu a tech. parametry produktu. pokusím se přiblížit modelem.

table produkt table produkt_parametr
--------------- ------------------------------
id_produkt | | id_parametr |
--------------- ------------------------------
skupina | | id_produkt |
--------------- -------------------------------
cena | | hodnota_parametru |
--------------- -------------------------------


Každá skupina produktů má jiné (i jiný počet) parametrů. Proto zvlášť tabulka pro parametry. Ale moje znalosti SQL mi nedovolí vymyslet efektivní a rychlé vyhledávání produktů podle parametrů. Složitě vytahuju idecka produktů z tabulky produkt_parametr a pak podle nich dotazem typu "id_produkt in (...,...,...,...)" jelikož záznamů v tabulce je opravdu hodně je to opravdu zdlouhavé hledání. Poradí mi někdo pokud to vůbec jde jak udělat rychlý dotaz, tak aby mi v podstatě vyšel tento výsledek?

vysledky_hledani
------------------------------------------------------------------------------------------
id_produkt | skupina | parametr1 | parametr2 | paramter3 |
------------------------------------------------------------------------------------------

snad jsem to popsal srozumitelně, děkuji za odpovědi.

koukám, že tabulky se mi nepodařilo dát vedle sebe, tak znova a pod sebou.

table produkt
---------------
id_produkt |
---------------
skupina |
---------------
cena |
---------------


table produkt_parametr
------------------------------
| id_parametr |
------------------------------
| id_produkt |
-------------------------------
| hodnota_parametru |
-------------------------------

Zkus se podívat na funkci GROUP_CONCAT().
Sice nedosáhneš těch sloupců parametr1, parametr2, .., ale tyto parametry se budou chovat jako jeden sloupec odděleny oddělovačem. Ty pak oddělíš PHP skriptem.

aha, děkuji... funkci jsem prostudoval a je to, pokud jsem to správně pochopil, zhruba to samé co dělám teď akorát ja to dělám pomocí scriptu v PHP. Zkusím to nasadit a porovnat rychlost.

Napadá někoho další řešení? Děkuji.

Je to použití EAV, můžeš si najít i jiná řešení. Tady máš jedno z nich:

SELECT * FROM produkt JOIN produkt_parametr USING (id_produkt) WHERE id_parametr=:typ AND hodnota_parametru=:hodnota;

no tak to je taky zajímavé. akorat se mi nedaří najít dotaz když chci najít produkt na základě více paramterů najednou.

Něco jako

SELECT * FROM produkt JOIN produkt_paramter USING (produkt_id) WHERE id_parametr = 1 AND hodnota='neco' and id_paramter = 2 and hodnota = 'neco 2'

chci vytvořit filtr, který bude obsahovat všechny parametry dané skupiny produktů. Napadá mně jak to řešit pomocí PHP, ale dá se to přes SQL ?

jdu hledat dál. za odpovědi děkuji.

To víš, že se to dá. Jen se to musí patřičně upravit. Místo tabulky produkt_paramter použiješ průnik samostatného vyhledání každého parametru v tabulce produkt_paramter. Jen se mi s tím už nechce piplat.

Když tak nad tím koukám, tak by se hodil vložený dotaz. Jde sice o stejný princip, který používáš, ale je to obalený do jednoho dotazu. Něco jako:

SELECT p.* FROM produkt AS p WHERE p.id_produkt IN ( SELECT pp.id_produkt FROM produkt_parametr AS pp WHERE pp.hodnota_parametru LIKE '%(co hledám)%' )

Tohle je pouze vyhledávací algoritmus. Výstupem je tabulka produkt.

Pokud chceš vypsat i ty parametry, pak použij zmíněný GROUP_CONCAT:

SELECT p.*, (SELECT GROUP_CONCAT(pp1.hodnota_parametru SEPARATOR ', ') FROM produkt_parametr as pp1 WHERE pp1.id_produkt=p.id_produkt) AS parametry FROM produkt AS p WHERE p.id_produkt IN ( SELECT pp.id_produkt FROM produkt_parametr AS pp WHERE pp.hodnota_parametru LIKE '%(co hledám)%' )


Je to děláno z hlavy a spleteno ze všech možných kousků :) Takže ber na vědomí, že dotaz může obsahovat chyby. Ale nějak takto by to mohlo vypadat.
Jen nevím, co to udělá s výkonem. Jsou tam dva vložené dotazy, které mohou zpracování zbrzdit takovým způsobem, že to bude delší, než dosavadní řešení ;)

to Kit:
byl bych ti vděčný za úkazku. z toho co jsi mi napsal nedokážu nic moc sesmolit.
----------------------------------------------------------------------------------------


to Tomík:
první příklad vlastně používám, akorát těch vnořených dotazů mám více zasebou - tím je to celé dost pomalé

druhý příklad resp. celkově GROUP_CONCAT() asi není to pravé co hledám.


myslel jsem si, že když už použiji EAV MODEL najdu nějaký "jednoduchý" a rychlý dotaz, který mi vybere produkt na základě shody ve více parametrech najednou.

Tomíkovo řešení se mi pro daný případ líbí víc. Když ho upravím, tak to bude vypadat asi takto:

SELECT * FROM produkt AS p WHERE
p.id_produkt IN (SELECT pp.id_produkt FROM produkt_parametr AS pp WHERE pp.id_parametr = 1 AND hodnota='neco') AND
p.id_produkt IN (SELECT pp.id_produkt FROM produkt_parametr AS pp WHERE pp.id_parametr = 2 AND hodnota='necojineho' );

EAV model se používá jen tam, kde chceme mít jednoduchý návrh databáze i za cenu nižší efektivity a složitějších dotazů. Pokud to potřebuješ mít rychlé a robustní, normalizuj. Dotazy budou mnohem jednodušší a rychlejší.

Souhlasím s Kitem. EAV je pěkný vzor, pokud je potřeba kompletně elastický návrh (například pro obecné řešení e-shopu, který se později použije vícekrát pro různé projekty nebo v situacích kdy se seznam parametrů zboží často mění atp.).

A teď pro změnu nesouhlasím s Kitem. Zmíňený dotaz vypadá pěkně a není ani složitý. Má však zásadní vadu, je neskutečně pomalý (s větším množství parametrů na velkých tabulkách neúnosně pomalý). Nevím, co přesně MySQL dělá (ač znám různé teorie a mám i vlastní domněnku) s vnořeným dotazem uvnitř IN(), ale na průměrně vytíženém serveru takový dotaz trvá násobně déle než při udělání můstku v PHP (tzn. nejprve načtu seznam ID, to jde mimochodem udělat i na jeden dotaz, a v druhém kroku načtu seznam produktů podle ID).

Osobně, kdybych teď dělal nový e-shop na zelené louce, poohlédnul bych se po jiném řešení (například po serializaci).

Dobrá, zkusím to upravit:

SELECT * FROM produkt AS p WHERE p.id_produkt IN (
SELECT DISTINCT pp.id_produkt FROM produkt_parametr AS pp WHERE pp.id_parametr = 1 AND hodnota='neco'
INTERSECT
SELECT DISTINCT pp.id_produkt FROM produkt_parametr AS pp WHERE pp.id_parametr = 2 AND hodnota='něcojiného'
INTERSECT
SELECT DISTINCT pp.id_produkt FROM produkt_parametr AS pp WHERE pp.id_parametr = 3 AND hodnota='třetípodmínka'
);

Myslel jsi to tak nebo ještě jinak?

MySQL má u vnořených dotazů problém s tím, že jejich výsledky neindexuje. V tomto případě by to nemuselo vadit, protože nejprve se vyhodnotí vnořené dotazy a pak výsledný id_produkt vyhledá v tabulce produkt podle primárního klíče. Ovšem EXPLAIN jsem nezkoušel, jen tipuji.

Pokud by místo EAV byly 3 samostatné tabulky, bylo by to samozřejmě mnohem rychlejší. Také by pomohlo, kdyby atribut `hodnota` byl indexován fulltextem. Ovšem do 1000 skladových položek to nemá smysl řešit.