A Store Speed Optimization in Progress

[boxtel]

my appologies for not writing the main part of the reply.

my website is not saving the configuration on the config_cache file, right now my default language is english and my default currency was US Dollars. i tried updating the config thru admin to change to spanish and pesos and it didnt work: then i modified manually config_cache.php and it shows me that it worked on the admin, but if u look at the page prices have 2 decimal points, which is the base for the US dollars currency. the Pesos currency (which i created previously and had given the status of default) doesnt have any decimals. thats the reason for me to believe that the admin is not saving the configuration. im thinking of uninstalling the config_cache contribution to see if that solves the problem. i know its not a threat for this forum so my appologies, i?ve been too much time in front of the computer.

<{POST_SNAPBACK}>

 

 

you can simply go to the file that should be saved and read it, see if it is correct.

[Guest]

9) Modified all product/product_description lookups to use that mysql code that retrieves all rows (ceases the need for the count(*) query) even if you use limit (sorry can't remember its too early).

10) Went back and modified all product/product_description to pull ALL rows and then modifying the split page class and all outputting pages to data_seek to where it needs instead of using limits and offsets.

 

See my proposal below

 

11) Combined application_top and all static, rarely changing include files (classes, functions, etc.) into one single php file so its not having to do so many damn file requests.

 

If you have your own server, you could install something like php-accelerator which will preparse all your files and store the compiled versions !

 

http://www.phpaccelerator.co.uk

 

The results of mod #8 did a lot of help since the count(*) queries took about 0.250s on average and the offset real query took 0.350s.  And adding the sql_query_results (or whatever the mysql code) is didn't add any significant parse time to the offset query netting about 1/4 of a second of all pages with product listings.

 

Ouch ! How many products do you have total and per category ?

 

The results are much improved.  I was able to knock down the parse time of the application_top file from 0.90s to 0.55s at stage #10.  At stage #11...surprisingly the application_top file parse time dropped down to 0.05s (a ~90% speed increase  :thumbsup: ).  It could be a fluke though...I think it may just be how my Win XP desktop test server handles file requests so poorly.

 

Yeah, if you run it on Windows, file access will certainly be slower. If you run it on linux/reiserfs, well... on my machine, a simple test (just a tar) reads about 28.000 files per second if the files are in the disk cache, which they should be in your case. However you might still benefit from the "big file" thing if using php-accelerator, simply because it won't have to stat() all these files to see if they've been modified (although reiserfs stats about 60K files/second)

 

I now have too many disk temporary tables being created...its like 85-90% of total temporary tables.  Yeah I know its mod #10 doing it...but still.  The most products i'll ever have in a category is going to be say 1,000 or so.  I bumped up the temporary table limit size to like 32M and every other config setting I can think of but still I hate that 85-90%.  I can't find a way of finding out how big some of the result sets are in bytes either.

 

Wow, what kind of store do you have to have this kind of counts ? Electronic parts ?

 

I'd suggets the following :

 

Create table products_presorted :

- categories_id

- order_by_id

- index

- products_id

 

Pre-fill this table with the following data :

 

For each category,

For each "sort by" column,

index = 0

