best sellers box optimization

[rdva]

If u have a big catalogue at your store, u may noticed - best sellers box slow down your shop. The reason - slow SQL query inside of best_sellers.php.

Playing around with that query for two days and solution was born:

 

1) in includes/boxes/best_sellers.php find:

if (isset($current_category_id) && ($current_category_id > 0)) {
   $best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and '" . (int)$current_category_id . "' in (c.categories_id, c.parent_id) and find_in_set('".$customer_group_id."', products_hide_from_groups) = 0 and find_in_set('" . $customer_group_id . "', categories_hide_from_groups) = 0 order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);    
 } else {
  $best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c using(products_id) left join " . TABLE_CATEGORIES . " c using(categories_id) where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and find_in_set('".$customer_group_id."', products_hide_from_groups) = 0 and find_in_set('" . $customer_group_id . "', categories_hide_from_groups) = 0 order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);   
 }

replace with:

if (isset($current_category_id) && ($current_category_id > 0)) {
   $best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and (c.categories_id =" . (int)$current_category_id . " OR c.parent_id=" . (int)$current_category_id . ") and find_in_set('".$customer_group_id."', products_hide_from_groups) = 0 and find_in_set('" . $customer_group_id . "', categories_hide_from_groups) = 0 order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);   
 } else {   
  $best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = pd.products_id and p.products_status = '1' and p.products_ordered > 0 and  pd.language_id = '" . (int)$languages_id . "' and find_in_set('".$customer_group_id."', products_hide_from_groups) = 0  order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);
 }

 

2) Create indexes (if indexes do not exist) for tables (table.column):

products.products_id (PRIMARY)

products_description.products_id (PRIMARY)

categories.categories_id(PRIMARY)

categories.parent_id

products_to_categories.products_id

products_to_categories.categories_id

 

3) Perform OPTIMIZE TABLE for all tables in database.

 

 

I've got 10-20 times page loading boost for my store (1000 products, 35000 categories)

[burt]

Yo haven't started out with a default best_sellers query, so would be confusing for anyone wanting to follow the recommendation.