db tuning - some queries not using defined indexes?

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;

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)

You have not provided the server base system????

No matter, as tuning my.cnf is an item that hosts spend a huge amount of time on and are reluctant to share

Do you mean the server/install specs?

MySQL 5.0.90-community
PHP Version 5.2.9
Zen Cart 1.3.8
Dedicated machine with a lot of RAM

And all of the tables in the query have indexes on the primary keys and the columns in the where clauses, yet they aren't used. Maybe it's because of the LEFT JOIN, the distinct or some other parts of the query but I'm not sure. I tried googling around and didn't find a clear answer.

-d

kobra:

You have not provided the server base system????

No matter, as tuning my.cnf is an item that hosts spend a huge amount of time on and are reluctant to share

Something about that query is just wrong.
Where did you get it from? As in ... I can't find that query within Zen Cart anywhere. What addon is using that?