For each product in : (Get all products id's from this category, sorted by the right column)

(just one SELECT... ORDER BY)

Insert into products_presorted, the products_id and the index

index++

 

This gives you :

categories_id order_by_id index products_id

1 1 0 product1

1 1 1 product2

....

1 1 N productN

 

1 2 0 product50 (products are ordered differently)

1 2 1 product20

....

1 2 N productXX

 

etc...

For instance order_by_id=1 for Name, 2 for Price, etc.

 

Create an index on (categories_id, order_by_id, index)

 

Thus, to get all products from a category :

SELECT products_id FROM products_presorted WHERE category=... AND order_by_id=... ORDER BY index

 

And the index column serves you to get your split pages result (as it was generated without holes, it will work). Suppose you have 10 products per page :

 

SELECT products_id FROM products_presorted

WHERE category=...

AND order_by_id=...

AND index BETWEEN (page*10) AND (page*10+9)

ORDER BY index

 

Just a simple index lookup. Should take less than 1ms.

 

To have the Count of products in one category :

SELECT index FROM products_presorted

WHERE category=...

AND order_by_id=...

ORDER BY index DESC LIMIT 1

 

or :

SELECT max(index) FROM products_presorted

WHERE category=...

AND order_by_id=...

 

You can even have cheap category counts :

SELECT max(index) FROM products_presorted

WHERE order_by_id=...

GROUP BY category_id

(I don't know if MySQL can optimize this to use the index).

 

All you need now is a cron job to update this table. Byebye temporary tables...

What do you think of this ?

[Guest]

To construct an in-memory tree of all your categorise (very fast if you have only a few):

function tep_show_category($counter) 
{
   global $foo, $categories_string, $id, $languages_id, $pf_cats, $cPath;
$path = explode( '_', $cPath );
$query = tep_db_query( "SELECT c.categories_id, cd.categories_name, c.parent_id, c.categories_class from " . 
 TABLE_CATEGORIES . " c, " .
 TABLE_CATEGORIES_DESCRIPTION . " cd 
 WHERE c.categories_id = cd.categories_id 
	 AND cd.language_id='" . $languages_id ."'
 ORDER by parent_id, sort_order, cd.categories_name;" );

$pf_cats = array();
while( $row = tep_db_fetch_array( $query ))
 $pf_cats[$row['parent_id']][] = $row;

echo "<div class=cat_column>\n";
pf_recurse_catbox( $path );
echo "</div>\n";
}

[Guest]

Yikes !

 

Installing php-accelerator brought down my page times from 120 ms to 50 ms !

Both these times contain about 15 ms of mysql queries (about 20 queries).

 

Looks acceptable !

 

Log the times of your boxes with sts_template :

 

sts_stop_capture : add after ob_get_contents

$sts_time_log[]= '['.$sts_block_name.' '.((microtime()-$sts_timer)*1000).' ms]';

 

sts_start_capture : add

$sts_timer = microtime();

 

database.php, add in tep_db_query :

$all_queries_log[] = array($query,(microtime()-$sttime)*1000);

 

sts_display_output: add below $template['banner'] = $sts_block['banner'];

 

$t = '';

$tt = 0;

foreach( $all_queries_log as $i=>$q)

{

$tt += $q[1];

$t .= "<br>[$i] ".$q[1].' ms '.$q[0].'<br>';

}

$page_parse_time = (microtime()- PAGE_PARSE_START_TIME)*1000;

$boxes_time = implode( $sts_time_log, '<br>' );

$template['querylog'] = "page time : $page_parse_time ms, total query time : $tt ms ($i queries)<br>$boxes_time<br>$t";

 

 

For instance I get, on a products listing page :

page time : 57.906 ms, total query time : 18.577 ms (20 queries)

[applicationtop2header 2.856 ms]

[header 1.544 ms]

[header2columnleft 0.343 ms]

[categorybox 2.175 ms]

[whatsnewbox 4.813 ms]

[loginbox 0.701 ms]

[languagebox 3.714 ms]

[currenciesbox 1.734 ms]

[columnleft2columnright 13.101 ms]

[cartbox 0.6 ms]

[footer2applicationbottom 0.332 ms]

[headertags 0.211 ms]

 

Damn ! That whatsnewbox is Slow !

 

columnleft2columnright is in fact the 'content'

[♥akmac]

Chemo,

 

Just installed Page_Cache_v1

 

and am getting this error:

 

Warning: fopen(mydomain/catalog//cache/_catalog_index.php__english_USD.cache): failed to open stream: No such file or directory in /home/alaskaje/public_html/catalog/includes/classes/page_cache.php on line 245

 

Warning: fwrite(): supplied argument is not a valid stream resource in /mydomain/catalog/includes/classes/page_cache.php on line 246

 

Warning: fclose(): supplied argument is not a valid stream resource in /mydomain/catalog/includes/classes/page_cache.php on line 247

 

The first path looks off, but I don't know (php in general)how to edit that line in page_cache.php

 

Also, and probably unrelated-when I was installing the mod the code in html_output.php:

-------------------------------

if (isset($_sid)) {

$link .= $separator . $_sid;

}

-------------------------------

Wasn't there.

 

Did you replace it already with your Cname Pname mod? Anyway, I didn't add the:

-------------------------------

if (!tep_session_is_registered('customer_id') && ENABLE_PAGE_CACHE == 'true' && class_exists('page_cache')) {

$link .= $separator . '<osCsid>';

} elseif (isset($_sid)) {

$link .= $separator . $_sid;

}

-------------------------------

because I didn't know where to put it. Does it still need to be in there?

[AXM]

http://www.phpaccelerator.co.uk

Ouch ! How many products do you have total and per category ?

Wow, what kind of store do you have to have this kind of counts ? Electronic parts ?

 

I have an automotive parts store with currently 8,000 products and I expect atleast 50,000 before I put it online.

 

I really love your idea on the faster CRON-updateable table to do the selection and sorting. I've done the best job possible doing with the indexing so its all indexed column/key to indexed column/key on all the joins, but still with the results being that significant the sorting had to be converted to slow disk based temporary tables.

 

Question: What would you recommend being the best way to utilize the CRON table? Doing it as a subquery? Or left joining all tables onto the query you listed in your post? Or ...?

 

Using your method since the sorting is already done based on the index col in your table, I'm very interested to see how much of a speed advantage that would be. Thank you. :thumbsup:

 

Okay now that the slow down on the sorting is out of the way...what do you think about my method of pulling all the rows for a category at one time and then using...

 

mysql_data_seek($listing_query, $listing_split->offset) // As a replacement for OFFSET

 

and

 

for ($i = 0; $i < $listing_split->number_of_rows_on_current_page; $i++ ) // Loop - As a replacement for LIMIT

 

So that way I am only doing one query for each category x each sort order x 2 (for each order asc or desc). And i'm pretty sure using an array_flip or one of PHP's many array sorting features I can do away with a different query for asc or desc and have php take care of it instead of mysql. So that leaves one query for each category x each sort order (which will end up being only two for me - sort by product_name and sort by product_price). And all of these queries are instantaneous after the first one as all future requests are drawn from the cache. So I eliminate the small MAX_DISPLAY_SEARCH_RESULTS result size of what...10/20 results and go for the whole batch one time.

 

So my current way reduces the amount of total product listing queries to be performed on the site. For me there is going to be 2 total for each category instead of 4 x number of pages per category. So if a category listing is split into 20 pages there will be 80 queries total if you viewed all the pages in all 4 sort orders while my method is still permanently set on 2 and the queries on pages 2-N and switching the sort orders will continue to pull only cached results not run new queries. If there is 200 pages of results there will be 800 queries while mine is still 2. Understand my method a little better now?

 

So based on my scenario of say 200 categories and maybe an average of 500 products per category:

 

1) Would it be better for a few large result queries or multiple 10-20 result queries?

 

2) Caching-wise I'd think the few larger results would work better, right?

 

3) Memory issue-wise, is PHP able to efficiently cope with such a large dataset on a moderately trafficed website? I think the typical row size would be less than 1.5KB (i'm only pulling product_id, product_name, product_model, product_image, and 4 additional varchar(255) fields). So even at 1,000 products for one of the larger categories that would only be 1.5MB. Is 1.5MB worth of contents in an associative array something to worry about? Is there going to be any strains in php handling such a large variable array? What if the array contents is 2.5MB or 5MB worth of data? Is there a recommended maximum limit on the size of an associative array for a moderately trafficed website?

 

I'm mainly worried about the memory issue. I wouldn't want to see the server crashing because of lack of memory from these large variables eating up resources.

[AXM]

I've tried MMCache (now eAccelerator I think) and it does wonders. I don't think PHP Accelerator and eAccelerator combine include files, they just compile them seperately. Atleast that's what the files look like in MMCache's temp folder...just like individually compiled php files. So there still has to be a file system request to retrieve each individual file. Might as well just tack everything all into one big application_top file if you ask me.

 

As for the moment I like doing the development with MySQL Query Cache and MMCache turned OFF. I prefer doing optimizing the code in its worst case scenarios.

 

Okay here's some parse time numbers...if it helps.

 

MySQL Query Cache OFF and MMCache OFF

 

Index?cpath=236_65

Results: 325 items @ 10/page

Total Parse Time: 0.26275s

Application Top Parse Time: 0.05408s

Total Query Parse Time: 0.15458s

Query Count: 19

Manufacturer Filter Query: 0.02224s

Product Count Query: Eliminated

Product Listing Query: 0.11559s

 

Index?manufacturers_id=12

Results: 336 items @ 10/page

Total Parse Time: 0.25066s

Application Top Parse Time: 0.05145s

Total Query Parse Time: 0.14972s

Query count: 14

Category Filter Query: 0.02094s

Product Count Query: Eliminated

Product Listing Query: 0.10828s

 

 

MySQL Query Cache ON and MMCache OFF

 

Index?cpath=236_65

Results: 325 items @ 10/page

Total Parse Time: 0.12086s (+54%)

Application Top Parse Time: 0.05335s (+1%)

Total Query Parse Time: 0.0161s (+90%)

Query Count: 19

Manufacturer Filter Query: 0.00034s (+98%)

Product Count Query: Eliminated

Product Listing Query: 0.00653s (+94%)

 

Index?manufacturers_id=12

Results: 336 items @ 10/page

Total Parse Time: 0.11585s (+54%)

Application Top Parse Time: 0.05264s (-2%)

Total Query Parse Time: 0.01564s (+90%)

Query count: 14

Category Filter Query: 0.00045s (+98%)

Product Count Query: Eliminated

Product Listing Query: 0.00673s (+94%)

 

 

MySQL Query Cache ON and MMCache ON

 

Index?cpath=236_65

Results: 325 items @ 10/page

Total Parse Time: 0.06408s (+76%)

Application Top Parse Time: 0.01689s (+69%)

Total Query Parse Time: 0.01335s (+91%)

Query Count: 19

Manufacturer Filter Query: 0.00035s (+98%)

Product Count Query: Eliminated

Product Listing Query: 0.00430s (+96%)

 

Index?manufacturers_id=12

Results: 336 items @ 10/page

Total Parse Time: 0.05689s (+77%)

Application Top Parse Time: 0.01363s (+74%)

Total Query Parse Time: 0.01217s (+92%)

Query count: 14

Category Filter Query: 0.00035s (+98%)

Product Count Query: Eliminated

Product Listing Query: 0.00362s (+97%)

[Guest]

...

So based on my scenario of say 200 categories and maybe an average of 500 products per category:

 

1) Would it be better for a few large result queries or multiple 10-20 result queries? 

 

2) Caching-wise I'd think the few larger results would work better, right?

 

