[mysql] Trage query mbt. product->categoriekoppeling

[Scyth]

Ik heb op dit moment voor een uitgebreide webshop 3 tabellen in een MySQL omgeving. Een productentabel, een categorieëntabel en een koppeltabel tussen beide. Zowel de productentabel als koppeltabel zijn beide gewoon standaardlayout; product_id en category_id als (gecombineerde)primary etc.

Categorieëntabel is ook redelijk standaard, met de toevoeging van parent_child relaties; die voor de query weinig uitmaken, maar voor de front-end wel handig zijn.

code:

category_id | parent_id | <overig>
______________________________
200         | 100       | <overig>
300         | 100       | <overig>
...         | ...       | <overig>
1000        | 511       | <overig>
1001        | 511       | <overig>
1002        | 511       | <overig>

Een product kan in 1 of meerdere categoriëen vallen. Aan de hand van een script kan de gebruiker specificeren aan welke 'search' de getoonde producten moeten voldoen. Door de parent-child relaties tussen de categorieëen wordt een overzicht gemaakt van selecteerbare categorieëen. Vervolgens wordt er door een simpel script het aantal mogelijke (logische) permutaties berekend. Lees; de klant selecteert de (hoofd)categorieëen
• zuivel (200)
• frisdrank (300)
En, puur ter illustratie de subcategorieëen
• zoete dranken (1000)
• zure dranken (1001).

De mogelijke permutaties zijn dan deze:
[[200,1000],[200,1001],[300,1000],[300,1001]]

In dezelfde database staat een stored procedure, die aan de hand van een set categorienummers de producten ophaalt. (code hieronder). De aanroep is als volgt:

code:

CALL GETPRODUCTSBYCATEGORYSET('200,1000', 2)

Hierop krijg ik dus distinct alle producten die horen in zowel category_id 200 als 1000 zitten. Met de 4 permutaties die hierboven genoemd zijn; wordt deze query dus 4 maal uitgevoerd; en via PHP gemerged tot een array met unieke product_id, die voldoen aan de filter die de gebruiker heeft gespecificeerd. Als ik het laatste nummer (2) aanpas naar 1, krijg ik alle producten die in 1 van de 2 categorieën zit. Dit als soort 'scoringssysteem'. Dit wordt echter (nog) niet gebruikt, en dit nummer is vooralsnog altijd gelijk aan het aantal elementen in de set.

Deze query, hoewel langzaam, is acceptabel qua tijden. Zodra ik echter extra gegevens ga joinen door de stored procedure (zie hieronder) aan te passen worden de querytijden onacceptabel hoog (lees 0,2 seconden of meer door simpelweg 'product_enabled' toe te voegen, en 0,8 seconden voor een SELECT *).
Zeker als je weet dat deze query in 1 overzichtsslag meerdere keren aangeroepen wordt, soms wel tot 30 maal per index, afhankelijk van de hoeveelheid mogelijke permutaties van de door de gebruiker geselecteerde categorieëen. (er wordt hierbij rekening gehouden met onmogelijke combinaties).

De query MOET geoptimaliseerd worden, aangezien er gesorteerd moet gaan worden op actieve voorraden en zelfs populariteit. Extra gegevens verkrijgen vanuit de stored procedure is érg aantrekkelijk aangezien er tot 30000 artikelen per query gereturned worden. Om deze naderhand in PHP nogmaals bij langs te gaan om te checken op voorraad, populariteit, whatever, is ondenkbaar traag.

Is er iemand die een idee heeft hoe ik dit beter aan kan pakken of iemand die de onderstaande query kan optimaliseren? Ik zie even door de bomen het bos niet meer.

Stored procedure getproductsbycategoryset

code:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getproductsbycategoryset`(
    IN CATSET TEXT,
    IN NUM TINYINT(3) UNSIGNED
)
BEGIN

SELECT C.product_id FROM
(SELECT product_id, category_id FROM product_category_mapping WHERE FIND_IN_SET(category_id, CATSET)) C
GROUP BY C.product_id
HAVING COUNT(C.product_id) >= NUM;

END

EDIT:
Het lijkt erop dat de volgende query precies hetzelfde doet, maar dan 3x sneller:

code:

SELECT product_id FROM inf_product_category_mapping WHERE FIND_IN_SET(category_id, CATSET) GROUP BY product_id HAVING COUNT(product_id) >= NUM;

Dit versnelt het proces dan al met een behoorlijke factor; maar nogmaals; ik ben benieuwd naar andere aanpakken van dit probleem.

[ Voor 4% gewijzigd door Scyth op 06-12-2011 14:12 . Reden: typo ]

[Creepy]

Even een tikje door naar PRG

[Scyth]

Inmiddels het probleem weten terug te brengen tot het volgende:

Er moeten uit een koppeltabel artikelnummers geselecteerd worden die een specifiek aantal keer matchen. Via een kennis heb ik onderstaande link gekregen; die het probleem beschrijft en de eerste reactie daarop is in principe het antwoord.

http://stackoverflow.com/...conditions-on-same-column

Daar was ik dus zelf ook al achter gekomen, die de eerlaatste edit van TS. Het probleem waar ik nu tegenaanloop is het combineren van deze query met andere tabellen, zonder dat er enorme performancelosses gaan optreden door erg nasty joins.

Iemand een idee hoe ik dit aan kan pakken, en of mijn aanpak uberhaupt de juiste is? De database is zo genormaliseerd mogelijk, maar ik neig nu om een stapje terug te doen; of te werken met cache-tables waarin in eerder opgehaalde informatie opsla in één enkele tabel ten koste van schaalbaarheid. Dit doet echter de query-cache erg teniet.

[Verwijderd]

Meh... je opzet is wel goed ik snap ook niet waarom je query meer dan een paar miliseconden zou duren, is weinig speciaals aan eigenlijk ... zelfs al zou het om een miljoen records gaan dan zou dit nog steeds een fluitje van een cent moeten zijn voor de gemiddelde database server.

weet je zeker dat je indexen goed staan ?
welke storage engine gebruik je ?

Mischien eens proberen om de query uit te voeren als :

SELECT product_id FROM inf_product_category_mapping WHERE category_in IN (3,76,7,2,8) GROUP BY product_id HAVING COUNT(product_id) >= $num

find_in_set lijkt me hier nogal overkil.

[Scyth]

Verwijderd schreef op woensdag 07 december 2011 @ 15:21:
...

Inmiddels had ik inderdaad al IN gebruikt inplaats van FIND_IN_SET, maar dat maakt qua snelheid niet echt uit. De query is inderdaad zo simpel en eenvoudig mogelijk; en op mijn thuisservertje doet hij er nu 0.031 seconden over. Best lang, maargoed; het is een i3. Waar ik me dus druk om maak is dat zodra er meer gegevens aanvast gekoppeld gaan worden; het fout gaat. Zie onderstaande query:

code:

SELECT M.product_id, P.product_enabled FROM inf_product_category_mapping M
JOIN inf_products P ON P.product_id = M.product_id
WHERE M.category_id IN (1001,200)
GROUP BY M.product_id, P.product_enabled
HAVING COUNT(M.product_id) >= 2

(Mijn MySQL installatie staat op de ONLY_FULL_GROUP_BY, vandaar de twee columns in de GROUP clause) Een dergelijke query bijvoorbeeld duurt nu al 0.234 seconden; en dát is nog maar het begin. De volgende query duurt al snel een halve seconde.

code:

SELECT M.product_id, P.product_enabled, SUM(S.stock_quantity) FROM inf_product_category_mapping M
JOIN inf_products P ON P.product_id = M.product_id
JOIN inf_stock S ON S.product_id = M.product_id
WHERE M.category_id IN (1001,200)
GROUP BY M.product_id, P.product_enabled
HAVING COUNT(M.product_id) >= 2

Ik vraag me op dit moment af hoe andere webshops dergelijke systemen databasetechnisch bouwen, en dan vooral aanspreken. Of is dit gewoon een kwestie van snellere hardware?

[pedorus]

Pff, wat een lange verhalen, waardoor natuurlijk niemand antwoord geeft

FIND_IN_SET is nogal traag omdat je geen indexen kan gebruiken, niet aan beginnen dus. Een join met IN zou kunnen, maar een self-join ligt een stuk meer voor de hand bij twee categorieën. Dus iets als:

SQL:

SELECT M.product_id, P.product_enabled FROM inf_product_category_mapping M
JOIN inf_product_category_mapping M2 ON M.product_id = M2.product_id
JOIN inf_products P ON P.product_id = M.product_id
WHERE M.category_id =1001 AND M2.category_id=200

Nou zou het kunnen dat dit ook traag is, post dan eens de EXPLAIN, en ga na of je wel correcte indexen hebt op in ieder geval inf_product_category_mapping.category_id. Gecombineerde index inf_product_category_mapping(category_id, product_id) zou kunnen helpen.

[Scyth]

code:

SELECT M.product_id, P.product_enabled FROM inf_product_category_mapping M
JOIN inf_product_category_mapping M2 ON M.product_id = M2.product_id
JOIN inf_products P ON P.product_id = M.product_id
WHERE M.category_id =1001 AND M2.category_id=200

Duration: 0.063 sec, fetched in: 0.000 sec

code:

SELECT M.product_id, P.product_enabled FROM inf_product_category_mapping M
JOIN inf_products P ON P.product_id = M.product_id
WHERE M.category_id IN(1001,200) GROUP BY M.product_id, P.product_enabled HAVING COUNT(M.product_id) >= 2

Duration: 0.218 sec, fetched in: 0.000 sec

Goed, ik switch denk ik voor kleinere sets maar over op de JOINS inderdaad; mocht de set nóg groter worden dan kan ik via PHP alsnog kijken of een IN() voordeliger is qua tijd.
Verder:

code:

SELECT M.product_id, P.product_enabled, SUM(S.stock_quantity) AS stock_quantity FROM inf_product_category_mapping M
JOIN inf_product_category_mapping M2 ON M.product_id = M2.product_id
JOIN inf_products P ON P.product_id = M.product_id
JOIN inf_stock S ON S.product_id = M.product_id
WHERE M.category_id = 1001
AND M2.category_id = 200
AND S.stock_id = 2
GROUP BY stock_id, product_id, product_enabled

Duration: 0.093 sec, fetched in: 0.000 sec

Dit valt inderdaad mee qua tijden. Hartelijk dank!
Beetje treurig inderdaad dat ik, ondanks dat JOINs in de StackOverflow thread al genoemd waren, vasthield aan de IN() en HAVING() clauses.

[GlowMouse]

Dit is hetzelfde probleem als besproken in MySQL, tags en performance, maar dan zonder ORDER BY.

[ Voor 12% gewijzigd door GlowMouse op 07-12-2011 18:33 ]

[Scyth]

GlowMouse schreef op woensdag 07 december 2011 @ 18:33:
Dit is hetzelfde probleem als besproken in MySQL, tags en performance, maar dan zonder ORDER BY.

Al 's gedacht aan een baan in de reclamebranche?
Nee, j/k, ik lees 'm even door. Mijn probleem is iets minder complex dan het probleem beschreven in jouw TS, maar ondanks dat wil ik graag dingen leren.

[pedorus]

Scyth schreef op woensdag 07 december 2011 @ 17:47:
Duration: 0.093 sec, fetched in: 0.000 sec

Dit is nog steeds niet echt snel, op basis van GlowMouse's resultaten zou je 0.000482s als duration verwachten, als ik het goed begrijp. Ik vermoed dat je ergens een index mist.

offtopic:
Het valt me trouwens op dat in Glowmouse's voorbeeld gemiddeld ~2 tags zijn uitgedeeld, waardoor compleet denormaliseren naar direct opvraagbare resultaten met combinaties van maximaal 4 tags ofzo waarschijnlijk prima te doen is.

Overigens kun je dit ook niet vergelijken met die ~0.5s, want die query klopt natuurlijk niet omdat je geen gebruik hebt gemaakt van een subquery in de from (joinen met inf_stock zorgt voor extra count, omgekeerd klopt ook de sum() niet met wat je wilt).

Waarom doe je trouwens niet direct (200 or 300) and (1000 or 1001), samenvoegen in PHP lijkt me wat onzinnig.

[Scyth]

pedorus schreef op woensdag 07 december 2011 @ 19:13:
[...]
Dit is nog steeds niet echt snel, op basis van GlowMouse's resultaten zou je 0.000482s als duration verwachten, als ik het goed begrijp. Ik vermoed dat je ergens een index mist.

Nee, want dat is waarschijnlijk op een échte server; dit draait op een desktop pc'tje.

pedorus schreef op woensdag 07 december 2011 @ 19:13:

offtopic:
Het valt me trouwens op dat in Glowmouse's voorbeeld gemiddeld ~2 tags zijn uitgedeeld, waardoor compleet denormaliseren naar direct opvraagbare resultaten met combinaties van maximaal 4 tags ofzo waarschijnlijk prima te doen is.

Heb ik al bij nagedacht, zie mijn tweede post (cachetables). Vind het echter erg lelijk en niet scalable genoeg. Deze situatie is niet representatief met wat ik uiteindelijk hiermee wil kunnen doen.

pedorus schreef op woensdag 07 december 2011 @ 19:13:
Waarom doe je trouwens niet direct (200 or 300) and (1000 or 1001), samenvoegen in PHP lijkt me wat onzinnig.

Voor gelijke lengtes zou dit inderdaad een mogelijkheid zijn. Thnx voor de tip.

[Scyth]

* Scyth performs ranzige kick

Na lang doorstoeien mbt. deze setup heb ik een query gevonden die de resultset behoorlijk snel uit de database weet te trekken. Om deze zoektocht dan zo goed als ten einde te brengen wil ik jullie het resultaat niet onthouden:

[code]
SELECT P.product_id FROM products P
NATURAL JOIN product_category_mapping M0
NATURAL JOIN product_category_mapping M1
WHERE M0.category_id IN(200,1004)
AND M1.category_id IN(300,1004)
AND P.product_enabled = 1
GROUP BY P.product_id
[/code]

Wellicht dat er nog meer optimalisaties toegepast kunnen worden, maar for now ben ik een gelukkig man.

Strike that, query gaat op z'n muil zodra er meerdere matchen moeten worden gemaakt. Ergens moet nog een OR in, maar daar ben ik dus nu mee aan het stoeien.

De tweede natural join joint op de category_id, niet op de product_id, waardoor de tweede join useless wordt. Uiteindelijk wel de producttabel als hoofdtabel gezet, waardoor de eindquery alsvolgt is geworden.

code:

SELECT P.product_id
FROM products P
JOIN product_category_mapping M0 ON M0.product_id = P.product_id
JOIN product_category_mapping M1 ON M1.product_id = P.product_id
WHERE M0.category_id IN(200,300,400)
AND M1.category_id IN(1001,1002,1003)
AND P.product_enabled = 1
GROUP BY P.product_id

[ Voor 35% gewijzigd door Scyth op 01-03-2012 18:03 ]

[TheNephilim]

Eens kijken of ik hier nog wat voorbeelden kan vinden van code die een soortgelijke contructie hebben...

Als ik het zou zie zou ik het met INNER JOINs oplossen.