[mySQL] Trage query

[Bio]

SQL:

SELECT 

shop_items.title AS title, 
shop_items.price AS price, 

shop_stock.stock AS stock 
    
FROM shop_items LEFT JOIN shop_stock ON (shop_items.num=shop_stock.id) 
    
ORDER BY shop_items.title ASC

Ik wil graag deze query uitvoeren, de tabellen shop_items en shop_stock zijn allebei ongeveer 24.000 rijen. Nu is het probleem dat deze query echt minutenlang bezig is, ik heb begrepen dat het iets met indexen is, en ik heb shop_items.num en shop_stock.id primary key gemaakt, maar het duurt nog steeds zo lang.

dit is wat ik met explain krijg

code:

id  select_type  table      type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE   shop_items     ALL     NULL        NULL    NULL        NULL    24640   Using temporary; Using filesort
1   SIMPLE   shop_stock     ALL     NULL        NULL    NULL        NULL    23678

Iemand die het weet?

[Kalentum]

Wat is de output van EXPLAIN als je die ORDER BY weghaalt? Misschien dat er op shop_items.title een index moet?

[Bio]

rutgerw schreef op dinsdag 08 januari 2008 @ 12:33:
Wat is de output van EXPLAIN als je die ORDER BY weghaalt? Misschien dat er op shop_items.title een index moet?

Die blijft hetzelfde, behalve de kolom extra. Deze wordt leeg.

edit: als ik een index op shop_items.title zet en ik zet de ORDER BY weer in de query is de EXPLAIN weer zoals in het begin.

[ Voor 18% gewijzigd door Bio op 08-01-2008 12:40 ]

[Wortelsoep]

Ik denk dat het wel gaat schelen als je een index plaatst op shop_items.title. Dat kun je ook makkelijk testen door de query uit te voeren zonder de ORDER. Als die wel snel gaat, heb je in ieder geval een bottleneck te pakken.

[ Voor 49% gewijzigd door Wortelsoep op 08-01-2008 12:41 ]

[whoami]

Heb je een LEFT JOIN nodig ? Is een INNER JOIN niet voldoende ?
Waar heb je indexen op liggen ?

Je hebt een PK gelegd op de velden 'num' (in shop_items) en id in die andere tabel ? Dus, je joined op 2 PK's ?

[Bio]

whoami schreef op dinsdag 08 januari 2008 @ 12:48:
Heb je een LEFT JOIN nodig ? Is een INNER JOIN niet voldoende ?
Waar heb je indexen op liggen ?

Je hebt een PK gelegd op de velden 'num' (in shop_items) en id in die andere tabel ? Dus, je joined op 2 PK's ?

Ik heb wel een LEFT JOIN nodig, als er geen voorraad is, wil ik nog wel het product weten. ik heb nu een index op title, en de join is inderdaad op 2 PK's.

Als ik ORDER BY weghaal gaat het nog steeds veel te traag, het gaat zo traag dat ik nog geen een x het resultaat van de query heb gezien. Net heb ik hem 10 minuten laten staan (dat was wel met ORDER BY) en toen was hij nog keihard bezig.

[ Voor 20% gewijzigd door Bio op 08-01-2008 12:54 ]

[P.O. Box]

de query is volgens mij niet zo gek... is er niet iets met je databaseserver aan de hand?

[Bio]

Edwardvb schreef op dinsdag 08 januari 2008 @ 13:25:
de query is volgens mij niet zo gek... is er niet iets met je databaseserver aan de hand?

De server is volgens mij niets mis mee, ligt het niet gewoon aan de hoeveelheid data die gejoind moet worden? (lijkt me ook niet normaal eigenlijk)

[Kettrick]

Ik doe al een tijdje niets meer met mysql, maar moet je niet nog een vacuum/analyse doen voordat je indexes gebruikt worden ?

[Kalentum]

Ik denk toch dat er iets mis is met de indexen. Uit die EXPLAIN blijkt dat er geen enkele index wordt gebruikt en ook zie je dat Mysql het gebruik van indexen niet eens overweegt. Volgens mij gebeurt dat als mysql een join moet doen op velden waar geen index voor aangemaakt is.

Met deze aantallen zou deze query gewoon snel moeten zijn.

[Bio]

Ik moet eerlijk zeggen dat ik ook weinig weet van indexen, maar ik heb ze gewoon in phpmyadmin aangemaakt. Dat zou toch moeten werken?

dat van die EXPLAIN viel me ook al op.

[ Voor 13% gewijzigd door Bio op 08-01-2008 13:41 ]

[Icelus]