3) Memory issue-wise, is PHP able to efficiently cope with such a large dataset on a moderately trafficed website?  I think the typical row size would be less than 1.5KB (i'm only pulling product_id, product_name, product_model, product_image, and 4 additional varchar(255) fields).  So even at 1,000 products for one of the larger categories that would only be 1.5MB.  Is 1.5MB worth of contents in an associative array something to worry about?  Is there going to be any strains in php handling such a large variable array?  What if the array contents is 2.5MB or 5MB worth of data?  Is there a recommended maximum limit on the size of an associative array for a moderately trafficed website?

...

<{POST_SNAPBACK}>

If you even have moderate traffic to your site the memory will be allocated in short order. If you use that method of large data queries be sure to unset() the array when appropriate.

 

In reference to #3 above: it would take 1.5Mb of storage either in RAM or swap. If you have 10 people visiting your site that would require 15.0 Mb of storage (by now you're server is swapping large amounts of data) and the net result is SLOWER rendering.

 

I would keep the stock queries as they are for the categories...

 

Bobby

[Guest]

I have an automotive parts store with currently 8,000 products and I expect atleast 50,000 before I put it online.

 

If you intend to manage that with the default osCommerce admin, i'm in awe to your courage ;)

 

I really love your idea on the faster CRON-updateable table to do the selection and sorting.  I've done the best job possible doing with the indexing so its all indexed column/key to indexed column/key on all the joins, but still with the results being that significant the sorting had to be converted to slow disk based temporary tables.

 

I think you understand the problem well. It could be spelled "How to kill a database server 101"...

 

Any db server (even MySQL) will be very fast if you pull out a limited number of rows from a table, using indexes for all seeking operations, even if you join 3-5 tables. What kills the speed is the sorting, because it has to :

- look up ALL the products in a category and store them in a temporary zone

- sort this temporary list by name, price, or whatever other sort criterion you have

- clip off and return only 10 out of these

 

So basically, if you have 200 products and you only want 10, you still get the query time for 200 ! And worse, mysql has to redo the whole thing for each page even though the sort results are the same, because you want a different portion. This is very inefficient !

 

This inefficient query should be planned like this :

get products in category X order by name limit A,B:

- join the products_to_categories, products and products_description tables to get the 200 products (index scan on the three tables)

- disk sort

- limit

 

Thus, as I said, I'd advise to create a table with the results of the sorting cached, according to all the possible sort criterions. You may have to add the language_id too, because sorting by name will give a different sort order with different languages. This way, you only join 10 rows (one page) and avoid the sort.

 

Even if you had a table with an indexed id column, and you wanted results ordered by this column, LIMIT A,B will suck if A!=0, because it has to scan the index to skip A rows. No other way ! This is why I added a numeric column with no holes to be able to replace LIMIT with index_number BETWEEN A AND A+B. This is very important to your performance ! With this extra column, all it has to do is lookup A in the index, then get the next records. It is faster, and more important, the speed stays the same if you query the first page or the last !

 

Now, I proposed to store only products_id's in this table. This is because, this 'cache' table avoids all the hard work of extracting all the products in a category and sorting them. You could go all the way and also store product name, price, special status, etc. in this table, so that product listing becomes just ONE query. I wonder if you would win. By all means, try it on YOUR catalog, which is where it matters, and tell us how it goes !

 

Don't forget to ANALYZE your tables !

 

Time the join of a cache table with just the product-ids along with products, products_descriptions (and also maybe specials), and just a query on the cache table. Is it worth the trouble to cache everything ? You gotta answer that.

 

Question:  What would you recommend being the best way to utilize the CRON table?  Doing it as a subquery?  Or left joining all tables onto the query you listed in your post?  Or ...?

 

Well you can SELECT ... WHERE products_id IN (subquery), or you can JOIN. Try both and see what's faster. MySQL query optimizer being what it is, one will probably be faster than the other. I don't know which !

 

Using your method since the sorting is already done based on the index col in your table, I'm very interested to see how much of a speed advantage that would be.  Thank you.  :thumbsup:

 

To know if it will really help you, measure the time spent in the product count and listing queries. This method will only save time on that, but in your case, I'd bet it is the worst slowdown, so you win.

 

Okay now that the slow down on the sorting is out of the way...what do you think about my method of pulling all the rows for a category at one time and then using...

 

I don't think it is a good idea, let me explain :

 

Do you prefer to retrieve 200 rows from the database and keep only 10, or just retrieve 10 rows ?

In all cases you retrieve these rows from the cache table ORDERED BY index_number, so your pagination is right.

 

Option 1 :

- look up the first index_number in the index, grab this row and the next 10.

 

Option 2 :

- look up the first row in the index, with category, order_by, and index_number=1

- skip N rows

- return 10 rows

 

Option 1 will be a big win.

 

1) Would it be better for a few large result queries or multiple 10-20 result queries? 

