897 replies to this topic

[revamp]

FWR Media, on May 22 2009, 07:31 PM, said:

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.

I see: when i put an key on a field in phpmyadmin (change field, click on key) then the key is the same as the field, so it is better to use the SQL command from Jan and add the prefix.
That brings me to the next question: how do you know which field must be a key? Because, stupid as i am, my first idea was to put a key to all the fields as i dont know which field to put a key on...

[FWR Media]

revamp, on May 22 2009, 07:39 PM, said:

I see: when i put an key on a field in phpmyadmin (change field, click on key) then the key is the same as the field, so it is better to use the SQL command from Jan and add the prefix.
That brings me to the next question: how do you know which field must be a key? Because, stupid as i am, my first idea was to put a key to all the fields as i dont know which field to put a key on...

If you are unsure I really wouldn't mess with it. Are you running RC2a?

Edited by FWR Media, 22 May 2009, 18:47.

[revamp]

FWR Media, on May 22 2009, 07:47 PM, said:

If you are unsure I really wouldn't mess with it. Are you running RC2a?

No 2.2ms2, i have just made the index upgrade from 2.2rc2a as suggested in this topic.
I surely will not mess around, therefor i keep on asking:
When i use the Output Query Debug, how do i determine from the output which field could have an index?

Edited by revamp, 22 May 2009, 19:25.

[FWR Media]

revamp, on May 22 2009, 08:21 PM, said:

No 2.2ms2, i have just made the index upgrade from 2.2rc2a as suggested in this topic.
I surely will not mess around, therefor i keep on asking:
When i use the Output Query Debug, how do i determine from the output which field could have an index?

Find the slowest queries .. there are many page options to press to guage this site wide.

Grab the slow query from queries debug output .. then ..

Run the query in phpMyAdmin adding EXPLAIN to the start.

EXPLAIN
SELECT whatever FROM thistable WHERE this = 'that'

you will then get very useful output.

Understanding that output is a different matter so play around .. see what the slow queries report then google the terms found.

Good luck.

[revamp]

FWR Media, on May 22 2009, 08:44 PM, said:

Find the slowest queries .. there are many page options to press to guage this site wide.

Grab the slow query from queries debug output .. then ..

Run the query in phpMyAdmin adding EXPLAIN to the start.

EXPLAIN
SELECT whatever FROM thistable WHERE this = 'that'

you will then get very useful output.

Understanding that output is a different matter so play around .. see what the slow queries report then google the terms found.

Good luck.

Thank you very much for your patience and great help!
Last question: which version of the Output Queries Debug do you advice? 1.2?

[FWR Media]

revamp, on May 22 2009, 09:04 PM, said:

Thank you very much for your patience and great help!
Last question: which version of the Output Queries Debug do you advice? 1.2?

Had to think about that one been ages since I used it .. I use my own .. having looked the last one I used was 1.6

[revamp]

FWR Media, on May 22 2009, 09:07 PM, said:

Had to think about that one been ages since I used it .. I use my own .. having looked the last one I used was 1.6

Amazing!!! 500 queries at index.php, a lot of work to do, so i play around:
I had this: [493]select startdate, counter from counter
with this time: [493] => 0.023619
EXPLAIN sql returned that there was no index in table "counter" so i put an index on "startdate" and look at the time needed for that querie: [493] => 0.000295 thats about 100 times faster!

Is this the right way to do this?

Because i noticed that [6] => select banners_id, date_scheduled from banners where date_scheduled != '' had a time of [6] => 0.026885 the first time and the second time (so with no changes on that) [5] => 0.000236
Notice also that apparantly [2] => select languages_id, name, code, image, directory from languages order by sort_order dissapeared the second time, probably because i just refreshed the window after adding the index to "counter"?
I also notice that the first time the Currente Parse Time was 1.081 s and the second time 0.346 s, caching and fluctuations in querietimes causes that? When i open the page in Firefox, the first time 0.347 parse time with querie [6], refresh gives 0.295 and querie[6] has dissappeared. Another thing: the total amount of queries stays the same 500, i see now that there are a few UPDATE queries at the end, ofcourse...

[pbor1234]

I was wondering; is it still usefull to apply configuration cache? If i take a look at the online performance i don't see much delay there:

select configuration_key as cfgKey, configuration_value as cfgValue from configuration
Explain:
id 1
select_type SIMPLE
table configuration
type ALL
possible_keys
key
key_len
ref
rows 503
Extra
Results:
min 0,0002
max 0,0201
mean 0,0036
stddev 0,0066

PS: These are results from a maintenance function i wrote, it simply repeats the same query 25 times and calculates some statistics.

So basically i would win no more than 7ms?

Paul

[FWR Media]

pbor1234, on May 27 2009, 09:24 PM, said:

I was wondering; is it still usefull to apply configuration cache? If i take a look at the online performance i don't see much delay there:

select configuration_key as cfgKey, configuration_value as cfgValue from configuration
Explain:
id 1
select_type SIMPLE
table configuration
type ALL
possible_keys
key
key_len
ref
rows 503
Extra
Results:
min 0,0002
max 0,0201
mean 0,0036
stddev 0,0066

PS: These are results from a maintenance function i wrote, it simply repeats the same query 25 times and calculates some statistics.

So basically i would win no more than 7ms?

Paul

Configuration cache is a valid thought as it is a large table scan but is flawed as a concept because the cache by it's very nature is a configuration option itself.

[pbor1234]

FWR Media, on May 27 2009, 10:52 PM, said:

Configuration cache is a valid thought as it is a large table scan but is flawed as a concept because the cache by it's very nature is a configuration option itself.

Robert,
I'm not understanding your remark, sorry. I'm trying to make a trade-off between having the configuration from the table (the original osc implementation) or have a copy on the filesystem and read it from there. On the pro's side i got a better performance (i guess) but furthermore there is nothing but con's: complexity, risk of having inconsistent content in the cache, multiple access to the same file and thus blocking requests.
So the performance win must be >50ms or so, otherwise the drawback outrun the benefits (to my opinion).
Paul

[revamp]

I have installed the Page Cache V1.5 and things are working out fine but when i click on a manufacturer I get ridiculous long parsing times. It seems that this querie causes it:

select p.products_image, m.manufacturers_name, p.products_model, pd.products_name, p.products_quantity, 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 p left join specials s on p.products_id = s.products_id, products_description pd, manufacturers m where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '16' order by pd.products_name limit 0, 20

When I put it in SQL with EXPLAIN its shows this:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE s system idx_specials_products_id NULL NULL NULL 0 const row not found
1 SIMPLE m const PRIMARY PRIMARY 4 const 1
1 SIMPLE pd index PRIMARY products_name 240 NULL 389965 Using where
1 SIMPLE p eq_ref PRIMARY PRIMARY 8 denit_oscommerce.pd.products_id 1 Using where

So products_description seems to be the problem: the queries searches 389965 rows bur the PRIMARY KEY (products_name) is set...

Does anyone knows how to solve this problem?

[Jan Zonjee]

revamp, on Jun 1 2009, 06:20 PM, said:

I have installed the Page Cache V1.5 and things are working out fine but when i click on a manufacturer I get ridiculous long parsing times. It seems that this querie causes it:

select p.products_image, m.manufacturers_name, p.products_model, pd.products_name, p.products_quantity, 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 p left join specials s on p.products_id = s.products_id, products_description pd, manufacturers m where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '16' order by pd.products_name limit 0, 20

When I put it in SQL with EXPLAIN its shows this:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE s system idx_specials_products_id NULL NULL NULL 0 const row not found
1 SIMPLE m const PRIMARY PRIMARY 4 const 1
1 SIMPLE pd index PRIMARY products_name 240 NULL 389965 Using where
1 SIMPLE p eq_ref PRIMARY PRIMARY 8 denit_oscommerce.pd.products_id 1 Using where

So products_description seems to be the problem: the queries searches 389965 rows bur the PRIMARY KEY (products_name) is set...

Does anyone knows how to solve this problem?

So if you have a large amount of products it is quicker to only use the tables products and manufacturers in this query. Then get the products_id's and with that list you do queries for products_description, then specials to see if there are special prices. Combine everything et voilá, faster site
At least that is how I would try to go for lower parse times in your case.

[revamp]

Jan Zonjee, on Jun 1 2009, 05:35 PM, said:

So if you have a large amount of products it is quicker to only use the tables products and manufacturers in this query. Then get the products_id's and with that list you do queries for products_description, then specials to see if there are special prices. Combine everything et voilá, faster site
At least that is how I would try to go for lower parse times in your case.

