Query optimaliseren

Door Ruud van Dijk op 16-01-2012 13:46 gewijzigd op 16-01-2012 13:46

1.626 views

Ik heb de volgende query:

SELECT DISTINCT p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, p.products_image, p.products_barcode
FROM products p
LEFT JOIN products_description pd ON ( p.products_id = pd.products_id AND pd.language_id = '4' )
LEFT JOIN specials s ON p.products_id = s.products_id
INNER JOIN products_to_stores p2s ON p.products_id = p2s.products_id
INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id
LEFT JOIN categories c ON p2c.categories_id = c.categories_id
WHERE p2s.stores_id = '20'
AND p.products_status =1
AND c.categories_status >=1
AND p2c.categories_id = '607'
AND p.products_id <> '70679816'
AND (
( s.status = 1 AND s.specials_new_products_price > 4.91598 and s.specials_new_products_price < 6.00842 )
OR ( s.status = 0 or s.status is null AND p.products_price > 4.91598 and p.products_price < 6.00842 )
)

Via mysqslap doet hij er 17.79 over (bij 100 querys tegelijk)
hiervoor had ik de query als 26 sec verwerkingstijd dus ik heb al 9 sec winst gemaakt ten opzichte van een oudere versie van deze query.
Maar ik wil nog verder optimaliseren

Als ik hem zo uitvoer:
SELECT DISTINCT p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, p.products_image, p.products_barcode, pd.products_id, pd.products_description, p.products_status
FROM products p LEFT JOIN products_description pd ON ( p.products_id = pd.products_id AND pd.language_id = '4' AND p.products_status =1 )
LEFT JOIN specials s ON p.products_id = s.products_id
INNER JOIN products_to_stores p2s ON p.products_id = p2s.products_id AND p2s.stores_id = '20'
INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id AND p2c.categories_id = '607'
LEFT JOIN categories c ON p2c.categories_id = c.categories_id AND c.categories_status >= 1
WHERE p.products_id <> '70679816'
AND (
( s.status = 1 AND s.specials_new_products_price > 4.91598 and s.specials_new_products_price < 6.00842 )
OR ( s.status = 0 or s.status is null AND p.products_price > 4.91598 and p.products_price < 6.00842 )
)

dan is de verwerkingstijd 2.5 sec voor 100 querys echter word de pd.description NULL zodra de products_status op 0 staat

Weet iemand nog methodes om de query te verlichten?
De 2e query is dus niet een optie helaas

Jelle -

16-01-2012 13:50

Heb je indexen gebruikt? Goede indexen kunnen query's gigantisch verbeteren in uitvoer tijd.

Ruud van Dijk

16-01-2012 13:53

Als het goed is wel
Dit is de explain output:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE c const PRIMARY,categories_status PRIMARY 4 const 1 Using temporary
1 SIMPLE p ref PRIMARY,products_status products_status 1 const 16296 Using where
1 SIMPLE pd eq_ref PRIMARY PRIMARY 8 p.products_id,const 1 Using index; Distinct
1 SIMPLE s ref idx_specials_products_id idx_specials_products_id 4 aximax_db.p.products_id 2 Using where; Distinct
1 SIMPLE p2c eq_ref PRIMARY PRIMARY 8 p.products_id,const 1 Using where; Using index; Distinct
1 SIMPLE p2s eq_ref PRIMARY PRIMARY 8 p.products_id,const 1 Using index; Distinct

Jelle -

16-01-2012 14:03

1 SIMPLE p ref PRIMARY,products_status products_status 1 const 16296 Using where

Lijkt mij dat ie hier nog geen index gebruikt, 16000 rijen * het aantal rijen dat je in totaal ophaalt lijkt me toch wel een redelijk klap.

Ruud van Dijk

16-01-2012 14:09

op products_status staat wel een index

Jelle -

16-01-2012 14:13

Ik zie meerdere malen ..._id = '1'
Wat als je die getallen eens als getallen behandeld, misschien dat dat wel eens een effect kan hebben.

Ruud van Dijk

16-01-2012 14:18

Smur f op 16/01/2012 14:13:42

Ik zie meerdere malen ..._id = '1'
Wat als je die getallen eens als getallen behandeld, misschien dat dat wel eens een effect kan hebben.

nope ook 17.5 sec

Indexen:

Sleutelnaam Type Unieke waarde Gecomprimeerd Kolom Kardinaliteit Collatie Null Opmerking
PRIMARY BTREE Ja Nee products_id 49387 A
idx_products_date_added BTREE Nee Nee products_date_added 0 A
products_status BTREE Nee Nee products_status 0 A
products_model BTREE Nee Nee products_model 0 A YES
manufacturers_id BTREE Nee Nee manufacturers_id 0 A YES
products_tax_class_id BTREE Nee Nee products_tax_class_id 0 A
products_barcode BTREE Nee Nee products_barcode 0 A YES
products_besteld BTREE Nee Nee products_besteld 0 A
products_sku BTREE Nee Nee products_sku 0 A YES

Reageren

Inloggen om te reageren