2) Caching-wise I'd think the few larger results would work better, right?

 

1) multiple small queries, and 2) no.

Because MySQL caches the entire query, caching each page will give you a lot of queries. It would be nice if you could cache a query, and then reissue the same query with some LIMIT and it would take the results from the cache, but it doesn't.

 

Know the following :

Transferring data from MySQL is expensive. MySQL has to fetch the rows, serialize them, transmit them over a socket, then the mysql library in PHP receives them, deserializes them, and converts them into PHP data structures. This is VERY slow, I've been surprised to see how slow it is just for a simple mysql_fetch_assoc !)

 

So fetching rows from the database and then ditching them should only be done when the time wasted doing this would otherwise be even more wasted in multiple queries, like in my categorybox example.

 

In the case of the categorybox, it is a waste to fetch say 20 categories and just use 10, but it would be even slower to have 3 separate queries, one for each level of the tree. So it's OK.

 

In you case, for products, consider the following example :

 

	function time_query( $sql )
{
 $times = 10;
 
 $qt = floatval(microtime(true));
 for($i=0; $i<$times; $i++)
	 $q = tep_db_query( $sql );
 $qt = (floatval(microtime(true)) - $qt) * 1000.0/$times;
 
 $tt = floatval(microtime(true));
 for($i=0; $i<$times; $i++)
 {
	 $q = tep_db_query( $sql );
	 $c = 0;
	 while( tep_db_fetch_array( $q ))
   $c++;
 }
 $tt = (floatval(microtime(true))-$tt) * 1000.0/$times;
 
 echo "<pre>\nQuery : $sql\nRows : $c Total time : $tt ms, query only time $qt ms\n</pre>";
}