Jo-hannes schreef op dinsdag 08 januari 2008 @ 13:41:
Ik moet eerlijk zeggen dat ik ook weinig weet van indexen, maar ik heb ze gewoon in phpmyadmin aangemaakt. Dat zou toch moeten werken?

dat van die EXPLAIN viel me ook al op.

Ja, dat zou moeten werken.
Met je het commando:

SQL:

DESCRIBE shop_items

kun je wat informatie over een tabel krijgen; zie je de indices daar ook bij staan?

[Kettrick]

Laat anders je tabel structuur eens zien, misschien probeer je verschillende datatypen te joinen ?

[Bio]

RoeLz schreef op dinsdag 08 januari 2008 @ 13:45:
Laat anders je tabel structuur eens zien, misschien probeer je verschillende datatypen te joinen ?

omg held

om de een of andere rare reden was de collatie anders in de tabel shop_stock

bedankt!

[Kettrick]

u bent welkom

erg vervelend dat de mysql explain functie dit niet laat zien, in een "normaal" query plan zie je dit veel sneller terug

[Bio]

Ondertussen heb ik de query wat uitgebreid naar dit:

SQL:

SELECT 

shop_items.item_id AS item_id, shop_items.category_id AS category_id, 
shop_items.title AS title, shop_items.price AS price, 
shop_items.artikelnummer AS num, shop_items.enabled AS enabled, 

shop_categories.title AS merk, shop_stock.stock AS stock 
    
FROM shop_items 
LEFT JOIN shop_stock 
     ON (shop_items.num=shop_stock.id) 
LEFT JOIN shop_categories 
     ON ( SUBSTR(shop_items.copid,1,3) = shop_categories.code) 

ORDER BY CONCAT(shop_categories.title,shop_items.title)

Deze duurt langer dan 3 seconden, kan ik daar nog iets aan optimaliseren? ik kan me zo voorstellen dat dit wel een zware query is...

[jan-marten]

Iets anders verzinnen voor SUBSTR(shop_items.copid,1,3)

B.v. extra veld voor deze waarde en gooi hier dan een index overheen.

[Bio]

Hm ik ben er achter gekomen dat het de CONCAT is, zijn daar nog alternatieven voor?

[ Voor 26% gewijzigd door Bio op 09-01-2008 14:37 ]

[whoami]

Aangezien je een functie gebruikt in je ORDER BY, kunnen daar geen indexen gebruikt worden (mochten er indexen liggen op title in shop_categories & shop_items.

Waarom ga je die 2 strings eigenlijk gaan concatten en daar op sorteren ?
Waarom doe je niet gewoon:

code:

ORDER BY shop_categories.title, shop_items.title

[Bio]

whoami schreef op woensdag 09 januari 2008 @ 14:44:
Aangezien je een functie gebruikt in je ORDER BY, kunnen daar geen indexen gebruikt worden (mochten er indexen liggen op title in shop_categories & shop_items.

Waarom ga je die 2 strings eigenlijk gaan concatten en daar op sorteren ?
Waarom doe je niet gewoon:

code:

ORDER BY shop_categories.title, shop_items.title

Dat heb ik inderdaad net veranderd mede dankzij deze pagina, het duurt nu nog steeds 2 seconden

edit: als ik alleen op shop_categories.title sorteer duurt het 1,3 seconden, als ik alleen op shop_items.title sorteer is het een acceptabel aantal ms...

[ Voor 11% gewijzigd door Bio op 09-01-2008 14:48 ]

[whoami]

Bij je 2de join kunnen ook geen indexen gebruikt worden.
Zowiezo stel ik me vragen bij je datamodel, als je moet gaan joinen op een gedeelte van een veld.

edit: als ik alleen op shop_categories.title sorteer duurt het 1,3 seconden, als ik alleen op shop_items.title sorteer is het een acceptabel aantal ms...

En wat denk je dan ? Dat er geen index ligt op shop_categories.title...

[ Voor 43% gewijzigd door whoami op 09-01-2008 14:49 ]

[Bio]

whoami schreef op woensdag 09 januari 2008 @ 14:48:
Bij je 2de join kunnen ook geen indexen gebruikt worden.
Zowiezo stel ik me vragen bij je datamodel, als je moet gaan joinen op een gedeelte van een veld.


[...]
En wat denk je dan ? Dat er geen index ligt op shop_categories.title...

Die index zit er dus wel

Het datamodel vind ik zelf ook niets, maar het is niet mijn ontwerp, het wordt ergens uit geimporteerd.

[Confusion]

Gebruik je InnoDb tables of MyISAM? In het geval van het laatste: verander het eens in het eerste en leg een foreign key.