I added this contrib and others and things are rocking now on my site...it was much needed.
Just one thing, I notice when go from my shopping cart to my shipping calculator or recently viewed all the cart contents get lost thus making the shopper redo their order.
Any ideas if it's something from this contrib?
Thanks,
FK
Latest News: (loading..)
0
897 replies to this topic
#682
Posted 09 October 2008, 01:15
Anyone is able to optimize reviews.php when review box displayed on left column? I spotted an average of 2-5 seconds when having multiple reviews box displayed within reviews.php. I am having my code posted below, any suggestion to improve its performance will be greatly appreciated!
<?php
/*
$Id: reviews.php,v 1.37 2003/06/09 22:20:28 hpdl Exp $
osCommerce, Open Source E-Commerce Solutions
http://www.oscommerce.com
Copyright (c) 2003 osCommerce
Released under the GNU General Public License
*/
?>
<!-- reviews //-->
<tr>
<td>
<?php
$info_box_contents = array();
$info_box_contents[] = array('text' => BOX_HEADING_REVIEWS);
new infoBoxHeading($info_box_contents, false, false, tep_href_link(FILENAME_REVIEWS));
$random_select = "select r.reviews_id, r.reviews_rating, p.products_id, p.products_image, pd.products_name from " . TABLE_REVIEWS . " r, " . TABLE_REVIEWS_DESCRIPTION . " rd, " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = r.products_id and r.reviews_id = rd.reviews_id and rd.languages_id = '" . (int)$languages_id . "' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'";
if (isset($HTTP_GET_VARS['products_id'])) {
$random_select .= " and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "'";
}
$random_select .= " order by r.reviews_id desc limit " . MAX_RANDOM_SELECT_REVIEWS;
$random_product = tep_random_select($random_select);
$info_box_contents = array();
if ($random_product) {
// display random review box
$rand_review_query = tep_db_query("select substring(reviews_text, 1, 60) as reviews_text from " . TABLE_REVIEWS_DESCRIPTION . " where reviews_id = '" . (int)$random_product['reviews_id'] . "' and languages_id = '" . (int)$languages_id . "'");
$rand_review = tep_db_fetch_array($rand_review_query);
$rand_review_text = tep_break_string(tep_output_string_protected($rand_review['reviews_text']), 15, '-<br>');
$info_box_contents[] = array('text' => '<div align="center"><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $random_product['products_id'] . '&reviews_id=' . $random_product['reviews_id']) . '">' . tep_image(DIR_WS_IMAGES . $random_product['products_image'], $random_product['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a></div><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $random_product['products_id'] . '&reviews_id=' . $random_product['reviews_id']) . '">' . $rand_review_text . ' ..</a><br><div align="center">' . tep_image(DIR_WS_IMAGES . 'stars_' . $random_product['reviews_rating'] . '.gif' , sprintf(BOX_REVIEWS_TEXT_OF_5_STARS, $random_product['reviews_rating'])) . '</div>');
} elseif (isset($HTTP_GET_VARS['products_id'])) {
// display 'write a review' box
$info_box_contents[] = array('text' => '<table border="0" cellspacing="0" cellpadding="2"><tr><td class="infoBoxContents"><a href="' . tep_href_link(FILENAME_PRODUCT_REVIEWS_WRITE, 'products_id=' . $HTTP_GET_VARS['products_id']) . '">' . tep_image(DIR_WS_IMAGES . 'box_write_review.gif', IMAGE_BUTTON_WRITE_REVIEW) . '</a></td><td class="infoBoxContents"><a href="' . tep_href_link(FILENAME_PRODUCT_REVIEWS_WRITE, 'products_id=' . $HTTP_GET_VARS['products_id']) . '">' . BOX_REVIEWS_WRITE_REVIEW .'</a></td></tr></table>');
} else {
// display 'no reviews' box
$info_box_contents[] = array('text' => BOX_REVIEWS_NO_REVIEWS);
}
new infoBox($info_box_contents);
?>
<!-- 2nd reviews -->
<?php
$random_select = "select r.reviews_id, r.reviews_rating, p.products_id, p.products_image, pd.products_name from " . TABLE_REVIEWS . " r, " . TABLE_REVIEWS_DESCRIPTION . " rd, " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = r.products_id and r.reviews_id = rd.reviews_id and rd.languages_id = '" . (int)$languages_id . "' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'";
if (isset($HTTP_GET_VARS['products_id'])) {
$random_select .= " and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "'";
}
$random_select .= " order by r.reviews_id desc limit " . MAX_RANDOM_SELECT_REVIEWS;
$random_product = tep_random_select($random_select);
$info_box_contents = array();
if ($random_product) {
// display random review box
$rand_review_query = tep_db_query("select substring(reviews_text, 1, 60) as reviews_text from " . TABLE_REVIEWS_DESCRIPTION . " where reviews_id = '" . (int)$random_product['reviews_id'] . "' and languages_id = '" . (int)$languages_id . "'");
$rand_review = tep_db_fetch_array($rand_review_query);
$rand_review_text = tep_break_string(tep_output_string_protected($rand_review['reviews_text']), 15, '-<br>');
$info_box_contents[] = array('text' => '<div align="center"><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $random_product['products_id'] . '&reviews_id=' . $random_product['reviews_id']) . '">' . tep_image(DIR_WS_IMAGES . $random_product['products_image'], $random_product['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a></div><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $random_product['products_id'] . '&reviews_id=' . $random_product['reviews_id']) . '">' . $rand_review_text . ' ..</a><br><div align="center">' . tep_image(DIR_WS_IMAGES . 'stars_' . $random_product['reviews_rating'] . '.gif' , sprintf(BOX_REVIEWS_TEXT_OF_5_STARS, $random_product['reviews_rating'])) . '</div>');
} elseif (isset($HTTP_GET_VARS['products_id'])) {
// display 'write a review' box
// $info_box_contents[] = array('text' => '<table border="0" cellspacing="0" cellpadding="2"><tr><td class="infoBoxContents"><a href="' . tep_href_link(FILENAME_PRODUCT_REVIEWS_WRITE, 'products_id=' . $HTTP_GET_VARS['products_id']) . '">' . tep_image(DIR_WS_IMAGES . 'box_write_review.gif', IMAGE_BUTTON_WRITE_REVIEW) . '</a></td><td class="infoBoxContents"><a href="' . tep_href_link(FILENAME_PRODUCT_REVIEWS_WRITE, 'products_id=' . $HTTP_GET_VARS['products_id']) . '">' . BOX_REVIEWS_WRITE_REVIEW .'</a></td></tr></table>');
// } else {
// display 'no reviews' box
// $info_box_contents[] = array('text' => BOX_REVIEWS_NO_REVIEWS);
}
new infoBox($info_box_contents);
?>
<!-- 2nd reviews END-->
<!-- 3rd reviews -->
<?php
$random_select = "select r.reviews_id, r.reviews_rating, p.products_id, p.products_image, pd.products_name from " . TABLE_REVIEWS . " r, " . TABLE_REVIEWS_DESCRIPTION . " rd, " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = r.products_id and r.reviews_id = rd.reviews_id and rd.languages_id = '" . (int)$languages_id . "' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'";
if (isset($HTTP_GET_VARS['products_id'])) {
$random_select .= " and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "'";
}
$random_select .= " order by r.reviews_id desc limit " . MAX_RANDOM_SELECT_REVIEWS;
$random_product = tep_random_select($random_select);
$info_box_contents = array();
if ($random_product) {
// display random review box
$rand_review_query = tep_db_query("select substring(reviews_text, 1, 60) as reviews_text from " . TABLE_REVIEWS_DESCRIPTION . " where reviews_id = '" . (int)$random_product['reviews_id'] . "' and languages_id = '" . (int)$languages_id . "'");
$rand_review = tep_db_fetch_array($rand_review_query);
$rand_review_text = tep_break_string(tep_output_string_protected($rand_review['reviews_text']), 15, '-<br>');
$info_box_contents[] = array('text' => '<div align="center"><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $random_product['products_id'] . '&reviews_id=' . $random_product['reviews_id']) . '">' . tep_image(DIR_WS_IMAGES . $random_product['products_image'], $random_product['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a></div><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $random_product['products_id'] . '&reviews_id=' . $random_product['reviews_id']) . '">' . $rand_review_text . ' ..</a><br><div align="center">' . tep_image(DIR_WS_IMAGES . 'stars_' . $random_product['reviews_rating'] . '.gif' , sprintf(BOX_REVIEWS_TEXT_OF_5_STARS, $random_product['reviews_rating'])) . '</div>');
} elseif (isset($HTTP_GET_VARS['products_id'])) {
// display 'write a review' box
// $info_box_contents[] = array('text' => '<table border="0" cellspacing="0" cellpadding="2"><tr><td class="infoBoxContents"><a href="' . tep_href_link(FILENAME_PRODUCT_REVIEWS_WRITE, 'products_id=' . $HTTP_GET_VARS['products_id']) . '">' . tep_image(DIR_WS_IMAGES . 'box_write_review.gif', IMAGE_BUTTON_WRITE_REVIEW) . '</a></td><td class="infoBoxContents"><a href="' . tep_href_link(FILENAME_PRODUCT_REVIEWS_WRITE, 'products_id=' . $HTTP_GET_VARS['products_id']) . '">' . BOX_REVIEWS_WRITE_REVIEW .'</a></td></tr></table>');
// } else {
// display 'no reviews' box
// $info_box_contents[] = array('text' => BOX_REVIEWS_NO_REVIEWS);
}
new infoBox($info_box_contents);
?>
<!-- 3rd reviews END-->
<!-- 4th reviews -->
<?php
$random_select = "select r.reviews_id, r.reviews_rating, p.products_id, p.products_image, pd.products_name from " . TABLE_REVIEWS . " r, " . TABLE_REVIEWS_DESCRIPTION . " rd, " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = r.products_id and r.reviews_id = rd.reviews_id and rd.languages_id = '" . (int)$languages_id . "' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'";
if (isset($HTTP_GET_VARS['products_id'])) {
$random_select .= " and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "'";
}
$random_select .= " order by r.reviews_id desc limit " . MAX_RANDOM_SELECT_REVIEWS;
$random_product = tep_random_select($random_select);
$info_box_contents = array();
if ($random_product) {
// display random review box
$rand_review_query = tep_db_query("select substring(reviews_text, 1, 60) as reviews_text from " . TABLE_REVIEWS_DESCRIPTION . " where reviews_id = '" . (int)$random_product['reviews_id'] . "' and languages_id = '" . (int)$languages_id . "'");
$rand_review = tep_db_fetch_array($rand_review_query);
$rand_review_text = tep_break_string(tep_output_string_protected($rand_review['reviews_text']), 15, '-<br>');
$info_box_contents[] = array('text' => '<div align="center"><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $random_product['products_id'] . '&reviews_id=' . $random_product['reviews_id']) . '">' . tep_image(DIR_WS_IMAGES . $random_product['products_image'], $random_product['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a></div><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $random_product['products_id'] . '&reviews_id=' . $random_product['reviews_id']) . '">' . $rand_review_text . ' ..</a><br><div align="center">' . tep_image(DIR_WS_IMAGES . 'stars_' . $random_product['reviews_rating'] . '.gif' , sprintf(BOX_REVIEWS_TEXT_OF_5_STARS, $random_product['reviews_rating'])) . '</div>');
} elseif (isset($HTTP_GET_VARS['products_id'])) {
// display 'write a review' box
// $info_box_contents[] = array('text' => '<table border="0" cellspacing="0" cellpadding="2"><tr><td class="infoBoxContents"><a href="' . tep_href_link(FILENAME_PRODUCT_REVIEWS_WRITE, 'products_id=' . $HTTP_GET_VARS['products_id']) . '">' . tep_image(DIR_WS_IMAGES . 'box_write_review.gif', IMAGE_BUTTON_WRITE_REVIEW) . '</a></td><td class="infoBoxContents"><a href="' . tep_href_link(FILENAME_PRODUCT_REVIEWS_WRITE, 'products_id=' . $HTTP_GET_VARS['products_id']) . '">' . BOX_REVIEWS_WRITE_REVIEW .'</a></td></tr></table>');
// } else {
// display 'no reviews' box
// $info_box_contents[] = array('text' => BOX_REVIEWS_NO_REVIEWS);
}
new infoBox($info_box_contents);
?>
<!-- 4th reviews END-->
</td>
</tr>
<!-- reviews_eof //-->
Thanks again in advance!
A.P.
#683
Posted 28 October 2008, 21:30
Hi, I'm learning so much from this thread... thank you all so much. Most of my heavily modified site is running much faster now. My worst page is still my product_info.php
Current Parse Time: 14.888 s with 86 queries
I've installed the queries debug. These two queries seems to be the culprit, but I'm not sure I understand enough to figure out what to do:
[69] =>
SELECT pov.products_options_values_id, pov.products_options_values_name,
pa.options_values_price, pa.price_prefix , pase.sort_order
FROM products_attributes pa, products_attributes_sets_to_products pas2pa, products_attributes_sets pas, products_attributes_sets_elements pase, products_options_values pov
WHERE pa.products_id = '377'
AND pa.options_id = '1'
AND pas2pa.products_id = pa.products_id
AND pas.products_attributes_sets_id = pas2pa.products_attributes_sets_id
AND pas.products_options_id = pa.options_id
AND pase.products_attributes_sets_id = pas.products_attributes_sets_id
AND pase.options_values_id = pa.options_values_id
AND pov.products_options_values_id = pa.options_values_id
AND pov.language_id = '1'
ORDER BY pase.sort_order, pa.options_values_id
[70] =>
SELECT pov.products_options_values_id, pov.products_options_values_name,
pa.options_values_price, pa.price_prefix , pase.sort_order
FROM products_attributes pa, products_attributes_sets_to_products pas2pa, products_attributes_sets pas, products_attributes_sets_elements pase, products_options_values pov
WHERE pa.products_id = '377'
AND pa.options_id = '2'
AND pas2pa.products_id = pa.products_id
AND pas.products_attributes_sets_id = pas2pa.products_attributes_sets_id
AND pas.products_options_id = pa.options_id
AND pase.products_attributes_sets_id = pas.products_attributes_sets_id
AND pase.options_values_id = pa.options_values_id
AND pov.products_options_values_id = pa.options_values_id
AND pov.language_id = '1'
ORDER BY pase.sort_order, pa.options_values_id
[69] => 3.187650
[70] => 10.220102
If anybody has suggestions, I'd appreciate it.
Janet
Current Parse Time: 14.888 s with 86 queries
I've installed the queries debug. These two queries seems to be the culprit, but I'm not sure I understand enough to figure out what to do:
[69] =>
SELECT pov.products_options_values_id, pov.products_options_values_name,
pa.options_values_price, pa.price_prefix , pase.sort_order
FROM products_attributes pa, products_attributes_sets_to_products pas2pa, products_attributes_sets pas, products_attributes_sets_elements pase, products_options_values pov
WHERE pa.products_id = '377'
AND pa.options_id = '1'
AND pas2pa.products_id = pa.products_id
AND pas.products_attributes_sets_id = pas2pa.products_attributes_sets_id
AND pas.products_options_id = pa.options_id
AND pase.products_attributes_sets_id = pas.products_attributes_sets_id
AND pase.options_values_id = pa.options_values_id
AND pov.products_options_values_id = pa.options_values_id
AND pov.language_id = '1'
ORDER BY pase.sort_order, pa.options_values_id
[70] =>
SELECT pov.products_options_values_id, pov.products_options_values_name,
pa.options_values_price, pa.price_prefix , pase.sort_order
FROM products_attributes pa, products_attributes_sets_to_products pas2pa, products_attributes_sets pas, products_attributes_sets_elements pase, products_options_values pov
WHERE pa.products_id = '377'
AND pa.options_id = '2'
AND pas2pa.products_id = pa.products_id
AND pas.products_attributes_sets_id = pas2pa.products_attributes_sets_id
AND pas.products_options_id = pa.options_id
AND pase.products_attributes_sets_id = pas.products_attributes_sets_id
AND pase.options_values_id = pa.options_values_id
AND pov.products_options_values_id = pa.options_values_id
AND pov.language_id = '1'
ORDER BY pase.sort_order, pa.options_values_id
[69] => 3.187650
[70] => 10.220102
If anybody has suggestions, I'd appreciate it.
Janet
#684
Posted 28 October 2008, 22:00
janetgot, on Oct 28 2008, 10:30 PM, said:
I've installed the queries debug. These two queries seems to be the culprit, but I'm not sure I understand enough to figure out what to do:
[69] =>
SELECT pov.products_options_values_id, pov.products_options_values_name,
pa.options_values_price, pa.price_prefix , pase.sort_order
FROM products_attributes pa, products_attributes_sets_to_products pas2pa, products_attributes_sets pas, products_attributes_sets_elements pase, products_options_values pov
WHERE pa.products_id = '377'
AND pa.options_id = '1'
AND pas2pa.products_id = pa.products_id
AND pas.products_attributes_sets_id = pas2pa.products_attributes_sets_id
AND pas.products_options_id = pa.options_id
AND pase.products_attributes_sets_id = pas.products_attributes_sets_id
AND pase.options_values_id = pa.options_values_id
AND pov.products_options_values_id = pa.options_values_id
AND pov.language_id = '1'
ORDER BY pase.sort_order, pa.options_values_id
[69] =>
SELECT pov.products_options_values_id, pov.products_options_values_name,
pa.options_values_price, pa.price_prefix , pase.sort_order
FROM products_attributes pa, products_attributes_sets_to_products pas2pa, products_attributes_sets pas, products_attributes_sets_elements pase, products_options_values pov
WHERE pa.products_id = '377'
AND pa.options_id = '1'
AND pas2pa.products_id = pa.products_id
AND pas.products_attributes_sets_id = pas2pa.products_attributes_sets_id
AND pas.products_options_id = pa.options_id
AND pase.products_attributes_sets_id = pas.products_attributes_sets_id
AND pase.options_values_id = pa.options_values_id
AND pov.products_options_values_id = pa.options_values_id
AND pov.language_id = '1'
ORDER BY pase.sort_order, pa.options_values_id
When using "EXPLAIN" before the SELECT MySQL will output some debug information and if it isn't using "index" but something like "where" you probably should add indexes on certain tables. But first things first.
#685
Posted 29 October 2008, 14:28
Jan Zonjee, on Oct 28 2008, 06:00 PM, said:
There was a post today where someone was suggested to copy and paste that in phpMyAdmin (the sql box) and add EXPLAIN before it (so EXPLAIN SELECT pov.products_options_values_id, pov.products_options_values_name, etcetera). He found out that he had to add an index somewhere to speed a query up tremendously.
When using "EXPLAIN" before the SELECT MySQL will output some debug information and if it isn't using "index" but something like "where" you probably should add indexes on certain tables. But first things first.
When using "EXPLAIN" before the SELECT MySQL will output some debug information and if it isn't using "index" but something like "where" you probably should add indexes on certain tables. But first things first.
Jan, thank you so much... THIS WAS IT! I found the posting you referred http://forums.oscommerce.com/index.php?s=&...t&p=1326879 and ran the EXPLAIN query. Here is what I got:
Quote
SQL result
Generation Time: Oct 29, 2008 at 07:04 AM
Generated by: phpMyAdmin 2.6.1 / MySQL 4.1.20-log
SQL-query: EXPLAIN SELECT pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix , pase.sort_order FROM products_attributes pa, products_attributes_sets_to_products pas2pa, products_attributes_sets pas, products_attributes_sets_elements pase, products_options_values pov WHERE pa.products_id = '377' AND pa.options_id = '1' AND pas2pa.products_id = pa.products_id AND pas.products_attributes_sets_id = pas2pa.products_attributes_sets_id AND pas.products_options_id = pa.options_id AND pase.products_attributes_sets_id = pas.products_attributes_sets_id AND pase.options_values_id = pa.options_values_id AND pov.products_options_values_id = pa.options_values_id AND pov.language_id = '1' ORDER BY pase.sort_order, pa.options_values_id;
Rows: 5
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE pas2pa ALL NULL NULL NULL NULL 1150 Using temporary; Using filesort
1 SIMPLE pase ALL NULL NULL NULL NULL 1794
1 SIMPLE pa ALL NULL NULL NULL NULL 5584 Using where
1 SIMPLE pas eq_ref PRIMARY PRIMARY 4 hellynewengland.pas2pa.products_attributes_sets_id 1 Using where
1 SIMPLE pov eq_ref PRIMARY PRIMARY 8 hellynewengland.pa.options_values_id,const 1
Generation Time: Oct 29, 2008 at 07:04 AM
Generated by: phpMyAdmin 2.6.1 / MySQL 4.1.20-log
SQL-query: EXPLAIN SELECT pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix , pase.sort_order FROM products_attributes pa, products_attributes_sets_to_products pas2pa, products_attributes_sets pas, products_attributes_sets_elements pase, products_options_values pov WHERE pa.products_id = '377' AND pa.options_id = '1' AND pas2pa.products_id = pa.products_id AND pas.products_attributes_sets_id = pas2pa.products_attributes_sets_id AND pas.products_options_id = pa.options_id AND pase.products_attributes_sets_id = pas.products_attributes_sets_id AND pase.options_values_id = pa.options_values_id AND pov.products_options_values_id = pa.options_values_id AND pov.language_id = '1' ORDER BY pase.sort_order, pa.options_values_id;
Rows: 5
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE pas2pa ALL NULL NULL NULL NULL 1150 Using temporary; Using filesort
1 SIMPLE pase ALL NULL NULL NULL NULL 1794
1 SIMPLE pa ALL NULL NULL NULL NULL 5584 Using where
1 SIMPLE pas eq_ref PRIMARY PRIMARY 4 hellynewengland.pas2pa.products_attributes_sets_id 1 Using where
1 SIMPLE pov eq_ref PRIMARY PRIMARY 8 hellynewengland.pa.options_values_id,const 1
and
Quote
Generation Time: Oct 29, 2008 at 07:07 AM
Generated by: phpMyAdmin 2.6.1 / MySQL 4.1.20-log
SQL-query: EXPLAIN SELECT pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix , pase.sort_order FROM products_attributes pa, products_attributes_sets_to_products pas2pa, products_attributes_sets pas, products_attributes_sets_elements pase, products_options_values pov WHERE pa.products_id = '377' AND pa.options_id = '2' AND pas2pa.products_id = pa.products_id AND pas.products_attributes_sets_id = pas2pa.products_attributes_sets_id AND pas.products_options_id = pa.options_id AND pase.products_attributes_sets_id = pas.products_attributes_sets_id AND pase.options_values_id = pa.options_values_id AND pov.products_options_values_id = pa.options_values_id AND pov.language_id = '1' ORDER BY pase.sort_order, pa.options_values_id;
Rows: 5
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE pas2pa ALL NULL NULL NULL NULL 1150 Using temporary; Using filesort
1 SIMPLE pase ALL NULL NULL NULL NULL 1794
1 SIMPLE pa ALL NULL NULL NULL NULL 5584 Using where
1 SIMPLE pas eq_ref PRIMARY PRIMARY 4 hellynewengland.pas2pa.products_attributes_sets_id 1 Using where
1 SIMPLE pov eq_ref PRIMARY PRIMARY 8 hellynewengland.pa.options_values_id,const 1
Generated by: phpMyAdmin 2.6.1 / MySQL 4.1.20-log
SQL-query: EXPLAIN SELECT pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix , pase.sort_order FROM products_attributes pa, products_attributes_sets_to_products pas2pa, products_attributes_sets pas, products_attributes_sets_elements pase, products_options_values pov WHERE pa.products_id = '377' AND pa.options_id = '2' AND pas2pa.products_id = pa.products_id AND pas.products_attributes_sets_id = pas2pa.products_attributes_sets_id AND pas.products_options_id = pa.options_id AND pase.products_attributes_sets_id = pas.products_attributes_sets_id AND pase.options_values_id = pa.options_values_id AND pov.products_options_values_id = pa.options_values_id AND pov.language_id = '1' ORDER BY pase.sort_order, pa.options_values_id;
Rows: 5
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE pas2pa ALL NULL NULL NULL NULL 1150 Using temporary; Using filesort
1 SIMPLE pase ALL NULL NULL NULL NULL 1794
1 SIMPLE pa ALL NULL NULL NULL NULL 5584 Using where
1 SIMPLE pas eq_ref PRIMARY PRIMARY 4 hellynewengland.pas2pa.products_attributes_sets_id 1 Using where
1 SIMPLE pov eq_ref PRIMARY PRIMARY 8 hellynewengland.pa.options_values_id,const 1
I added an index as follows:
alter table products_attributes_sets add index idx_products_attributes_sets_id (products_attributes_sets_id);
and got an error: PRIMARY and INDEX keys should not both be set for column `products_attributes_sets_id`
my second index was:
ALTER TABLE products_attributes_sets_elements ADD INDEX idx_options_values_id( options_values_id )
with no errors
And guess what! ?
Current Parse Time: 1.374 s with 80 queries
Whoooo WEEEEEEE! Thank you! This just made my week! THANK YOU!
#686
Posted 04 December 2008, 23:31
I was having a terrible time when I had everything in the store on specials. The website slowed to a crawl ... and I have only 1300 items in my store. There was a MySQL query that was examining 1.2 million records!! Pages were taking sometimes 20 seconds to load!!
This was the query causing the problems:
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 = '45' order by pd.products_name limit 0, 20;
My host (Pair) had to disable my DB because it was slowing down the entire DB server.
I followed the advice from Carine Bruyndoncx earlier in this thread and executed the following query to create an index in the specials table
It worked wonders! Pages begin loading immediately.
Now I need to install the thumbnail contrib to further speed things up :-)
Thank you, thank you, thank you.
This was the query causing the problems:
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 = '45' order by pd.products_name limit 0, 20;
My host (Pair) had to disable my DB because it was slowing down the entire DB server.
I followed the advice from Carine Bruyndoncx earlier in this thread and executed the following query to create an index in the specials table
create index IDXSPECIALSPRICE on specials (products_id,status,specials_new_products_price)
It worked wonders! Pages begin loading immediately.
Now I need to install the thumbnail contrib to further speed things up :-)
Thank you, thank you, thank you.
#687
Posted 31 December 2008, 04:16
bruyndoncx, on Aug 8 2007, 10:28 PM, said:
if people add a lot of specials oscommerce tends to slow down. I believe this is because the specials table only has an primary key on specials_id.
When listing products, I noticed that the full table was included in the queryplan, while it could have been matched on products_id.
I've added this index which has all the fields necessary for the listing to show the specials data, so no need to access the underlying table data.
I'm under the impression that is has improved things on my site, but it would be nice for people with loads of special pricing to confirm this really makes a difference.
When listing products, I noticed that the full table was included in the queryplan, while it could have been matched on products_id.
I've added this index which has all the fields necessary for the listing to show the specials data, so no need to access the underlying table data.
create index IDXSPECIALSPRICE on specials (products_id,status,specials_new_products_price)
I'm under the impression that is has improved things on my site, but it would be nice for people with loads of special pricing to confirm this really makes a difference.
I have to reply to this. It took me the last 3-4 days to figure out how to optimize my store. query used to take 3-4 seconds. Now, a catalog query only takes less than 1 second. I'm amazed.
I been changes MySQL variables but nothing worked. Until I found this.
Great jobs. My thumbs up for the Pro. You should be proud of yourself.
Edited by jasonpham, 31 December 2008, 04:18.
#688
Posted 23 January 2009, 07:25
Does anyone know of any side effects to commenting out the who's online functions including: require(DIR_WS_FUNCTIONS . 'whos_online.php');? I know it saves a few queries. I personally never use this attribute in the admin. I was unsure if it has any effect on checking out, cart contents, etc. I cannot seem to find anything.
#689
Posted 03 March 2009, 13:15
Heres my humble tiny tips 
When your editing the files there really is no need to have this in the top of every one....
My other tips are install phpaccelerator if you have a dedicated server, and also enable Mysql Query Caching.
Most of your queries will then be in this sort of range...
[0] => 0.000403
[1] => 0.000382
[2] => 0.000329
[3] => 0.000298
[4] => 0.000724
[5] => 0.000296
[6] => 0.000292
[7] => 0.000951
(notice the number of zeros there)
Does anyone know any way to optimize this a bit please, its from the visitors tracking from Ian-san which is/was one of the most popular contribs.
update visitors set counter = counter+1, online = now() where browser_ip = 'xxxxxxxx' AND browser_language = 'en-gb,en;q=0.5' AND to_days(now()) - to_days(date) < 1
I think this comes from here somewhere...
The other one that could be optized a bit is
SELECT COUNT(*) as count from topics where parent_id = '2'
Which comes from here I think...
Thanks in advance.
When your editing the files there really is no need to have this in the top of every one....
osCommerce, Open Source E-Commerce Solutions http://www.oscommerce.com Copyright (c) 2003 osCommerce Released under the GNU General Public LicenseLikewise some contribs, have about 30 lines of text at the top. If you get in the habit of reducing this, and searching for white spaces and removing these it all helps.
My other tips are install phpaccelerator if you have a dedicated server, and also enable Mysql Query Caching.
Most of your queries will then be in this sort of range...
[0] => 0.000403
[1] => 0.000382
[2] => 0.000329
[3] => 0.000298
[4] => 0.000724
[5] => 0.000296
[6] => 0.000292
[7] => 0.000951
(notice the number of zeros there)
Does anyone know any way to optimize this a bit please, its from the visitors tracking from Ian-san which is/was one of the most popular contribs.
update visitors set counter = counter+1, online = now() where browser_ip = 'xxxxxxxx' AND browser_language = 'en-gb,en;q=0.5' AND to_days(now()) - to_days(date) < 1
I think this comes from here somewhere...
// Add Customer entry to Visitors
if (!tep_db_num_rows(tep_db_query("select counter FROM " . TABLE_VISITORS . " where (browser_ip = '" . $b_ip . "' || customers_id = '" . (int)$customer_id . "') AND browser_language = '" . $b_lang . "' AND to_days(now()) - to_days(date) < 1"))) {
// A1. No entries exist so add new Customer Entry to Visitors
tep_db_query("insert into " . TABLE_VISITORS . " (date,customers_id,counter,browser_ip,browser_language,online,language,refere
r,uri) values (now(),'" . (int)$customer_id . "','1','".$b_ip."','".$b_lang."',now(),'".$language."','".tep_db_input($referer)."','".tep_db_input($uri)."')");
// A2. Add and update Customer entries in Visitors Trace
if (((substr($b_lang, 0, 1) == '[') && $trace_spiders) || ((substr($b_lang, 0, 1) != '[') && $trace_users)){
tep_db_query("insert into " . TABLE_VISITORS_TRACE . " (date,customers_id,browser_ip,uri) values (now(),'" . (int)$customer_id . "','".$b_ip."','".tep_db_input($uri)."')");
tep_db_query("update " . TABLE_VISITORS_TRACE . " set customers_id = '" . (int)$customer_id . "' where browser_ip = '" . $b_ip . "' AND to_days(now()) - to_days(date) < 1");
}
} else {
// B1. Either one or two entries exist
if (tep_db_num_rows(tep_db_query("select counter FROM " . TABLE_VISITORS . " where (browser_ip = '" . $b_ip . "' || customers_id = '" . (int)$customer_id . "') AND browser_language = '" . $b_lang . "' AND to_days(now()) - to_days(date) < 1")) == '1') {
// B1a. Only one Customer entry so just update it in Visitors
tep_db_query("update " . TABLE_VISITORS . " set counter = counter+1, customers_id = '" . (int)$customer_id . "', online = now(), browser_ip = '" . $b_ip . "' where (customers_id = '" . (int)$customer_id . "' || browser_ip = '" . $b_ip . "') AND browser_language = '" . $b_lang . "' AND to_days(now()) - to_days(date) < 1");
} else {
// B1b. Must be two Customer entries so need to delete newest and update oldest in Visitors
$counter_old_raw = tep_db_query("select browser_id, counter FROM " . TABLE_VISITORS . " where (browser_ip = '" . $b_ip . "' || customers_id = '" . (int)$customer_id . "') AND browser_language = '" . $b_lang . "' AND to_days(now()) - to_days(date) < 1 order by browser_id DESC limit 1");
$counter_old = tep_db_fetch_array($counter_old_raw);
$counter_sum = $counter_old["counter"];
$browser_id = $counter_old["browser_id"];
tep_db_query("delete from " . TABLE_VISITORS . " where browser_id = '" . $browser_id . "'");
tep_db_query("update " . TABLE_VISITORS . " set counter = counter + '" . $counter_sum . "' + 1, customers_id = '" . (int)$customer_id . "', online = now(), browser_ip = '" . $b_ip . "' where (customers_id = '" . (int)$customer_id . "' || browser_ip = '" . $b_ip . "') AND browser_language = '" . $b_lang . "' AND to_days(now()) - to_days(date) < 1");
}
// B2. Add and update Customer entries in Visitors Trace
if (((substr($b_lang, 0, 1) == '[') && $trace_spiders) || ((substr($b_lang, 0, 1) != '[') && $trace_users)){
tep_db_query("insert into " . TABLE_VISITORS_TRACE . " (date,customers_id,browser_ip,uri) values (now(),'" . (int)$customer_id . "','".$b_ip."','".tep_db_input($uri)."')");
tep_db_query("update " . TABLE_VISITORS_TRACE . " set customers_id = '" . (int)$customer_id . "', browser_ip = '" . $b_ip . "' where (browser_ip = '" . $b_ip . "' || customers_id = '" . $customer_id . "') AND to_days(now()) - to_days(date) < 1");
}
}
The other one that could be optized a bit is
SELECT COUNT(*) as count from topics where parent_id = '2'
Which comes from here I think...
function tep_count_articles_in_topic($topic_id, $include_inactive = false) {
$articles_count = 0;
if ($include_inactive == true) {
$articles_query = tep_db_query("SELECT COUNT(*) as total from " . TABLE_ARTICLES . " a, " . TABLE_ARTICLES_TO_TOPICS . " a2t where (a.articles_date_available IS NULL or to_days(a.articles_date_available) <= to_days(now())) and a.articles_id = a2t.articles_id and a2t.topics_id = '" . (int)$topic_id . "'");
} else {
$articles_query = tep_db_query("SELECT COUNT(*) as total from " . TABLE_ARTICLES . " a, " . TABLE_ARTICLES_TO_TOPICS . " a2t where (a.articles_date_available IS NULL or to_days(a.articles_date_available) <= to_days(now())) and a.articles_id = a2t.articles_id and a.articles_status = '1' and a2t.topics_id = '" . (int)$topic_id . "'");
}
$articles = tep_db_fetch_array($articles_query);
$articles_count += $articles['total'];
$child_topics_query = tep_db_query("select topics_id from " . TABLE_TOPICS . " where parent_id = '" . (int)$topic_id . "'");
if (tep_db_num_rows($child_topics_query)) {
while ($child_topics = tep_db_fetch_array($child_topics_query)) {
$articles_count += tep_count_articles_in_topic($child_topics['topics_id'], $include_inactive);
}
}
return $articles_count;
Thanks in advance.
#691
Posted 31 March 2009, 22:29
KJ666, on Mar 19 2009, 12:15 AM, said:
I am still learning here but how do i find out how many Queries and the Parse Time??
download and install it from here http://addons.oscommerce.com/info/2575
#692
Posted 18 April 2009, 00:51
I notice that most of the contribs relating to cacheing various thing have fallen by the wayside so I was quite happy to find this thread. My quick tip is to convert the stylesheet.css into a stylesheet.php allowing the gzip to compress it.
Simply place this at the top of your stylesheet.css file and rename to stylesheet.php
Then change the link in all catalog pages from
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
to:
<link href="<?php (DIR_WS_HTTP_CATALOG); ?>/stylesheet.php" rel="stylesheet" type="text/css" />
My stylesheet is up around 30k and this works a treat.
I also use http://www.websiteoptimization.com/services/analyze/ thay have a free web site speed test that yeilds some useful info.
graeme
Simply place this at the top of your stylesheet.css file and rename to stylesheet.php
<?php
ob_start ("ob_gzhandler");
header("Content-type: text/css");
header("Cache-Control: must-revalidate");
$offset = 60 * 60 ;
$ExpStr = "Expires: " .
gmdate("D, d M Y H:i:s",
time() + $offset) . " GMT";
header($ExpStr);
?>
Then change the link in all catalog pages from
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
to:
<link href="<?php (DIR_WS_HTTP_CATALOG); ?>/stylesheet.php" rel="stylesheet" type="text/css" />
My stylesheet is up around 30k and this works a treat.
I also use http://www.websiteoptimization.com/services/analyze/ thay have a free web site speed test that yeilds some useful info.
graeme
#694
Posted 22 May 2009, 17:32
Chemo, on Oct 31 2004, 05:03 AM, said:
The only requirement to the optimizations are a net increase in performance and NO LOSS OF FUNCTIONALITY.
Vanilla Install Stats:
Parse Time: .328 seconds
Queries - 92
Disable Category Counts:
Parse Time: .259 seconds
Queries: 52
So, save 40 queries right off the bat for disabling category counts.
More optimizations tomorrow...whe I start to dissect the code and get this puppy screaming.
Vanilla Install Stats:
Parse Time: .328 seconds
Queries - 92
Disable Category Counts:
Parse Time: .259 seconds
Queries: 52
So, save 40 queries right off the bat for disabling category counts.
More optimizations tomorrow...whe I start to dissect the code and get this puppy screaming.
I am new here and try to catch up: disabling category counts: you mean in the admin/my store?
#695
-
- Community Member
- 6,463 posts
- Real Name:Robert Fisher
- Gender:Male
- Location:Stowmarket - Suffolk - UK
Posted 22 May 2009, 17:34
revamp, on May 22 2009, 06:32 PM, said:
I am new here and try to catch up: disabling category counts: you mean in the admin/my store?
Yes it creates a ridiculous number of queries.
Ultimate SEO Urls 5 PRO - Multi Language Modern, Powerful SEO Urls
KissMT Dynamic SEO Meta & Canonical Header Tags
KissER Error Handling and Debugging
If you found my post useful please click the green + sign to the right
Please only PM me for paid work.
KissMT Dynamic SEO Meta & Canonical Header Tags
KissER Error Handling and Debugging
If you found my post useful please click the green + sign to the right
Please only PM me for paid work.
#696
Posted 22 May 2009, 17:39
FWR Media, on May 22 2009, 06:34 PM, said:
Yes it creates a ridiculous number of queries.
Does anyone has a clue how to solve this for the admin/catalog?
#697
-
- Community Member
- 6,463 posts
- Real Name:Robert Fisher
- Gender:Male
- Location:Stowmarket - Suffolk - UK
Posted 22 May 2009, 17:41
revamp, on May 22 2009, 06:39 PM, said:
I have noticed that, I have 30.000 categories and subs in total and it took minutes to load the page, i have disabled the count and now the page loads instantly.
Does anyone has a clue how to solve this for the admin/catalog?
Does anyone has a clue how to solve this for the admin/catalog?
Not really .. the vast majority of "optimisations" are aimed at the front end as that affects the customer and the customer affects the pocket.
Ultimate SEO Urls 5 PRO - Multi Language Modern, Powerful SEO Urls
KissMT Dynamic SEO Meta & Canonical Header Tags
KissER Error Handling and Debugging
If you found my post useful please click the green + sign to the right
Please only PM me for paid work.
KissMT Dynamic SEO Meta & Canonical Header Tags
KissER Error Handling and Debugging
If you found my post useful please click the green + sign to the right
Please only PM me for paid work.
#698
Posted 22 May 2009, 18:02
FWR Media, on May 22 2009, 06:41 PM, said:
Not really .. the vast majority of "optimisations" are aimed at the front end as that affects the customer and the customer affects the pocket.
Another question is which addon to use from http://addons.oscommerce.com/info/2575 (the Output Query Debug) ? And how to use the output to determine the possible keys to set?
Anybody knows why Chemo is banned?
Sorry for the questions that may be silly for others than me.
Edited by revamp, 22 May 2009, 18:14.
#699
Posted 22 May 2009, 18:28
FWR Media, on Jun 5 2008, 09:00 PM, said:
Jans version is better as it's a good idea to add a "tag" so as not to conflict with functions .. the "tag" in Jans case is idx_ where idx_ is known not to be a function.
#700
-
- Community Member
- 6,463 posts
- Real Name:Robert Fisher
- Gender:Male
- Location:Stowmarket - Suffolk - UK
Posted 22 May 2009, 18:31
revamp, on May 22 2009, 07:28 PM, said:
Maybe a stupid question but do i achieve the same thing as i put an index on a field in the database directly using phpmyadmin?
Yes but it is best to use a prefix like idx_ as has already been explained. Also bear in mind that indices should be used sparingly as they take up a lot of space .. you can quickly end up having a detrimental effect.
Edited by FWR Media, 22 May 2009, 18:32.
Ultimate SEO Urls 5 PRO - Multi Language Modern, Powerful SEO Urls
KissMT Dynamic SEO Meta & Canonical Header Tags
KissER Error Handling and Debugging
If you found my post useful please click the green + sign to the right
Please only PM me for paid work.
KissMT Dynamic SEO Meta & Canonical Header Tags
KissER Error Handling and Debugging
If you found my post useful please click the green + sign to the right
Please only PM me for paid work.