var_dump(floatval(microtime(true)));
time_query( "SELECT * FROM products;" );
time_query( "SELECT p.*, pd.* FROM products p, products_description pd WHERE p.products_id=pd.products_id AND pd.language_id=4;" );
$ids = "(163, 172, 40, 110, 176, 198, 108, 52, 79, 93)";
time_query( "SELECT * FROM products WHERE products_id IN $ids;" );
time_query( "SELECT p.*, pd.* FROM products p, products_description pd WHERE p.products_id=pd.products_id AND pd.language_id=4 AND p.products_id IN $ids;" );

 

Query : SELECT * FROM products;

Rows : 159 Total time : 4.5811 ms, query only time 1.6 ms

 

Query : SELECT p.*, pd.* FROM products p, products_description pd WHERE p.products_id=pd.products_id AND pd.language_id=4;

Rows : 159 Total time : 8.1194 ms, query only time 4.1379 ms

 

Query : SELECT * FROM products WHERE products_id IN (163, 172, 40, 110, 176, 198, 108, 52, 79, 93);

Rows : 10 Total time : 0.5846 ms, query only time 0.4011 ms

 

Query : SELECT p.*, pd.* FROM products p, products_description pd WHERE p.products_id=pd.products_id AND pd.language_id=4 AND p.products_id IN (163, 172, 40, 110, 176, 198, 108, 52, 79, 93);

