Jump to content

Archived

This topic is now archived and is closed to further replies.

rdva

best sellers box optimization

Recommended Posts

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)

Share this post


Link to post
Share on other sites

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


This is a signature that appears on all my posts.  
IF YOU MAKE A POST REQUESTING HELP...please state the exact version
of osCommerce that you are using. THANKS

 
Get the latest Responsive osCommerce CE (community edition) here

Share this post


Link to post
Share on other sites

×