[MySQL\PhP] Query optimaal?

[jopiek]

Na een topic van een paar dagen terug met een beetje verkeerde stemming (o.a. door eigen frustraties) het volgende:

ik heb (dat wisten sommigen als) een Tweakers.net pricewatch idee geimplementeerd maar dan voor LEGO. Nu was een bepaalde pagina ongelofelijk zwaar en dat in combinatie met wat uit reacties van het hardwareforum blijkt een verkeerd mobo + koeler in een el cheapo 1u kast zorgde voor een cpu die over de 80 graden werd. Nu werkt een en ander wel weer, de kast staat gewoon open te loeien tot er een nieuw mobo +cooler komt en dus ben ik de pagina na uitdrukkelijke waarschuwingen in dit forum gaan optimaliseren.
De automatische Dreamweaver code is flink aangepast zodat er geen geheugen meer verspild wordt etc.

Alleen ben ik aangekomen bij zo ongeveer de lastigste query van de site:
- het overzicht geven van de prijzen van een set
- waarbij: alleen de nieuwste prijs voor een bepaalde toko getoond wordt
- en gesorteerd wordt op prijzen (oplopend)

code:

SELECT DISTINCT set_in_shop.setid, set_in_shop.prijs, 
set_in_shop.user, set_in_shop.tijdstmp, set_in_shop.directurl, 
set_in_shop.shopid, set_in_shop.is_shop, set_in_shop.archief, shops . *
FROM set_in_shop
INNER JOIN shops ON set_in_shop.shopid = shops.uid WHERE (setid = 488)
AND (set_in_shop.tijdstmp IN (SELECT max(tijdstmp)
                             FROM set_in_shop AS setshop
                             WHERE (setid = 488) and (setshop.shopid = shops.uid)
                             GROUP BY setshop.shopid))
AND NOT (archief = 1)
ORDER BY set_in_shop.Prijs

Is hier nog een betere oplossing voor te vinden? Uiteraard wordt 488 gewoon een php variabele in de echte query...

[P_de_B]

Beetje meer info gewenst: welke MySQL versie, de layout van je tabellen en een klein voorbeeldje van de gewenste output. Dat is eenvoudiger dan de query die je nu hebt gaan ontleden

[Bosmonster]

tabel-aliassen kunnen je query al een stuk leesbaarder maken in ieder geval...

[frickY]

Juist.
Welke tabellen heb je, en welke gegevens wil je er uit halen aan de hand van welke gegevens (aan de hand van een 'setid' zo te zien?

Zorg in ieder geval voor Indexen op de kolommen waarop je join-ed, where-ed, of order-ed

Hoelang doet deze query er nu over om uit te voeren? En hoelang wanneer je de 2 querys (de hoofdquery en de subquery) apart uitvoert?

//edit
Zo te zien doe je die subquery alleen om de hoogste datum op te halen. Dat kan volgens mij ook door de tabel met zichzelf te joinen, en de hoogste tijdstmp te selecteren waar je in je WHERE op selecteerd.

SQL:

SELECT
  SIS.setid, 
  MAX(SIS2.tijdstmp) AS maxTijdstmp,
  SIS.prijs, 
  SIS.user, 
  SIS.tijdstmp, 
  SIS.directurl, 
  SIS.shopid, 
  SIS.is_shop, 
  SIS.archief, 
  S.*
FROM 
  set_in_shop AS SIS
INNER JOIN
  set_in_shop AS SIS2 ON SIS2.setid= SIS.setid
INNER JOIN
  shops AS S ON SIS.shopid = S.uid 
WHERE 
  SIS.setid = 488
  AND 
  SIS.tijdstmp = maxTijdstmp
  AND archief != 1
GROUP BY
  SIS.setid
ORDER BY
  SIS.Prijs

Deze query zal ongetwijfelt niet werken. Maar denk dat je het in die richting kunt zoeken.

[ Voor 62% gewijzigd door frickY op 24-08-2005 09:54 ]

[jopiek]

Een bepaalde LEGO set heeft

Dit is de output van de eerder genoemde query:
http://213.233.222.222/le...etoverzicht.php?setid=488

De structuur is als volgt:
deze query draait om 2 tabellen: de winkels en de sets

Een winkel verkoopt een set voor een bepaalde prijs
in set_in_shop staat dan een record met een id van een shop (shopid, komt overeen met de primary key in tabel shops) en een setnummer (setid, welke overeenkomt met primary key uit tabel sets).

Een prijswatcher kan alleen prijzen toevoegen, dus als er een nieuwe prijs gevonden wordt dan voegt hij/zij deze gewoon toe, de database onthoud de datum en toont alleen de laatst bekende prijs voor de set en bepaalde winkel.

[P_de_B]

Ik krijg een foutmelding op de link die je geeft: "unknown table shops in where clause". Het is echt het handigst als je even een overzichtje van je tabellen geeft.

[jopiek]

P_de_B schreef op woensdag 24 augustus 2005 @ 10:05:
Ik krijg een foutmelding op de link die je geeft: "unknown table shops in where clause". Het is echt het handigst als je even een overzichtje van je tabellen geeft.

Mja, kwam omdat ik ff de executiontimes bekeek en foutje maakte, ik probeer ff tabeloverzicht te fixen

Ondertussen de executiontimes:
Volledige query 0.270148 secs
Deel1 0.013676 secs
Deel2 0.012185 secs


uitvoer:
Overzicht van de beschikbare setprijzen voor set x
A-Tembo € 4.25 2611LB Delft 00-00-0000
Superspeelgoed.nl € 4.80 7543GG Enschede 00-00-0000
De speelgoedkamer € 4.99 6718GH Ede 00-00-0000
Shop at Home € 5.00 00-00-0000

Shops heeft de volgende velden:
`uid` int(11) NOT NULL auto_increment,
`naam` varchar(25) NOT NULL default '',
`keten` int(11) default NULL,
`straat` varchar(30) default NULL,
`no` int(11) default NULL,
`postcode` varchar(6) default NULL,
`telno` varchar(11) default NULL,
`url` varchar(50) default NULL,
`waardering` int(11) NOT NULL default '0',
`user` int(11) default NULL,
`woonplaats` varchar(35) default NULL,
`has_pab` tinyint(4) NOT NULL default '0',
`email` varchar(40) default NULL,
PRIMARY KEY (`uid`)

En de tabel set_in_shop met de prijs voor een set in bep. winkel:
`uid` int(11) NOT NULL auto_increment,
`setid` int(11) NOT NULL default '0',
`prijs` decimal(10,2) NOT NULL default '0.00',
`user` int(11) NOT NULL default '0',
`tijdstmp` timestamp NOT NULL default CURRENT_TIMESTAMP,
`directurl` varchar(150) default NULL,
`shopid` int(11) NOT NULL default '0',
`is_shop` tinyint(4) NOT NULL default '1',
`archief` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`uid`)