Rows : 10 Total time : 0.983 ms, query only time 0.8433 ms

 

So, retrieving 10 rows instead of 150 is about 10x faster. Unsurprising !

 

3) Memory issue-wise, is PHP able to efficiently cope with such a large dataset on a moderately trafficed website?  I think the typical row size would be less than 1.5KB (i'm only pulling product_id, product_name, product_model, product_image, and 4 additional varchar(255) fields).  So even at 1,000 products for one of the larger categories that would only be 1.5MB.  Is 1.5MB worth of contents in an associative array something to worry about?  Is there going to be any strains in php handling such a large variable array?  What if the array contents is 2.5MB or 5MB worth of data?  Is there a recommended maximum limit on the size of an associative array for a moderately trafficed website?

 

Well, it will most probably kill your server ;)

 

Moving around 5 MB of data structures would be very small for a cron script, for instance, but for a webpage it's huge !

 

Considering SELECTing just the rows you really need ;)

[Guest]

I've made a little test with postgres to see how it played with various query plans.

 

products : 2544 rows

So the cache table has 2544 rows (only one order_by etc.)

 

CREATE VIEW prodanddesc AS SELECT p.*, pd.* FROM products_description pd join products p on p.products_id=pd.products_id2;

 

Selecting 10 products from the 2544, with a subquery taking the products_ids from the cache table, takes 0.6 ms. Not that bad :

 

select products_name from prodanddesc where products_id in (select ids from ids where ser between 800 and 810);

 

ser is the index_number from before.

Getting a page of 10 products out of 80.000 takes about 0.8 milliseconds.

 

Strangely, postgres is slower with fewer products ! I guess a few millions wouldn't disturb it, but with 100 it takes about 2 ms because it chooses another, less optimal query plan! I'd have to tune this animal a little better... right now it's as un-tuned as it gets.

 

My version of MySQL does not support subqueries, and I'm too lazy to install 4.1, thus you will have to check for yaself, but it does look like the subquery version will perform adequately.

 

You could also use a three-table join :

select * from prodanddesc pad, ids i where pad.products_id=i.ids and i.ser between 8000 and 8010;

 

(note prodanddesc is a view, so it's really a two tables join :)

select * from products p, products_description pd, ids i where p.products_id=i.ids and pd.products_id2=i.ids and i.ser between 8000 and 8010;

 

it takes about exactly the same time as the subquery (postgres can rewritea subquery as a join if it feels like it, so no surprise here).

 

Writing it this way saves 5 microseconds as it does not have to expand the view definition. Oh, well.

 

So yes, I believe you're OK with a table caching just the product_id's. And it'll be a lot smaller, too !

[Guest]

Just realized I forgot the "ORDER BY index_number" (which is the column ser in my example). Oh well. The chosen query plan gives the rows in that order anyway, so adding it does not add any time to the query.

 

However, I forgot that this ordering by cannot be done with the subquery form, so youll have to do a three table join.

 

With 10 out of 130 products postgres does it in 1.3 ms, kicking it a little so it uses a better query plan gives about 0.6 ms.

 

Unfortunately MySQL does not time queries down to the milliseconds. I tested with 10 out of 80k products and it says, with a 3-table join "0.00s" which means to me "less than 5 ms", so it could be anything from 10x slower than postgres to faster... in any case it's fast enough !

[Guest]

Just realized EXPLAIN does not display the query time. What does it display then ?

phpMyAdmin says it takes 1 ms. Looks good.

[AXM]

I'm still undecided on the issue. Sorry guys.

 

Its just that I like the fact that no matter what page someone is on in a category it is going to be cached after the first hit. Let's assume the server is just started and no query caching what so ever has been performed. The first visitor to the website clicks on the category box on the link for "Widgets". The server then serves up the Widget page (index?cpath=1) to the visitor. It will query the database:

 

Variance 1: Query all rows for entire category sorted by widget name.

Variance 2: Query for category contents sorted by widget name with only results 1-20.

 

The visitor gets to see the split page results of 1-20. Say the customer wants to look at more widgets and clicks to see page 2. Page 2 will be parsed by the server and it will again query the database:

 

Variance 1: Duplicate query (Query all rows for entire category sorted by widget name ). Will be served from cache. Will not have to provide any more row lookups nor temporary tables nor any type of sorting.

Variance 2: Query for category contents sorted by widget name with only results 21-40.

 

So no matter what page the visitor is on it will all be cached from the first hit on the category. Get where I'm going with this?? Its a trade off:

 

Benefits:

1) Only 1 query per category per sort column per sort direction. IRREGARDLESS of page. Only 1 temporary table and sorting to every be performed as long as the query stays in the cache.

