Very slow SQL queries

[frimipiso]

Hi,

 

after discovering that my shop has gotten incredibly slow, I spotted

the evil SQL queries that take an incredible amount of time (sometimes up

to 20s per query). They all have the following "where" clause in common:

 

where p.products_status = '1' and p.products_id = p2c.products_id

 

After reading a bit about mysql table indexing I checked whether an

index is used for these select queries and I get the following response

using the "explain" command:

 

table	 type	 possible_keys	 key	 key_len	 ref	 rows	 Extra
p	index	PRIMARY	PRIMARY	5	NULL	873	Using where

 

So no index is used for queries in the products table and all products are searched for each

query. For all other tables in these queries, the indexes are used.

 

How can I avoid this? Currently some page parse times can take up to a minute.

 

Thanks a lot for your help!

 

Jens

 

By the way: these are the bad queries:

 

		   [22] => select distinct m.manufacturers_id as id, m.manufacturers_name as name from zzbaf_products p, zzbaf_products_to_categories p2c, zzbaf_manufacturers m where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and p.products_id = p2c.products_id and p2c.categories_id = '164' order by m.manufacturers_name


10s:

		[24] => select count(p.products_id) as total  from zzbaf_products_description pd, zzbaf_products p left join zzbaf_manufacturers m on p.manufacturers_id = m.manufacturers_id left join zzbaf_specials s on p.products_id = s.products_id, zzbaf_products_to_categories p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '2' and p2c.categories_id = '164'


23s:

		[27] => select p.products_image, pd.products_name, p.products_quantity, p.products_id, p.manufacturers_id, p.products_price, p.products_ordered, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from zzbaf_products_description pd, zzbaf_products p left join zzbaf_manufacturers m on p.manufacturers_id = m.manufacturers_id left join zzbaf_specials s on p.products_id = s.products_id, zzbaf_products_to_categories p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '2' and p2c.categories_id = '164' order by p.products_id DESC limit 0, 9

 

and these are the indexes for my products table:

 

PRIMARY	 PRIMARY	 1163 			 products_id
products_status
idx_products_date_added	 INDEX	 1 			 products_date_added
idx_products_model	 INDEX	 1163 			 products_model
idx_manufacturers_id	 INDEX	 32 			 manufacturers_id

[spax]

There is a thread dedicated to optimizing an osCommerce store:

 

http://www.oscommerce.com/forums/index.php?showtopic=119077

 

Also, read through posts by "Monika in Germany". You will learn lots from Monika, particularly about databases, indexes etc, as she is a bit of a wiz in that department.