Abracadabra for me...how do i do that?

[pbor1234]

revamp, on Jun 1 2009, 06:20 PM, said:

I have installed the Page Cache V1.5 a...

I'm still having doubts about optimizing the configurations; Did it work for you? How large was the performance improvement you measured? Just for your information; my server reaches average query time of 0.2ms for the table scan of 500 items.
Thanks,
Paul

[Jan Zonjee]

revamp, on Jun 1 2009, 07:08 PM, said:

Abracadabra for me...how do i do that?

I played a bit with that but found it is not as simple as I hoped because most of the times pd.products_name is used as a sorting field. So you almost always need the products_name.
I think using a trimmed down temporary table (see the ENGINE = MEMORY statement in the new function osc_create_tmp_name_table) might do the trick working with large data sets. If you get errors that this is not supported leave out the ENGINE = MEMORY part.

In includes/functions/general.php add before the last ?> add:

<?php
/*
  $Id: product_listing.php 1739 2007-12-20 00:52:16Z hpdl $

  osCommerce, Open Source E-Commerce Solutions
  [url="http://www.oscommerce.com"]http://www.oscommerce.com[/url]

  Copyright © 2003 osCommerce

  Released under the GNU General Public License
*/
  global $random_name;
  $listing_split = new splitPageResults($listing_sql, MAX_DISPLAY_SEARCH_RESULTS, 'p.products_id');

  if ( ($listing_split->number_of_rows > 0) && ( (PREV_NEXT_BAR_LOCATION == '1') || (PREV_NEXT_BAR_LOCATION == '3') ) ) {
?>
<table border="0" width="100%" cellspacing="0" cellpadding="2">
  <tr>
    <td class="smallText"><?php echo $listing_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
    <td class="smallText" align="right"><?php echo TEXT_RESULT_PAGE . ' ' . $listing_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>
  </tr>
</table>
<?php
  }

  $list_box_contents = array();

  for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
    switch ($column_list[$col]) {
      case 'PRODUCT_LIST_MODEL':
        $lc_text = TABLE_HEADING_MODEL;
        $lc_align = '';
        break;
      case 'PRODUCT_LIST_NAME':
        $lc_text = TABLE_HEADING_PRODUCTS;
        $lc_align = '';
        break;
      case 'PRODUCT_LIST_MANUFACTURER':
        $lc_text = TABLE_HEADING_MANUFACTURER;
        $lc_align = '';
        break;
      case 'PRODUCT_LIST_PRICE':
        $lc_text = TABLE_HEADING_PRICE;
        $lc_align = 'right';
        break;
      case 'PRODUCT_LIST_QUANTITY':
        $lc_text = TABLE_HEADING_QUANTITY;
        $lc_align = 'right';
        break;
      case 'PRODUCT_LIST_WEIGHT':
        $lc_text = TABLE_HEADING_WEIGHT;
        $lc_align = 'right';
        break;
      case 'PRODUCT_LIST_IMAGE':
        $lc_text = TABLE_HEADING_IMAGE;
        $lc_align = 'center';
        break;
      case 'PRODUCT_LIST_BUY_NOW':
        $lc_text = TABLE_HEADING_BUY_NOW;
        $lc_align = 'center';
        break;
    }

    if ( ($column_list[$col] != 'PRODUCT_LIST_BUY_NOW') && ($column_list[$col] != 'PRODUCT_LIST_IMAGE') ) {
      $lc_text = tep_create_sort_heading($HTTP_GET_VARS['sort'], $col+1, $lc_text);
    }

    $list_box_contents[0][] = array('align' => $lc_align,
                                    'params' => 'class="productListing-heading"',
                                    'text' => '&nbsp;' . $lc_text . '&nbsp;');
  }

  if ($listing_split->number_of_rows > 0) {
//   echo $listing_split->sql_query;

    $rows = 0;
    $listing_query = tep_db_query($listing_split->sql_query);

    $no_of_listings = tep_db_num_rows($listing_query);

	while ($_listing = tep_db_fetch_array($listing_query)) {
	$listing[] = $_listing;
	$list_of_prdct_ids[] = $_listing['products_id'];
	}

// an extra query is needed for all the specials

	$specials_query = tep_db_query("select products_id, specials_new_products_price from " . TABLE_SPECIALS . " where products_id in (" . implode(',', $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'], 'products_price' => '', 'specials_new_products_price' => $specials_array['specials_new_products_price'] , 'final_price' => $specials_array['specials_new_products_price']);
	}

// add the correct specials_new_products_price and replace final_price
	for ($x = 0; $x < $no_of_listings; $x++) {
    if(!empty($new_s_prices)) {
	    for ($i = 0; $i < count($new_s_prices); $i++) {
		    if( $listing[$x]['products_id'] == $new_s_prices[$i]['products_id'] ) {
		    $listing[$x]['specials_new_products_price'] = $new_s_prices[$i]['specials_new_products_price'];
		    $listing[$x]['final_price'] = $new_s_prices[$i]['final_price'];
		    }
	    }
	  } // end if(!empty($new_s_prices)
	} // end for ($x = 0; $x < $no_of_listings; $x++)

//    while ($listing = tep_db_fetch_array($listing_query)) { (was original code)
	for ($x = 0; $x < $no_of_listings; $x++) {
      $rows++;

      if (($rows/2) == floor($rows/2)) {
        $list_box_contents[] = array('params' => 'class="productListing-even"');
      } else {
        $list_box_contents[] = array('params' => 'class="productListing-odd"');
      }

      $cur_row = sizeof($list_box_contents) - 1;

      for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
        $lc_align = '';

        switch ($column_list[$col]) {
          case 'PRODUCT_LIST_MODEL':
            $lc_align = '';
            $lc_text = '&nbsp;' . $listing[$x]['products_model'] . '&nbsp;';
            break;
          case 'PRODUCT_LIST_NAME':
            $lc_align = '';
            if (isset($HTTP_GET_VARS['manufacturers_id'])) {
              $lc_text = '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&products_id=' . $listing[$x]['products_id']) . '">' . $listing[$x]['products_name'] . '</a>';
            } else {
              $lc_text = '&nbsp;<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $listing[$x]['products_id']) . '">' . $listing[$x]['products_name'] . '</a>&nbsp;';
            }
            break;
          case 'PRODUCT_LIST_MANUFACTURER':
            $lc_align = '';
            $lc_text = '&nbsp;<a href="' . tep_href_link(FILENAME_DEFAULT, 'manufacturers_id=' . $listing[$x]['manufacturers_id']) . '">' . $listing[$x]['manufacturers_name'] . '</a>&nbsp;';
            break;
          case 'PRODUCT_LIST_PRICE':
            $lc_align = 'right';

            if (tep_not_null($listing[$x]['specials_new_products_price'])) {
              $lc_text = '&nbsp;<s>' .  $currencies->display_price($listing[$x]['products_price'], tep_get_tax_rate($listing[$x]['products_tax_class_id'])) . '</s>&nbsp;&nbsp;<span class="productSpecialPrice">' . $currencies->display_price($listing[$x]['specials_new_products_price'], tep_get_tax_rate($listing[$x]['products_tax_class_id'])) . '</span>&nbsp;';
            } else {
              $lc_text = '&nbsp;' . $currencies->display_price($listing[$x]['products_price'], tep_get_tax_rate($listing[$x]['products_tax_class_id'])) . '&nbsp;';
            }
            break;
          case 'PRODUCT_LIST_QUANTITY':
            $lc_align = 'right';
            $lc_text = '&nbsp;' . $listing[$x]['products_quantity'] . '&nbsp;';
            break;
          case 'PRODUCT_LIST_WEIGHT':
            $lc_align = 'right';
            $lc_text = '&nbsp;' . $listing[$x]['products_weight'] . '&nbsp;';
            break;
          case 'PRODUCT_LIST_IMAGE':
            $lc_align = 'center';
            if (isset($HTTP_GET_VARS['manufacturers_id'])) {
              $lc_text = '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&products_id=' . $listing[$x]['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $listing[$x]['products_image'], $listing[$x]['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a>';
            } else {
              $lc_text = '&nbsp;<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $listing[$x]['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $listing[$x]['products_image'], $listing[$x]['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a>&nbsp;';
            }
            break;
          case 'PRODUCT_LIST_BUY_NOW':
            $lc_align = 'center';
            $lc_text = '<a href="' . tep_href_link(basename($PHP_SELF), tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $listing[$x]['products_id']) . '">' . tep_image_button('button_buy_now.gif', IMAGE_BUTTON_BUY_NOW) . '</a>&nbsp;'; // EOF Separate Pricing per Customer
            break;
        }

        $list_box_contents[$cur_row][] = array('align' => $lc_align,
                                               'params' => 'class="productListing-data"',
                                               'text'  => $lc_text);
      }
    }

    new productListingBox($list_box_contents);
  } else {
    $list_box_contents = array();

    $list_box_contents[0] = array('params' => 'class="productListing-odd"');
    $list_box_contents[0][] = array('params' => 'class="productListing-data"',
                                   'text' => TEXT_NO_PRODUCTS);

    new productListingBox($list_box_contents);
  }
  $clean_up = tep_db_query("drop table if exists " . $random_name . "");
  if ( ($listing_split->number_of_rows > 0) && ((PREV_NEXT_BAR_LOCATION == '2') || (PREV_NEXT_BAR_LOCATION == '3')) ) {
?>
<table border="0" width="100%" cellspacing="0" cellpadding="2">
  <tr>
    <td class="smallText"><?php echo $listing_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
    <td class="smallText" align="right"><?php echo TEXT_RESULT_PAGE . ' ' . $listing_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>
  </tr>
</table>
<?php
  }
?>

Edited by Jan Zonjee, 03 June 2009, 19:45.

[revamp]

pbor1234, on Jun 1 2009, 08:56 PM, said:

I'm still having doubts about optimizing the configurations; Did it work for you? How large was the performance improvement you measured? Just for your information; my server reaches average query time of 0.2ms for the table scan of 500 items.
Thanks,
Paul

Page Caching V1.5 works good for me: i have querie times of 0.000070 seconds for the categorie queries. I have 30.000 categories (in total with subs and subsubs) and 520.000 articles in the shop and i`m still working on a shared server...this is a particular quest of mine to see what is possible with a fairly standard OSC shop on a shared server. From there i try to optimize the queries.
Only the first time i load the page it takes about 3 seconds for 500 queries, but after that the page cache does his work: when i click the biggest categorie i get 0.592 - 0.814 seconds with 1402 queries...Refresh gives then 0.329 - 0.355 seconds with only 13 queries (the most of the queries are then cached).

[revamp]

Jan Zonjee, on Jun 1 2009, 11:25 PM, said:

I played a bit with that but found it is not as simple as I hoped because most of the times pd.products_name is used as a sorting field. So you almost always need the products_name.
I think using a trimmed down temporary table (see the ENGINE = MEMORY statement in the new function osc_create_tmp_name_table) might do the trick working with large data sets. If you get errors that this is not supported leave out the ENGINE = MEMORY part.
SNIP

Thanks Jan, you are the best.

I will try this in a day or so (little busy right now) and let you know the results.

[pbor1234]

revamp, on Jun 2 2009, 09:09 PM, said:

Page Caching V1.5 works good for me: i have querie times of 0.000070 seconds for the categorie queries. I have 30.000 categories (in total with subs and subsubs) and 520.000 articles in the shop and i`m still working on a shared server...this is a particular quest of mine to see what is possible with a fairly standard OSC shop on a shared server. From there i try to optimize the queries.
Only the first time i load the page it takes about 3 seconds for 500 queries, but after that the page cache does his work: when i click the biggest categorie i get 0.592 - 0.814 seconds with 1402 queries...Refresh gives then 0.329 - 0.355 seconds with only 13 queries (the most of the queries are then cached).

Carlo, uhhhh, sorry i mixed up the configuration cache with page cache. So my question was about the configuration cache and not about the page cache.
PS: I know what you are trying to do (from the dutch forum .
Paul

[Jan Zonjee]

revamp, on Jun 2 2009, 09:09 PM, said:

Refresh gives then 0.329 - 0.355 seconds with only 13 queries (the most of the queries are then cached).

Try logging-in as a customer on that point. I expect the cache can't be used anymore then.

[revamp]

Jan Zonjee, on Jun 2 2009, 09:35 PM, said:

Try logging-in as a customer on that point. I expect the cache can't be used anymore then.

That`s right, Jan. You are a step ahead because that was the next question: how to fix this?