2) Less mysql server processing as all subsequent queries will be handled by query cache and not re-processed with only different limit and offset values.

 

Undetermined:

Memory conerns.

 

Sacrifices:

On my estimates will take 0.025s longer for the query than a limited results query.

 

I really think I am on to something. It works perfectly from my point of view. Only thing I don't know is whether how significantly it will kill the server at a higher load.

 

I have something similiar to Chemo's debuging feature and I only see improvements. And I look at the qcache low memory purge and don't see anything. Also I have set it up so that when the results set are large I can just send a flag to the query function and it will return to the traditional query format (currently used for allproducts and advance searches).

 

So what's going to take more resources? Re-doing a large query with a temporary table sort every page or sending a megabyte or two from the query cache to PHP as a variable array on every page?

[AXM]

PeuFeu,

I really like your idea on the external sort table. Just wondering how efficient it will be on my particular setup. I know for sure it won't work on the sort by price strategy because I have a customer discount/group discount contribution (which I heavily hacked up) and got it to be able to sort by prices with specials included. Initially it didn't and somehow I got it to work. So if someone is logged in and group/individual specials exist...it will be sorting wrong.

 

And i'm trying to think if I have any other discrepancies that might clash with your method. But so far I'm like 85% sure its going to help on the sort by name product listings.

 

I have over 42 contributions added - 95% of them I went through the code line by line for cleanliness, efficiency, and improvement. And added countless numbers of my own tweaks. So sometimes I have to take some time to analyze how everything is going to work together.

 

And i'm also going to take your suggestion and try creating a temporary table that contains just the necessary columns for product listings. Which i'm pretty sure should do a fantastic job. I'll probably tackle it this week and let you guys know the results.

 

Thanks again both PeuFeu and Chemo...keep up the good work with the speed improvements.

[AXM]

Here's another one.

 

I've noticed that not many people took the effort to improve their advanced search page. The stock OSC's advanced search page has a lot of parse time stuck trying to create the category tree (atleast on mine it was 0.7 seconds with ~200 categories). Even a november CVS snapshot I'm playing with still doesn't fix it even though I think there is a category tree made specifically for that. *shrug*

 

Mine...a measily 0.11764s with 10 queries with query cache and mmcache off. The November OSC CVS its a 0.848s parse time advanced search page with 306 queries. OUCH!!

 

Maybe people don't notice it as much with only a couple categories..but still...306 queries??? :-)

[♥akmac]

Chemo,

 

Just installed Page_Cache_v1

 

and am getting this error:

 

