[SQL] Query opbouw vergelijking, query loopt vast?

[sebasmac]

Beste mede tweakers,

Voor een school project ben ik bezig een prijsvergelijk systeem op te zetten, de vergelijking bestaat uit een product die gekoppeld is aan een merk die weer gekoppeld is aan een leverancier. hierbij hebben ze allemaal prioriteiten, zo kan het dus zijn dat een poduct prio 1 heeft met als merk prio 1 en de leverancier ook prio 1 dat maakt dus 3, hoe hoger deze waarde is hoe belangrijker het is dat de prijs van dit product snel word vergeleken met andere prijzen. Om een lijst te maken van alle producten die vergeleken moeten worden word er een SQL query gedraaid. Deze ziet er als volgt uit:

SQL:

 
SELECT ( pricecompare_products.importanter + pricecompare_brands.importanter + pricecompare_presenter.importanter ) AS importent, 
    pricecompare_products.`Name` AS productname, 
    pricecompare_presenter.`Name` AS provider, 
    pricecompare_brands.`Name` AS brandname, 
    pricecompare_presenter.ID AS presenterID, 
    pricecompare_products.ID AS productID, 
    pricecompare_products.Mediaurl AS mediaURL, 
    pricecompare_presenter.Country AS providercountry
FROM pricecompare_competitor INNER JOIN pricecompare_products ON pricecompare_competitor.BrandID = pricecompare_products.BrandID
     INNER JOIN pricecompare_brands ON pricecompare_products.BrandID = pricecompare_brands.ID
     INNER JOIN pricecompare_presenter ON pricecompare_competitor.PresenterID = pricecompare_presenter.ID
WHERE pricecompare_products.active = '1' 
ORDER BY importent DESC
 

Dit werkt prima, de prioriteiten (importent) worden netjes bij elkaar opgeteld en gesorteerd, zodra de prijsvergelijk start komt dus ook netjes product 1 in de lijst naar boven en kan deze vergeleken worden. Maar elke keer als de prijs vergelijking opent krijg je dus hetzelfde product, dus wat heb ik bedacht. Zodra er een prijs geinsert word krijgt deze in de price tabel lastprice = 1 en komt de prioriteit op -100 hiebij komt die dus weer helemaal onder in het rijtje en als je de prijsvergelijk opent begin je weer bij waar je gebleven was, tot zover mijn theorie.

Om dit te realiseren heb ik een query gemaakt die ook een leftjoin doet in de prijstabel want zodra deze niet bestaat moet die wel naar voren komen alleen dan met een hogere prio. zodra deze niet bestaat moet de prio op 0 gezet worden en komt deze naar boven. dit is de volgende query:

SQL:

 
SELECT CASE WHEN COUNT(pricecompare_price.importanter) = NULL THEN (0 + pricecompare_products.importanter + pricecompare_brands.importanter + pricecompare_presenter.importanter) ELSE (pricecompare_price.importanter + pricecompare_products.importanter + pricecompare_brands.importanter + pricecompare_presenter.importanter) END AS importent, 
    pricecompare_products.`Name` AS productname, 
    pricecompare_presenter.`Name` AS provider, 
    pricecompare_brands.`Name` AS brandname, 
    pricecompare_presenter.ID AS presenterID, 
    pricecompare_products.ID AS productID, 
    pricecompare_products.Mediaurl AS mediaURL, 
    pricecompare_price.product_url, 
    pricecompare_presenter.Country AS providercountry
FROM pricecompare_competitor INNER JOIN pricecompare_brands ON pricecompare_competitor.BrandID = pricecompare_brands.ID
     INNER JOIN pricecompare_presenter ON pricecompare_competitor.PresenterID = pricecompare_presenter.ID
     INNER JOIN pricecompare_products ON pricecompare_competitor.BrandID = pricecompare_products.BrandID
     LEFT JOIN pricecompare_price ON pricecompare_competitor.PresenterID = pricecompare_price.AanbiederID AND pricecompare_products.ID = pricecompare_price.ProductID AND pricecompare_price.last_price = '1'
