Leaving things the way they are for a bit :) (the index was put on the zen_orders_products table) now we have the current_bestsellers query running slow...
Information:
slow query log entry
Query_time: 11 Lock_time: 0 Rows_sent: 10 Rows_examined: 3381564
select distinct op.products_id, pd.products_name, sum(op.products_quantity) as total
from zen_orders_products op, zen_orders o, zen_products p, zen_products_description pd, zen_products_to_categories p2c, zen_categories c
where p.products_id = op.products_id
and pd.products_id = op.products_id
and o.orders_id = op.orders_id
and op.products_id = p2c.products_id
and p2c.categories_id = c.categories_id
and p.products_status = '1'
and p.products_price > '0'
and pd.language_id = '1'
and '63' in (c.categories_id, c.parent_id)
and TO_DAYS(NOW()) - TO_DAYS(o.date_purchased) <= 30
group by op.products_id
order by total desc, pd.products_name
limit 10;
ran in phpmyadmin - Showing rows 0 - 9 (10 total, Query took 11.1249 sec) (weird that the slow log shows over 3 million rows to examine, where phpmyadmin says 6406)
id 1 select_type SIMPLE table pd type ALL possible_keys PRIMARY key NULL key_len NULL ref NULL rows 6406 ExtraUsing where; Using temporary; Using filesort
id 1 select_type SIMPLE table p type eq_ref possible_keys PRIMARY,idx_products_status_zen key PRIMARY key_len 4 ref *****.pd.products_id rows 1 ExtraUsing where
id 1 select_type SIMPLE table op type ref possible_keys idx_orders_id_prod_id_zen,opa.orders_id,products_i... key products_id key_len 4 ref *****.p.products_id rows 56 ExtraUsing where
id 1 select_type SIMPLE table p2c type ref possible_keys PRIMARY,idx_cat_prod_id_zen key PRIMARY key_len 4 ref *****pd.products_id rows 3 ExtraUsing index
id 1 select_type SIMPLE table o type eq_ref possible_keys PRIMARY key PRIMARY key_len 4 ref *****.op.orders_id rows 1 ExtraUsing where
id 1 select_type SIMPLE table c type eq_ref possible_keys PRIMARY key PRIMARY key_len 4 ref *****.p2c.categories_id rows 1 ExtraUsing where