Warning: fopen(mydomain/catalog//cache/_catalog_index.php__english_USD.cache): failed to open stream: No such file or directory in /home/alaskaje/public_html/catalog/includes/classes/page_cache.php on line 245

 

Warning: fwrite(): supplied argument is not a valid stream resource in /mydomain/catalog/includes/classes/page_cache.php on line 246

 

Warning: fclose(): supplied argument is not a valid stream resource in /mydomain/catalog/includes/classes/page_cache.php on line 247

 

The first path looks off, but I don't know (php in general)how to edit that line in page_cache.php

 

Also, and probably unrelated-when I was installing the mod the code in html_output.php:

-------------------------------

if (isset($_sid)) {

$link .= $separator . $_sid;

}

-------------------------------

Wasn't there. 

 

Did you replace it already with your Cname Pname mod?  Anyway, I didn't add the:

-------------------------------

if (!tep_session_is_registered('customer_id') && ENABLE_PAGE_CACHE == 'true' && class_exists('page_cache')) {

$link .= $separator . '<osCsid>';

} elseif (isset($_sid)) {

$link .= $separator . $_sid;

}

-------------------------------

because I didn't know where to put it.  Does it still need to be in there?

<{POST_SNAPBACK}>

 

 

Regarding the first three warnings-they existed because the "cache" directory was located in www.sitename.com/cache and should've been located in www.sitename.com/catalog/cache.

 

Chemo,

 

the second question concerning the code on html_output.php, still exists. It isn't causing any errors-but I'm not knowledgeable enough in php to know the function of the code-and thus whether or not I need it.

[Guest]

Outstanding...let's hear back from one more person that is is functioning properly and I'll release another version with updated install directions.

<{POST_SNAPBACK}>

 

Finally made the code mods as suggested. Works for me as well.

 

-jared

[RushRC]

I am basically a noob and I was attempting to do some of the optimization contributions of yours.

 

I have done

Administration -> Configuration -> Show Category Counts = false

Keeping the Banners because I will use that

Did the Implemented Tax Class

And the Config_cache_1_2

 

After doing the last I could no longer use my Easy populate. Any suggestions or can you help? The EP screen comes up fine and it allows me to select the .txt file and then acts as it will load to db then nothing jus teh page is refreshed.

 

Thanks

[Guest]

I am basically a noob and I was attempting to do some of the optimization contributions of yours.

 

I have done

Administration -> Configuration -> Show Category Counts = false

Keeping the Banners because I will use that

Did the Implemented Tax Class

And the Config_cache_1_2

 

After doing the last I could no longer use my Easy populate.  Any suggestions or can you help?  The EP screen comes up fine and it allows me to select the .txt file and then acts as it will load to db then nothing jus teh page is refreshed.

 

Thanks

<{POST_SNAPBACK}>

The config_cache should not affect that at all. Are you sure your host did not upgrade PHP and modify settings in light of the recent PHP vulnerabilities?

[RushRC]

I just checked and they did a update yesterday :'(

 

Here is what they said

PHP's default upload_tmp_dir was /tmp, which is no longer world writeable as this is where the worms using the phpBB exploit download their programs to spread. This was necessary to stop our servers from attacking others. Your scripts should not download files to this area anyways as it is not available on all the servers your site is hosted on.

 

If you use PHP to upload files, you need to have a custom php.ini. You need to uncomment and edit the upload_tmp_dir variable to point to a directory that is writeable by you, which would be your home directory or a subdirectory inside your home directory. DO NOT change that directories permissions to 777, it is already writeable by your php scripts if it is the default 755.

 

 

;;;;;;;;;;;;;;;;

; File Uploads ;

;;;;;;;;;;;;;;;;

 

; Whether to allow HTTP file uploads.

file_uploads = On

 

; Temporary directory for HTTP uploaded files (will use system default if not

; specified).

upload_tmp_dir = /www/u/username

 

; Maximum allowed size for uploaded files.

upload_max_filesize = 2M

 

Please note that if you are uploading files larger than 2MB's you will also need to change upload_max_filesize.

 

Thanks I will look into this more.

 

*Solicitation

 

Thanks

bruce

[Guest]

...

Chemo,

 

the second question concerning the code on html_output.php, still exists.  It isn't causing any errors-but I'm not knowledgeable enough in php to know the function of the code-and thus whether or not I need it.

<{POST_SNAPBACK}>

Use this code:

if (!tep_session_is_registered('customer_id') && ENABLE_PAGE_CACHE == 'true' && class_exists('page_cache')) {
 $link .= $separator . '<osCsid>';
 $seo_link .= $separator . '<osCsid>';
} elseif (isset($_sid)) {
     $link .= $separator . $_sid;
  $seo_link .= $separator . $_sid;
   }

[Mark Evans]

First of all what is really apparent here is that the osCommerce coders are quite incompetent.

 

How to win friends an influence people :D

 

Hindsight is 20/20 vision as they say :-"

[berkedam]

How to win friends an influence people :D

 

Hindsight is 20/20 vision as they say :-"

<{POST_SNAPBACK}>

Oh my, so sorry, forgot to sent best wishes for 2005+ especially to all influential teammembers and other forum members :)

[Guest]

How to win friends an influence people :D

 

Hindsight is 20/20 vision as they say :-"

<{POST_SNAPBACK}>

 

True. Some people lack, shall we say, any proper social skills at all. From my experience, folks that make posts like this are also the folks with <15 posts total at the time of their scathing. They walk into the room, and inform all that we're lucky to be graced with their presence. Occasionally they tone it down and actually contribute.

 

-jared

[mugitty]

I've selectively implemented some of the changes here and have seen good results. I'm baffled about one thing currently...

 

I turned off the category counts in admin, yet I still see this in the query results on every page

[11] => select count(*) as count from categories where parent_id = '1'

[12] => select count(*) as count from categories where parent_id = '2'

[13] => select count(*) as count from categories where parent_id = '3'

[14] => select count(*) as count from categories where parent_id = '23'

[15] => select count(*) as count from categories where parent_id = '24'

[16] => select count(*) as count from categories where parent_id = '25'

any ideas where I can eliminate these - If I'm not mistaken, these are the queries for counting products in each of my top-level categories, are they not?