Hi,

I have a few queries that frequently drag down database peformance on our site. It's gotten so bad that it's causing the
database to lock up and stop returning queries (I check processlist and see one query "writing to tmp" and then a bunch of queries with status "Locked". Then our pages stop being served (the whole site is db driven) and the site is essentially down until we reboot mysql....not ideal.

One query in particular keeps showing up in our slow queries log and I looked at the explain plan. Looks like the query isn't using any of the indexes on the tables for some reason.

Has anyone else run into issues with db queries like this, and do you have any suggestions to alleviate it? I already worked with our server guys to try to optimize my.cnf variables, and installed the query cache mod and that's helped a bit but the top level category pages are still really heavy. Explain plan is below...maybe there's something obvious I'm missing but none of the indexes are being used at all...

Any suggestions/insight appreciated.

thanks,
Don

-----

select distinct p.products_id, p.products_image, p.products_tax_class_id, p.products_date_added, p.products_price,m.manufacturers_id ,m.manufacturers_name ,s.specials_new_products_price
from products p
left join specials s
on p.products_id = s.products_id, products_to_categories p2c, products_description pd ,categories c,
manufacturers m
where p.products_id = p2c.products_id
and p.products_id=pd.products_id
and p.manufacturers_id=m.manufacturers_id
and p2c.categories_id =7 XoR 8 and p2c.categories_id = c.categories_id
and p.products_status = 1 order by pd.products_name limit 0,9;

Code:
mysql> explain select distinct p.products_id, p.products_image, p.products_tax_class_id, p.products_date_added, p.products_price,m.manufacturers_id ,m.manufacturers_name ,s.specials_new_products_price 
    -> from products p
    -> left join specials s
    -> on p.products_id = s.products_id, products_to_categories p2c, products_description pd ,categories c,
    -> manufacturers m
    -> where p.products_id = p2c.products_id
    -> and p.products_id=pd.products_id
    -> and p.manufacturers_id=m.manufacturers_id
    -> and p2c.categories_id =7 XoR 8 and p2c.categories_id = c.categories_id
    -> and p.products_status = 1 order by pd.products_name limit 0,9; 
+----+-------------+-------+-------+---------------------+---------------------+---------+--------------------------+------+------------------------------------+
| id | select_type | table | type  | possible_keys       | key                 | key_len | ref                      | rows | Extra                              |
+----+-------------+-------+-------+---------------------+---------------------+---------+--------------------------+------+------------------------------------+
|  1 | SIMPLE      | pd    | ALL   | NULL                | NULL                | NULL    | NULL                     |  725 | Using temporary; Using filesort    | 
|  1 | SIMPLE      | m     | ALL   | NULL                | NULL                | NULL    | NULL                     |   90 |                                    | 
|  1 | SIMPLE      | p     | ALL   | NULL                | NULL                | NULL    | NULL                     |  727 |                                    | 
|  1 | SIMPLE      | s     | ref   | idx_products_id_zen | idx_products_id_zen | 4       | zc_db_prod.p.products_id |    1 |                                    | 
|  1 | SIMPLE      | c     | index | NULL                | PRIMARY             | 4       | NULL                     |  159 | Using index; Distinct              | 
|  1 | SIMPLE      | p2c   | index | NULL                | PRIMARY             | 8       | NULL                     | 2475 | Using where; Using index; Distinct | 
+----+-------------+-------+-------+---------------------+---------------------+---------+--------------------------+------+------------------------------------+
6 rows in set (0.00 sec)