Very slow site when adding even just 1 special

[josh1r]

About a week ago, in the midst of adding several contributions, my jewelry site suddenly crawled to a near-halt if I had any specials active. I cannot figure out what I did to cause this problem. My product pages are fine, as are information pages, but the main index page and category pages take about 10 seconds to load if I have even just 1 special.

 

I've installed and run the queries debug contribution so hopefully someone can help me out.

 

Here's the script without specials and with specials.

 

I can see that step [18] is taking 9 seconds when i add specials but i'm not sure why or how to fix it :(

 

Thanks for you help!

[10seconds]

About a week ago, in the midst of adding several contributions, my jewelry site suddenly crawled to a near-halt if I had any specials active. I cannot figure out what I did to cause this problem. My product pages are fine, as are information pages, but the main index page and category pages take about 10 seconds to load if I have even just 1 special.

 

I've installed and run the queries debug contribution so hopefully someone can help me out.

 

Here's the script without specials and with specials.

 

I can see that step [18] is taking 9 seconds when i add specials but i'm not sure why or how to fix it :(

 

Thanks for you help!

 

Josh , i have the same problem...but i had 50,000 products and it took 5 minutes to load index.php...i timmed it to 10,000 products and it still takes 2 minutes...this all started when i installed the "specials by category contrib.[which i have since removed]. Did you ever work out what was slowing up the site?

 

Cheers

Danny

[Jan Zonjee]

Josh , i have the same problem...but i had 50,000 products and it took 5 minutes to load index.php...i timmed it to 10,000 products and it still takes 2 minutes...this all started when i installed the "specials by category contrib.[which i have since removed]. Did you ever work out what was slowing up the site?

