Help on optimizing a query

[Confidence]

Hi guys,

 

for my new job my boss gave me a little task of optimization that i need to research, so i hope some of u have ideas on it, the case is the following:

 

We have a shop with around 100 000 products and around 1000 categories.

 

When calling the "top sold" products, all the categories and products gets searched, which totally slows down the server.

 

My task is researching all possible optimization possibilities

 

the query looks like this somehow:

 

$sql="select distinct pr.product_id, pr.product_tax_class_id, pr.product_image, pr.product_price, pr.product_price_ratio, pr.product_base_price_id, pd.product_name
from products p, " .
product_description . " pd, ".
product_to_categories . " p2c, " .
categories . " c
where pr.product_status = '1' and
cat.categories_status = '1' and
pr.product_ordered > 0 and
pr.product_id = pd.product_id and
pr.product_id = p2cat.product_id and
p2cat.categories_id = cat.categories_id
order by pr.product_ordered desc, pd.product_name limit " . MAX_SHOW_BESTSELLERS;

 

i know first that caching (APC, Memcache) would help,

also setting an index, but i dunno on what column to be honest....any more ideas?

Thanks in advance.

[fenway]

Let's see some EXPLAIN output.