Een auto kan aan 1 of meerdere categorieën gekoppeld zijn op welke niveau dan ook.
Het systeem gaat middels een stappen proces door de categorieën. Bij elke stap (categorie boom) moet ik controleren welke categorieen ik nog moet laten zien. Alleen degene welke daaronder nog een auto gekoppeld hebben. Bijv.
De gele auto hoef ik dus niet te laten zien. Nu heb ik de volgende query om per categorie weergave te controleren of er auto's onder hangen. Ik moet dus alle auto's hebben die gekoppeld zijn aan meerdere geselecteerde categorieën. Een AND verhaal dus, niet een OR.
Huidige QUERY:
SELECT car.id
FROM car_in_category cc
JOIN car c ON car.id = cc.car_id
WHERE cc.category_id IN (119456,119458,119460,119462,119464,119738)
GROUP BY car.id
HAVING COUNT(*) = 6;
119456,119458,119460,119462,119464,119738 <= dit zijn dus de geselecteerde categorieën.
Probleem:
Ik heb op dit moment 60.000 auto's in mijn database welke allen gelinkt zijn aan 1 of meerdere categorieën. Als ik bovenstaande query uitvoer ben ik binnen deze situatie 0.5 tot 1.4 sec bezig. Als ik dat voor 10 categorieën wil doen op een pagina is dat uiteraard niet acceptabel.
Iemand een idee waaraan dit kan liggen of hoe het beter zou kunnen?
Ik heb indexes op alle waardes staan welke gebruikt worden.
Dat is dan ook geen rocket-science. Dus daar ligt het dan toch ook niet aan?
car.id PK
category.id PK
car_in_category.car_id FK
car_in_category.category_id FK
Vreemd datamodel en waarom is de having?
Vreemd is dat rood/snel/luxe allen een categorie zijn.
Het lijkt mij dat je datamodel een beetje te ver is uitgenormaliseerd door alles in categorie te stoppen. Is merk (ford, opel) ook een categorie en zonee waarom niet? Ongetwijfeld heb je zeer valide redenen voor dit vreemde datamodel, ik ben benieuwd...
Het gaat hier niet om het datamodel. Deze is goed en kan volgens mij niet veel mis aan zijn.
item
category (id, parent_id)
item_category
Daarnaast is bovenstaande data fictie. Ik heb gewoon een situatie waarin ik veel categorieën heb op verschillende niveaus waaraan items gekoppeld kunnen worden. Niet meer en niet minder.
Het probleem waar jullie mij kunnen helpen is:
Waarom zou deze query zo langzaam zijn bij een tabel met 60.000 items.
Ik ben vast niet de eerste met een tabel welke dit aantal records bevat.
Een tabel met 60.000 records is datatechnisch gezien geen grote tabel en zal op zich geen performance problemen opleveren. Het ligt altijd aan de manier waarop je de tabel uitvraagt. Worden de indexen goed gebruikt of heb je (herhalende) full-table scans? Je moet full-table scans vermijden en wegwerken. Hoe is je query plan?
Nu heb ik bijv. 3000 type rally auto's en 60.000 type straat auto's.
De query waar het om gaat, heeft altijd betrekking op 1 type auto.
Echter als ik dus de SELECT beperk met het volgende, gaat de snelheid nog steeds niet omhoog.
SELECT car.id
FROM car_in_category cc
JOIN car c ON car.id = cc.car_id
WHERE cc.category_id IN (119456,119458,119460,119462,119464,119738)
AND car.type = "straat"
GROUP BY car.id
HAVING COUNT(*) = 6;
maak eens een explain om te kijken wat er gebeurt met je query.
EXPLAIN
SELECT car.id
FROM car_in_category cc
JOIN car c ON car.id = cc.car_id
WHERE cc.category_id IN (119456,119458,119460,119462,119464,119738)
AND car.type = "straat"
GROUP BY car.id
HAVING COUNT(*) = 6;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE c index PRIMARY PRIMARY 3 NULL 77751 Using index
1 SIMPLE cc ref car_id,category_id car_id 3 db.car.id 2 Using where
Hoop dat je hier een beetje uit komt, krijg het niet echt recht..
Ziet er normaal uit, er worden 77751 rows opgehaald uit table c
Hoe snel kan je systeem i/o dat verwerken en wat doet de having count(*) in de query?
Op welk platform (server) doe je deze bewerking?
HAVING COUNT(*) heeft te maken dat ik alleen de items wil hebben die aan ALLE categorieën zijn gekoppeld. Dus geen OR situatie.
Ik doe deze bewerking op een Linux CentOS systeem.
Intel(R) Xeon CPU @ 2.53 GHz
Zou toch goed moeten zijn?
Hoe zou jij die 77751 rows verminderen? Ik hoef in principe niet te weten hoeveel items ik heb. Als ik maar weet dat het in ieder geval meer dan 0 zijn?
Nee de output is gegroupeerd (GROUP BY product.pid) dus krijg wel minder resultaten. Maar ik ben het ermee eens dat dit te veel is. Helemaal als je vervolgens hier niet veel mee doet. Ik controleer hiermee alleen of ik een resultaat heb,
Ik heb in ieder geval deze query nodig om te controleren of ik een resultaat heb middels de beschikbare waardes (119456,119458,119460,119462,119464,119738). Dat mag dus ook korter zijn. Een simpele limit toepassen werkt niet..