Apparently MySQL becomes very inefficient in this query. Looks like such a simple query. The slow query seems to be the one from includes/modules/new_products.php. Perhaps a way around it is to use two queries. The second one picking up the specials prices (haven't tested this fully):

<?php
/*
 $Id: new_products.php,v 1.34 2003/06/09 22:49:58 hpdl Exp $

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2003 osCommerce

 Released under the GNU General Public License
*/
?>
<!-- new_products //-->
<?php
 $info_box_contents = array();
 $info_box_contents[] = array('text' => sprintf(TABLE_HEADING_NEW_PRODUCTS, strftime('%B')));

 new contentBoxHeading($info_box_contents);

 if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
// get rid of the join with specials
$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, p.products_price as products_price from " . TABLE_PRODUCTS . " p where products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS); 
 } else {
$new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, p.products_price as products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' order by p.products_date_added desc limit ". MAX_DISPLAY_NEW_PRODUCTS);
 }

 if (($no_of_new_products = tep_db_num_rows($new_products_query)) > 0) {
  while ($_new_products = tep_db_fetch_array($new_products_query)) {
$new_products[] = $_new_products;
$list_of_prdct_ids[] = $_new_products['products_id'];
} 

$select_list_of_prdct_ids = "products_id = '".$list_of_prdct_ids[0]."' ";
 if ($no_of_new_products > 1) {
  for ($n = 1; $n < count($list_of_prdct_ids); $n++) {
  $select_list_of_prdct_ids .= "or products_id = '".$list_of_prdct_ids[$n]."' ";   
  }
}

// an extra query is now used for all the specials
$specials_query = tep_db_query("select products_id, specials_new_products_price from specials where (".$select_list_of_prdct_ids.") and status = '1' ");
while ($specials_array = tep_db_fetch_array($specials_query)) {
$new_s_prices[] = array ('products_id' => $specials_array['products_id'], 'specials_new_products_price' => $specials_array['specials_new_products_price']);
}

// replace products_price with the specials_new_products_price
if(!empty($new_s_prices)) {
for ($x = 0; $x < $no_of_new_products; $x++) { 
	for ($i = 0; $i < count($new_s_prices); $i++) {
		if( $new_products[$x]['products_id'] == $new_s_prices[$i]['products_id'] ) {
		$new_products[$x]['products_price'] = $new_s_prices[$i]['specials_new_products_price'];
		}
	   }
   } 
} // // end if(!empty($new_s_prices)

 $row = 0;
 $col = 0;

 $info_box_contents = array();

//  while ($new_products = tep_db_fetch_array($new_products_query)) {
for ($x = 0; $x < $no_of_new_products; $x++) {
$new_products[$x]['products_name'] = tep_get_products_name($new_products[$x]['products_id']);

$info_box_contents[$row][$col] = array('align' => 'center',
									   'params' => 'class="smallText" width="33%" valign="top"',
									   'text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $new_products[$x]['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $new_products[$x]['products_image'], $new_products[$x]['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a><br><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $new_products[$x]['products_id']) . '">' . $new_products[$x]['products_name'] . '</a><br>' . $currencies->display_price($new_products[$x]['products_price'], tep_get_tax_rate($new_products[$x]['products_tax_class_id'])));

$col ++;
if ($col > 2) {
  $col = 0;
  $row ++;
}
  } // end for ($x = 0; $x < $no_of_new_products; $x++)
} //  end if (($no_of_new_products = tep_db_num_rows($new_products_query)) > 0)
new contentBox($info_box_contents);
?>
<!-- new_products_eof //-->

[10seconds]

Apparently MySQL becomes very inefficient in this query. Looks like such a simple query. The slow query seems to be the one from includes/modules/new_products.php. Perhaps a way around it is to use two queries. The second one picking up the specials prices (haven't tested this fully):

<?php
/*
 $Id: new_products.php,v 1.34 2003/06/09 22:49:58 hpdl Exp $

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2003 osCommerce

 Released under the GNU General Public License
*/
?>
<!-- new_products //-->
<?php
 $info_box_contents = array();
 $info_box_contents[] = array('text' => sprintf(TABLE_HEADING_NEW_PRODUCTS, strftime('%B')));

 new contentBoxHeading($info_box_contents);

 if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
// get rid of the join with specials
$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, p.products_price as products_price from " . TABLE_PRODUCTS . " p where products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS); 
 } else {
$new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, p.products_price as products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' order by p.products_date_added desc limit ". MAX_DISPLAY_NEW_PRODUCTS);
 }

 if (($no_of_new_products = tep_db_num_rows($new_products_query)) > 0) {
  while ($_new_products = tep_db_fetch_array($new_products_query)) {
$new_products[] = $_new_products;
$list_of_prdct_ids[] = $_new_products['products_id'];
} 

$select_list_of_prdct_ids = "products_id = '".$list_of_prdct_ids[0]."' ";
 if ($no_of_new_products > 1) {
  for ($n = 1; $n < count($list_of_prdct_ids); $n++) {
  $select_list_of_prdct_ids .= "or products_id = '".$list_of_prdct_ids[$n]."' ";   
  }
}

// an extra query is now used for all the specials
$specials_query = tep_db_query("select products_id, specials_new_products_price from specials where (".$select_list_of_prdct_ids.") and status = '1' ");
while ($specials_array = tep_db_fetch_array($specials_query)) {
$new_s_prices[] = array ('products_id' => $specials_array['products_id'], 'specials_new_products_price' => $specials_array['specials_new_products_price']);
}

// replace products_price with the specials_new_products_price
if(!empty($new_s_prices)) {
for ($x = 0; $x < $no_of_new_products; $x++) { 
	for ($i = 0; $i < count($new_s_prices); $i++) {
		if( $new_products[$x]['products_id'] == $new_s_prices[$i]['products_id'] ) {
		$new_products[$x]['products_price'] = $new_s_prices[$i]['specials_new_products_price'];
		}
	   }
   } 
} // // end if(!empty($new_s_prices)

 $row = 0;
 $col = 0;

 $info_box_contents = array();

//  while ($new_products = tep_db_fetch_array($new_products_query)) {
for ($x = 0; $x < $no_of_new_products; $x++) {
$new_products[$x]['products_name'] = tep_get_products_name($new_products[$x]['products_id']);

$info_box_contents[$row][$col] = array('align' => 'center',
									   'params' => 'class="smallText" width="33%" valign="top"',
									   'text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $new_products[$x]['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $new_products[$x]['products_image'], $new_products[$x]['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a><br><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $new_products[$x]['products_id']) . '">' . $new_products[$x]['products_name'] . '</a><br>' . $currencies->display_price($new_products[$x]['products_price'], tep_get_tax_rate($new_products[$x]['products_tax_class_id'])));

$col ++;
if ($col > 2) {
  $col = 0;
  $row ++;
}
  } // end for ($x = 0; $x < $no_of_new_products; $x++)
} //  end if (($no_of_new_products = tep_db_num_rows($new_products_query)) > 0)
new contentBox($info_box_contents);
?>
<!-- new_products_eof //-->

 

Thanks for the code Jan...

I'm not sure if products_new.php is a problem though...my new products modules load pretty quickly still.

It's only index.php that is really slow.

 

Cheers

Danny

[boxtel]

Thanks for the code Jan...

I'm not sure if products_new.php is a problem though...my new products modules load pretty quickly still.

It's only index.php that is really slow.

 

Cheers

Danny

 

adding indexes to products_id and status doesn't help ?