WHERE pricecompare_products.active = '1' ORDER BY importent DESC LIMIT 0,1
 

Als ik deze query uitvoer, krijg ik een mooi ronddraiend rondje te zien, en uiteindelijk zie ik aardig wat CPU belasting op mijn machine maar hoelang ik ook wacht er gebeurd niks... Ik zie vast iets stoms over het hoofd of mijn hele theorie klopt niet

Is er iemand die mij kan helpen?

Alvast bedankt!

[GlowMouse]

Je moet één index hebben op de vier kolommen (PresenterID,AanbiederID,ProductID,last_price), de volgorde maakt niet uit. Waarschijnlijk kun je ook wel uit met een kleinere index, maar dat hangt van je data af.

[sebasmac]

GlowMouse schreef op donderdag 07 augustus 2014 @ 00:49:
Je moet één index hebben op de vier kolommen (PresenterID,AanbiederID,ProductID,last_price), de volgorde maakt niet uit. Waarschijnlijk kun je ook wel uit met een kleinere index, maar dat hangt van je data af.

Ik heb de index aangemaakt, maar nog steeds blijft de query hangen... De price tabel bestaat uit ongeveer 20000 records, en de product tabel uit ongeveer 6700 records x 5 aanbieders loopt dit aantal wel op in de query, maar ik limit hem tot max 1 dus ik snap niet dat het resultaat zolang duurd terwijl in de eerste query het binnen enkele milliseconde op mijn scherm staat zelfs al limit ik hem niet (+/- 25000 resultaten)

[GlowMouse]

Heb je de output van EXPLAIN?

[sebasmac]

GlowMouse schreef op donderdag 07 augustus 2014 @ 13:38:
Heb je de output van EXPLAIN?

SQL:

1   SIMPLE  pricecompare_competitor ALL                 146 Using temporary
1   SIMPLE  pricecompare_brands eq_ref  PRIMARY PRIMARY 4   ntba_prod.pricecompare_competitor.BrandID   1   
1   SIMPLE  pricecompare_presenter  ALL PRIMARY             6   Using where; Using join buffer
1   SIMPLE  pricecompare_products   ALL                 5728    Using where; Using join buffer
1   SIMPLE  pricecompare_price  ALL                 17195   

EDIT:

Niet echt makkelijke te lezen vandaar even plaatje bijgevoegd:

[ Voor 13% gewijzigd door sebasmac op 08-08-2014 00:43 ]

[Knutselsmurf]

Ik denk dat het tijd is om eens te gaan kijken wat INDEXen zijn, om deze vervolgens op de juiste plaatsen aan je database toe te voegen.

[Tom-W]

Index op pricecompare_price (last_price, AanbiederID, ProductID) is eigenlijk een minimum. Zuiver voor deze query zou ik last_price vooraan zetten. Op die manier krijg je ook nog een optimalisatie van je cache door meteen alles met last_price != 1 in een ander deel van de index te zetten wat je IO ten goede komt. Eventueel kan je de index nog uitbreiden met de kolom importanter, dan kan je IO naar de base table volledig uitsluiten op deze query.
Ook een index op pricecompare_products(BrandID) toevoegen. Ik heb de indruk dat je daar een andere primary key hebt (waarschijnlijk ID), dus een goed idee om die toe te voegen.

Btw, voor de leesbaarheid van je query kan je tabellen ook een alias geven:
SELECT compet.name, brands.name
FROM pricecompare_competitor compet INNER JOIN pricecompare_brands brands ON compet.BrandID = brands.ID

Verder lijkt me dat je in de eerste lijn ook gewoon kan schrijven:
SELECT nvl(pricecompare_price.importanter, 0) + pricecompare_products.importanter + pricecompare_brands.importanter + pricecompare_presenter.importanter AS importent