Discussion :

[gotomtom]

J'ai la requête suivante qui met entre 0,6 et 0,8 seconde pour s'exécuter. Les tables ult_product_descriptions et product_descriptions comptent plus de 500'000 entrées.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
SELECT SQL_CALC_FOUND_ROWS products.product_id,
                                    IF(shared_descr.product_id IS NOT NULL, shared_descr.product, descr1.product) AS product,
                                    bc.country_code,
                                    100 - ((prices.price * 100) / list_price) AS sales_discount
FROM cscart_products AS products
LEFT JOIN cscart_product_descriptions AS descr1 ON descr1.product_id = products.product_id
AND descr1.lang_code = 'fr'
LEFT JOIN cscart_product_prices AS prices ON prices.product_id = products.product_id
AND prices.lower_limit = 1
INNER JOIN cscart_products_categories AS products_categories ON products_categories.product_id = products.product_id
INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id
AND (cscart_categories.usergroup_ids = ''
     OR FIND_IN_SET(0, cscart_categories.usergroup_ids)
     OR FIND_IN_SET(1, cscart_categories.usergroup_ids))
AND cscart_categories.status IN ('A',
                                 'H')
LEFT JOIN cscart_ult_product_descriptions shared_descr ON shared_descr.product_id = products.product_id
AND shared_descr.company_id = 1
AND shared_descr.lang_code = 'fr'
LEFT JOIN cscart_product_block_countries bc ON bc.product_id = products.product_id
WHERE 1
  AND cscart_categories.company_id = 1
  AND (products.usergroup_ids = ''
       OR FIND_IN_SET(0, products.usergroup_ids)
       OR FIND_IN_SET(1, products.usergroup_ids))
  AND products.status IN ('A')
  AND prices.usergroup_id IN (0,
                              0,
                              1)
GROUP BY products.product_id
HAVING sales_discount > 0
ORDER BY product ASC LIMIT 0,
                           8

Résultat du EXPLAIN

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
id	select_type		table					type 		        possible_keys							key		    key_len	ref							rows	     Extra
1	SIMPLE		prices				ref			usergroup,product_id,lower_limit,usergroup_id	lower_limit	    2		const							12676    Using where; Using temporary; Using filesort
1	SIMPLE		products				eq_ref		PRIMARY,status							PRIMARY	    3		prod.prices.product_id				1	     Using where
1	SIMPLE		descr1				eq_ref		PRIMARY,product_id						PRIMARY	    9		prod.prices.product_id,const		1	
1	SIMPLE		shared_descr			eq_ref		PRIMARY,product_id,company_id				PRIMARY	    13		prod.products.product_id,const,const	1	
1	SIMPLE		bc					ref			product_id								product_id	    3		prod.products.product_id			1	
1	SIMPLE		products_categories		ref			PRIMARY,pt							pt		    3		prod.prices.product_id				2	      Using index
1	SIMPLE		cscart_categories		eq_ref		PRIMARY,c_status,p_category_id				PRIMARY	    3		prod.products_categories.category_id	1	      Using where

Comment puis-je optimiser la requête ? Merci