Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

New longest Query Removal - or Reduction


Guest

Recommended Posts

Posted

Hi all,

 

I currently have a query that is running really slowly, but only once I am at the lowest category level and the products are about to be displayed.

 

The query is

 

[83] => select p.products_image, pd.products_name, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status,

s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from

products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join specials s on p.products_id =

s.products_id, products_to_categories p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and

pd.language_id = '1' and p2c.categories_id = '812' order by pd.products_name limit 20, 20

 

I have narrowed this down to my index.php file and have removed part of the SPECIAL calling.....(I do not use specials, or manufacturers, or multiple languages, or different tax codes.....basically just an item with a cost) and was looking to strip anything that I can this is the section I now have:

 

// show the products of a specified manufacturer
   if (isset($HTTP_GET_VARS['manufacturers_id'])) {
     if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only a specific category
       $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, 

s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . 

TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " 

m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)

$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id 

. "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";
     } else {
// We show them all
       $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, 

s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . 

TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " 

m where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = 

m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";
     }
   } else {
// show the products in a given categorie
     if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only specific catgeory
       $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, 

s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . 

TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " 

m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)

$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' 

and p2c.categories_id = '" . (int)$current_category_id . "'";
     } else {
// We show them all
       $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, 

s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . 

TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . 

TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = 

p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id 

. "'";
     }

 

Any help would be great :D

Posted

go into phpmyadmin, the specials table, and add a unique index on the column products_id. then recheck your loading time ...

:-)

Monika

 

addicted to writing code ... can't get enough of databases either, LOL!

 

my toolbox: Textpad - Compare and Merge - phpMyAdmin - WS_FTP - Photoshop - How to search the forum

 

Interactive Media Award July 2007 ~ category E-Commerce

my advice on the forum is for free, PMs where you send me work are considered consultation which I charge for ...

Posted

go into phpmyadmin, the specials table, and add a unique index on the column products_id. then recheck your loading time ...

 

 

Thanks Monika,

 

That didn't have any effect though.......any other ideas??

 

Cheers,

 

Michael

Posted

sure ... divide the combined index in the p2c table into 2 separate indexes ...

:-)

Monika

 

addicted to writing code ... can't get enough of databases either, LOL!

 

my toolbox: Textpad - Compare and Merge - phpMyAdmin - WS_FTP - Photoshop - How to search the forum

 

Interactive Media Award July 2007 ~ category E-Commerce

my advice on the forum is for free, PMs where you send me work are considered consultation which I charge for ...

Posted

sure ... divide the combined index in the p2c table into 2 separate indexes ...

 

WOW!!!

 

Thanks so much!!! So......that works like steroids for a database!!

 

So that I don't appear too silly - What does splitting the indexes do?

 

Cheers,

 

Michael

Posted

Most queries do not query for both parts of this combined index, and in those case the query runs much faster if the compound index is split up. Normally, obviously, compound indexes are better as they reduce index tree length. This was part of an extensive database tuning experience I made a few years ago when working on a larger osC store and tried out a lot of things to make it faster.

:-)

Monika

 

addicted to writing code ... can't get enough of databases either, LOL!

 

my toolbox: Textpad - Compare and Merge - phpMyAdmin - WS_FTP - Photoshop - How to search the forum

 

Interactive Media Award July 2007 ~ category E-Commerce

my advice on the forum is for free, PMs where you send me work are considered consultation which I charge for ...

Posted

Most queries do not query for both parts of this combined index, and in those case the query runs much faster if the compound index is split up. Normally, obviously, compound indexes are better as they reduce index tree length. This was part of an extensive database tuning experience I made a few years ago when working on a larger osC store and tried out a lot of things to make it faster.

 

Thanks for all the help Monika!!!

 

Any chance that you wrote up a forum thread for that database tuning experience??

Oh, and when my store makes its first million I'll send you a cut ;)

 

Cheers,

 

Michael

Posted

:-) ... cute idea, thx in advance ...

 

no thread done, as it was a work project which I was obviously not allowed to share ...

:-)

Monika

 

addicted to writing code ... can't get enough of databases either, LOL!

 

my toolbox: Textpad - Compare and Merge - phpMyAdmin - WS_FTP - Photoshop - How to search the forum

 

Interactive Media Award July 2007 ~ category E-Commerce

my advice on the forum is for free, PMs where you send me work are considered consultation which I charge for ...

Archived

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

×
×
  • Create New...