[ Voor 65% gewijzigd door jopiek op 24-08-2005 10:18 ]

[frickY]

Gooi sowieso die DISTINCT eruit en gebruik GROUP BY.

En elke set staat dus zo vaak in de dataabse als dat er winkels zijn die hem verkopen?

Je hebt niet een tabel met alle sets. Een tabel met alle winkels. En een koppeltabel met een set, een winkel, een datum en een prijs?

[jopiek]

frickY schreef op woensdag 24 augustus 2005 @ 10:17:
Gooi sowieso die DISTINCT eruit en gebruik GROUP BY.

En elke set staat dus zo vaak in de dataabse als dat er winkels zijn die hem verkopen?

Je hebt niet een tabel met alle sets. Een tabel met alle winkels. En een koppeltabel met een set, een winkel, een datum en een prijs?

Ja, (uiteraard) heb ik wel dat laatste, heb wel verschillende vakken m.b.t. relationele databases gehad in het verleden... Alleen de prijsinformatie is redundant omdat ik in de toekomst net zoals op de FP een grafiekje of zo wil laten zien...

Die distinct is idd raar dat ik die daar heb staan, wellicht nog rudimentair van het oplossen van een ander probleem. Maar de Group by, waar zou ik die nog moeten doen dan?

[ Voor 9% gewijzigd door jopiek op 24-08-2005 10:21 ]

[ACM]

Je originele query ziet er overigens prima uit. Op wat kleine dingetjes na, zo zou ie toch goed moeten zijn:

SQL:

SELECT 
   sis.setid, sis.prijs, sis.user, sis.tijdstmp, sis.directurl, 
   sis.shopid, sis.is_shop, sis.archief, s.*
FROM set_in_shop sis
INNER JOIN shops s ON sis.shopid = s.uid
WHERE 
   sis.setid = 488
AND set_in_shop.tijdstmp = (SELECT max(tijdstmp)
                             FROM set_in_shop AS setshop
                             WHERE setshop.setid = sis.setid
                                          AND setshop.shopid = s.uid)
AND sis.archief != 1
ORDER BY sis.prijs

Hier heb ik met name de GROUP BY en IN bij je subquery vervangen/weggelaten en je DISTINCT weggeveegt bovenin.
Als dit traag is controleer dan of je een correcte index op je set_in_shop-tabel hebt. Met name een index op de combinatie "shopid, setid" zal het erg leuk doen voor je performance, wellicht moet je zelfs "shopid, setid, tijdstmp" gebruiken.

Controleer ook met het EXPLAIN-statement of die index wel gebruikt wordt.
Mocht het met zo'n goede index-setup nog steeds te sloom zijn... MySQL is geen echte topspeler als het aankomt op subqueries, dus dan moet je die zien te elimineren en dat is in dit geval niet echt mogelijk.

Wat je daar tegen zou kunnen doen is wat bewerkelijker bij het invoeren van prijzen, maar kan deze query wel significant verbeteren. Je zou bijvoorbeeld het archief-veld kunnen gebruiken om aan te geven dat een prijs niet de meest recente is, waardoor je die hele subquery overboord kan zetten en enkel nog op zoek moet naar de correcte archiefwaarden. Uiteraard moet je er voor zorgen dat er maximaal 1 prijs per shop per set dan een goede archiefwaarde heeft

[ Voor 7% gewijzigd door ACM op 24-08-2005 11:10 ]

[jopiek]

ACM schreef op woensdag 24 augustus 2005 @ 11:08:
Als dit traag is controleer dan of je een correcte index op je set_in_shop-tabel hebt. Met name een index op de combinatie "shopid, setid" zal het erg leuk doen voor je performance, wellicht moet je zelfs "shopid, setid, tijdstmp" gebruiken.

Het was idd traag, nog tager dan de normale query, met de index op de 3 velden is het echter supersnel!!! Wow, wist niet dat dat zoveel uitmaakte:
0.006833 secs (was ong. 0.270148 secs)

Archief wil ik echter gebruiken voor als de winkel de set de deur uit doet, wellicht pas ik een en ander echter toch nog aan met die tip, maar die indexering is al een heel goede verbetering!

[ Voor 23% gewijzigd door jopiek op 24-08-2005 11:37 ]