Query controleer item en categorie koppelingen langzaam
Heb even jullie hulp nodig bij het volgende:
Ten eerste, onderstaande situatie is fictie wat de data betreft. Heb even een "auto" als onderwerp genomen om het makkelijker te begrijpen.
Mijn tabellen:
CAR (ID, NAME)
CATEGORY (ID, PARENT_ID, NAME)
CAR_IN_CATEGORY (ID, CAR_ID, CATEGORY_ID)
Situatie:
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.
2 snelle[=categorie], rode[=categorie], luxe[=categorie] auto's
0 snelle[=categorie], gele[=categorie], luxe[=categorie] auto's
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
Anyone?
Thanks!
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...
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.
Alvast bedankt.
Het antwoord ligt dus ergens inderdaad bij de queries.
Misschien dat jij me dan kan helpen.
Hierbij wat meer info van wat ik gebruik:
item
-id (PK)
-type (offroad,rally,straat,race)
category
-id (PK)
-parent_id
-name (rood)
item_in_category
-id (PK)
-item_id (FK)
-category_id (FK)
Nogmaals bovenstaande data is fictie.
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;
Gewijzigd op 26/01/2011 11:28:57 door Dan Me
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
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;
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;
en beoordeel het resultaat.
Code (php)
1
2
3
2
3
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
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..
Gewijzigd op 26/01/2011 12:41:09 door Dan Me
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?
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?
Je krijgt nu namelijk minimaal 77751 records in beeld en dat kost even tijd.
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..
Ik neem aan dar je merk, bouwjaar en model niet in categorie stopt omdat dat dan juist te maken heeft met te ver uitnormaliseren en je performance niet te goede komt. In de 3e normaalvorm horen merk, bouwjaar en model tot de entiteit CAR omdat ze maar 1x voor kunnen komen bij CAR.