REALLY slow MySQL queries

[Rightfield]

I've been having complaints about the slowness of my site quite often, so I set up a log file for slow queries. And I'm having some very very large ones. I'll copy a few of them here.

# Time: 040411 20:36:38
# User@Host: shoppingcart[shoppingcart] @ localhost []
# Query_time: 25  Lock_time: 0  Rows_sent: 20  Rows_examined: 8660
select p.products_image, pd.products_name,  p.products_id, p.manufacturers_id, p.products_price, 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 products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '2060' order by pd.products_name limit 0, 20;
# User@Host: shoppingcart[shoppingcart] @ localhost []
# Query_time: 17  Lock_time: 0  Rows_sent: 10  Rows_examined: 19141
select distinct p.products_id, pd.products_name from products p, products_description pd, products_to_categories p2c, categories c where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and '11120' in (c.categories_id, c.parent_id) order by p.products_ordered desc, pd.products_name limit 10;
# Time: 040411 20:36:39
# User@Host: shoppingcart[shoppingcart] @ localhost []
# Query_time: 21  Lock_time: 0  Rows_sent: 20  Rows_examined: 17243
select distinct p.products_image,  m.manufacturers_id, p.products_id, pd.products_name, p.products_price, 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 products p left join manufacturers m using(manufacturers_id), products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id  and ((pd.products_name like '%bazzill%' or p.products_model like '%bazzill%' or m.manufacturers_name like '%bazzill%') ) order by pd.products_name limit 0, 20;
# Time: 040411 20:36:40
# User@Host: shoppingcart[shoppingcart] @ localhost []
# Query_time: 17  Lock_time: 0  Rows_sent: 20  Rows_examined: 8903
select p.products_image, pd.products_name,  p.products_id, p.manufacturers_id, p.products_price, 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 products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '29010' order by pd.products_name limit 0, 20;
# Time: 040411 20:37:31
# User@Host: shoppingcart[shoppingcart] @ localhost []
# Query_time: 17  Lock_time: 0  Rows_sent: 10  Rows_examined: 19503
select distinct p.products_id, pd.products_name from products p, products_description pd, products_to_categories p2c, categories c where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and '29000' in (c.categories_id, c.parent_id) order by p.products_ordered desc, pd.products_name limit 10;
# Time: 040411 20:56:44
# User@Host: shoppingcart[shoppingcart] @ localhost []
# Query_time: 23  Lock_time: 0  Rows_sent: 1  Rows_examined: 11493
select count(*) as total from products_to_categories where categories_id = '24200';
# Time: 040411 20:57:18
# User@Host: shoppingcart[shoppingcart] @ localhost []
# Query_time: 67  Lock_time: 0  Rows_sent: 1  Rows_examined: 8634
select count(p.products_id) as total  from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '29012';
# Time: 040411 20:57:21
# User@Host: shoppingcart[shoppingcart] @ localhost []
# Query_time: 70  Lock_time: 0  Rows_sent: 20  Rows_examined: 8980
select p.products_image, pd.products_name,  p.products_id, p.manufacturers_id, p.products_price, 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 products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '1171' order by pd.products_name  limit 20, 20;
# Time: 040411 20:57:34
# User@Host: shoppingcart[shoppingcart] @ localhost []
# Query_time: 50  Lock_time: 0  Rows_sent: 1  Rows_examined: 8374
select count(p.products_id) as total  from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '24200';
# Time: 040411 20:57:37

 

SEVENTY SECONDS!

There has to be a way to index or something, so that page loads don't take so long. Does anybody have any experience with this? I logged for a few days and got a 600 mb file of slow queries.

[Guest]

why not post a link to your site? also, what / how is your site hosted? on what host company?

[Mark Evans]

A good way to optimize the query is to change the order of the where statement.

 

Try changing

 

where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '1171' order by pd.products_name

 

to

 

where p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '1171' and p.products_status = '1' order by pd.products_name

[Rightfield]

Hi; thanks for the responses.

 

This site is www.scrapbookersparadise.com/catalog/ and www.scrapbookersparadise.com/wcatalog/ (that's retail and wholesale, respectively.)

 

We're hosted on a Verio shared server.

 

I'm not sure if the problem is that the server is too slow, or if it's that the queries are too large.

 

Sparky, thanks for the tip, I'll work on that.

[Rightfield]

I have a problem, though. I have no idea which php file is calling these queries, so I can't change it. Any ideas on how to find the spot in the code?

[user99999999]

Since you have a lot of products/categories make sure your cache is on and working. Also check your server error log for any clues. You would need to optimize a 70sec query 100% to make it acceptable.

[Rightfield]

Yeah, cache is working.

 

What do you mean by optomize it a hundred percent?