Jump to content

Archived

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

Chemo

A Store Speed Optimization in Progress

Recommended Posts

I found this thread very instructional! Chemo, thanks so much for your hard and excellent work you've done for the community!

 

Since the session sid issue has been intensively discussed here, I'd like to learn from you the compatibility of your Page Cache contrib with other contribs. There are currently two useful contribs out there I think (maybe wrong) might be related to your work, namely AutoLogin and Admin Account with Access Level. The AutoLogin contrib prompts the customers a checkbox when they login enabling them not repeating the login process on their future visit. The Admin Account with Access contrib makes the admin area of osC more secure by giving each of the administrator an access level.

 

Would you please test your great contrib of Page Cache with them?

 

Thanks in advance

Share this post


Link to post
Share on other sites

The page cache is compatible with both of those contributions.

 

The autologin (secure) logs the custom in and there is a switch that automatically turns the page cache off for logged in customers.

 

The admin with access levels is on the admin control panel side and is not affected by catalog cache.

Share this post


Link to post
Share on other sites

There is an automatic switch with the page cache that turns off when a customer logs in.

Share this post


Link to post
Share on other sites

Chemo

 

I have a problem installing the Output Queries Debug contribution.

 

I keep having this :

Warning: session_start(): Cannot send session cache limiter - headers already sent (output started at /home/i8carati/public_html/catalog/includes/functions/database.php:272) in /home/i8carati/public_html/catalog/includes/functions/sessions.php on line 76

every time I load any page. I also tried with a previous version, but warning is still there.

 

I think my shop( heavily modded ) needs to be optimized since I can see some 170 queries on index page and 176 on product_info. could you quickly list the changes I should make in order of importance ? The website is fully template based , would it be a problem with the page cache contrib ?

 

Thanks

Franco


Outside links in signatures are not allowed!

Share this post


Link to post
Share on other sites
...

I keep having this :

Warning: session_start(): Cannot send session cache limiter - headers already sent (output started at /home/i8carati/public_html/catalog/includes/functions/database.php:272) in /home/i8carati/public_html/catalog/includes/functions/sessions.php on line 76

every time I load any page. I also tried with a previous version, but warning is still there.

It sounds like there is some text being output on or around line 272. Can you paste lines ~250-300?

 

I think my shop( heavily modded ) needs to be optimized since I can see some 170 queries on index page and 176 on product_info. could you quickly list the changes I should make in order of importance ? The website is fully template based , would it be a problem with the page cache contrib ?

 

Thanks

Franco

It's not compatible with template system...you're stuck with normal query optimizations. Don't lose faith! You can trim the fat and get it down to a workable number pretty easy.

Share this post


Link to post
Share on other sites

Thanks Chemo,

 

Problem with installation solved...now queries and parse time are showing below footer.

It's not compatible with template system...you're stuck with normal query optimizations. Don't lose faith! You can trim the fat and get it down to a workable number pretty easy

 

Pity I cannot use page caching.

Do you think my website performance is very poor ? now I just had a parse time of 11.873 !!!! with 170 queries on index page and I'm the only one browsing .

What would happen when some 50 users are browsing simultaniously ?


Outside links in signatures are not allowed!

Share this post


Link to post
Share on other sites
Thanks Chemo,

 

Problem with installation solved...now queries and parse time are showing below footer.

Pity I cannot use page caching.

Do you think my website performance is very poor ? now I just had a parse time of 11.873 !!!! with 170 queries on index page and I'm the only one browsing .

What would happen when some 50 users are browsing simultaniously ?

That query count is on the higher side...but I've see worse. Is this your server or virtually hosted?

 

Look at this: http://www.18carati.com/catalog/index.php?output=1

 

Scroll to the bottom and check out all the queries. There is a lot of redundant queries. Install the tax class contribution. Also, it looks like the category build is happening TWICE. Look at your code and find that immediately. Good luck!

Share this post


Link to post
Share on other sites
That query count is on the higher side...but I've see worse.  Is this your server or virtually hosted?

 

It's a virtual server, good resources, but not enough for the amount of queries I have.

Can I ask you some ping / traceruote to 18carati.com from your location ? just to see how it goes...

 

Thanks for now

 

Franco


Outside links in signatures are not allowed!

Share this post


Link to post
Share on other sites

Reply from 207.58.134.247: bytes 32 time=60ms TTL=49

Reply from 207.58.134.247: bytes 32 time=53ms TTL=49

Reply from 207.58.134.247: bytes 32 time=55ms TTL=49

Reply from 207.58.134.247: bytes 32 time=42ms TTL=49

Reply from 207.58.134.247: bytes 32 time=62ms TTL=49

 

There's the ping stats from my workstation...

 

I still suggest you optimize your queries!

Share this post


Link to post
Share on other sites

Chemo or anyone, any help on improving this query....

 

$new = tep_db_query("select p.products_model, p.products_id, pd.products_name, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_DESCRIPTION . " pd where m.manufacturers_id = p.manufacturers_id and p.products_id = p2c.products_id and p.products_id = pd.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . $new_products_category_id . "' and p.products_status = '1' order by p.products_last_modified DESC limit " . MAX_DISPLAY_NEW_PRODUCTS);

 

its my only real slow query on this site, it does the recently updated products for the category, needs to get PRODUCT ID, NAME, MODEL, MANUFACTURER NAME, PRICE, IMAGE, for the most recently updated products in category and sub catagories.

 

most often that query alone adds 3-5 seconds to page parse time

 

click for slow query example with debug output

 

site has a fair amount of traffic and over 50,000 products

 

For the most part other than that query the site performs very very good. Hosted on a dedicated machine 2ghz and 768meg ram MySQL 3.23.58

 

Thanks for any help.

John


Car Shop Inc.

421 12th street

Moline, IL 61265

Share this post


Link to post
Share on other sites
That query count is on the higher side...but I've see worse.  Is this your server or virtually hosted?

 

Look at this: http://www.18carati.com/catalog/index.php?output=1

 

Scroll to the bottom and check out all the queries.  There is a lot of redundant queries.  Install the tax class contribution.  Also, it looks like the category build is happening TWICE.  Look at your code and find that immediately.  Good luck!

also seems you customer group query is going multiple


Treasurer MFC

Share this post


Link to post
Share on other sites

First of all what is really apparent here is that the osCommerce coders are quite incompetent. You don't write a function containing database queries, like tep_get_tax_rate, then call it everywhere instead of doing a Join.

 

Also, the page generation times you post are "normal" for osCommerce, but for any other site they would be considered really, really high. If I was designing a webstore like this, on a low-end server I would target less than 5 milliseconds per page as the absolute maximum acceptable time ; and I think it would not be impossible to meet even on a low-end server (mine is an Athlon 2500+).

 

To give you an idea, I wrote an admin for osCommerce, as the admin they give you, well, kinda sucks. It's written in Python (duh ! like I was gonna use PHP ;). Being an Admin, it is not optimized for speed, rather optimized for being easy to maintain !

 

I have a page which lists all the products on the site, with their descriptions, all attributes, there is also a clickable category tree (with category counts of course) and various forms. Right now I have 159 products, so the page weighs 577 KBytes (!) but it's generated in about 400 milliseconds. This is counting authentication and all associated rights management. I consider this Very Slow, but that's because I use a very versatile templating system (SkunkWeb) which is not that fast. I have benchmarked mod_python alone without templates and it can spit out an amazing 4 to 10 megabytes per second of generated HTML (Test was dumping a huge table from Postgres in an HTML table).

 

This is just to put things into perspective. Now enough ranting, here is what I did to speed up my osCommerce :

 

* add a query result cache in tep_db_query() :

I didn't want to modify all over the code, so I just added a static variable in tep_db_query() : array( query string => results ). Thus the same query is never executed twice in any given page generation.

 

* skin sts_template to the bone :

To my surprise, the STS Template system was generating a huge lot of queries. So I looked in the code and saw that it was doing Very Stupid Things, like querying the names of all the categories in all the languages Just In Case I Needed Them. Well I didn't. So look in the sts_template files for a huge optimization opportunity, just slash these queries, they're useless.

 

* category box :

The osC category box is a good example of shitty code. I have replaced it by one of my own which does the following :

 

SELECT * FROM categories, categories_description (with the appropriate join) ORDER BY parent_id, sort_order

 

So you get the categories in the order of parent_id, sort_order

You stash all this in an array. As it comes in order of (parent_id, sort_order) you can add a children field to each category and fill it on the fly. Guess what, at the end of the loop, you have a tree structure, just gotta display it. In One query. And the resulting code is like, just two pages, half of what it was before. Did it in Python too, just for laugh, it was like 3 times smaller and faster. Category counts can be added by joining to products and a group by, but I didn't need them.

 

* Other optimizations

 

Did you notice most of the tables created by the osCommerce installation did not have indexes ?

Postgres would laugh at it because it can invent good query plans even in this case (if the tables are small, which is the case here) ; but MySQL definitely needs help ! So go create these indexes, especially on the products_options link tables, orders (customer_id) and the like !

 

Enough. I hope you like this category box idea. You really don't need more than One query if you have less than say 50 categories. It's faster to grab 50 rows and only display 10 than to make 10 queries ;)

Share this post


Link to post
Share on other sites
First of all what is really apparent here is that the osCommerce coders are quite incompetent. You don't write a function containing database queries, like tep_get_tax_rate, then call it everywhere instead of doing a Join.

 

Also, the page generation times you post are "normal" for osCommerce, but for any other site they would be considered really, really high. If I was designing a webstore like this, on a low-end server I would target less than 5 milliseconds per page as the absolute maximum acceptable time ; and I think it would not be impossible to meet even on a low-end server (mine is an Athlon 2500+).

 

To give you an idea, I wrote an admin for osCommerce, as the admin they give you, well, kinda sucks. It's written in Python (duh ! like I was gonna use PHP ;). Being an Admin, it is not optimized for speed, rather optimized for being easy to maintain !

 

I have a page which lists all the products on the site, with their descriptions, all attributes, there is also a clickable category tree (with category counts of course) and various forms. Right now I have 159 products, so the page weighs 577 KBytes (!) but it's generated in about 400 milliseconds. This is counting authentication and all associated rights management. I consider this Very Slow, but that's because I use a very versatile templating system (SkunkWeb) which is not that fast. I have benchmarked mod_python alone without templates and it can spit out an amazing 4 to 10 megabytes per second of generated HTML (Test was dumping a huge table from Postgres in an HTML table).

 

This is just to put things into perspective. Now enough ranting, here is what I did to speed up my osCommerce :

 

* add a query result cache in tep_db_query() :

I didn't want to modify all over the code, so I just added a static variable in  tep_db_query() : array( query string => results ). Thus the same query is never executed twice in any given page generation.

 

* skin sts_template to the bone :

To my surprise, the STS Template system was generating a huge lot of queries. So I looked in the code and saw that it was doing Very Stupid Things, like querying the names of all the categories in all the languages Just In Case I Needed Them. Well I didn't. So look in the sts_template files for a huge optimization opportunity, just slash these queries, they're useless.

 

* category box :

The osC category box is a good example of shitty code. I have replaced it by one of my own which does the following :

 

SELECT * FROM categories, categories_description (with the appropriate join) ORDER BY parent_id, sort_order

 

So you get the categories in the order of parent_id, sort_order

You stash all this in an array. As it comes in order of (parent_id, sort_order) you can add a children field to each category and fill it on the fly. Guess what, at the end of the loop, you have a tree structure, just gotta display it. In One query. And the resulting code is like, just two pages, half of what it was before. Did it in Python too, just for laugh, it was like 3 times smaller and faster. Category counts can be added by joining to products and a group by, but I didn't need them.

 

* Other optimizations

 

Did you notice most of the tables created by the osCommerce installation did not have indexes ?

Postgres would laugh at it because it can invent good query plans even in this case (if the tables are small, which is the case here) ; but MySQL definitely needs help ! So go create these indexes, especially on the products_options link tables, orders (customer_id) and the like !

 

Enough. I hope you like this category box idea. You really don't need more than One query if you have less than say 50 categories. It's faster to grab 50 rows and only display 10 than to make 10 queries  ;)

 

 

happy to see that some if us have retained the capacity to make friends.


Treasurer MFC

Share this post


Link to post
Share on other sites
happy to see that some if us have retained the capacity to make friends.

 

Hehe. Sorry if I offended you ;)

It's just that I'm so... underwhelmed by the quality of the code in osCommerce. So yes, I'm ranting... But then again it's the only free and open-source project available, so I used it. And in the end the results are not that bad.

 

I just wish it was better designed.

Share this post


Link to post
Share on other sites
First of all what is really apparent here is that the osCommerce coders are quite incompetent. You don't write a function containing database queries, like tep_get_tax_rate, then call it everywhere instead of doing a Join.

That is pretty harsh...

 

As a side note: your logic is flawed and is not scalable. I presented left joins earlier in the thread since I believe most stores are small to medium sized with less than a few hundred categories / products. The reason you would want a separate function is to keep the code scalable for large stores. Since the osC database is overnormalized a left join if used improperly on a large store increases the table scan exponentially. Not a good general practice...but works well on small to medium size stores.

 

* add a query result cache in tep_db_query() :

I didn't want to modify all over the code, so I just added a static variable in  tep_db_query() : array( query string => results ). Thus the same query is never executed twice in any given page generation.

The great thing about open source software is that your input could help others. I personally would like to see how you implemented the result cache (instead of using the builtin query cache with MySQL 4+) as what you presented will store a resource and not a result set.

 

* category box :

The osC category box is a good example of shitty code. I have replaced it by one of my own which does the following :

 

SELECT * FROM categories, categories_description (with the appropriate join) ORDER BY parent_id, sort_order

 

So you get the categories in the order of parent_id, sort_order

You stash all this in an array. As it comes in order of (parent_id, sort_order) you can add a children field to each category and fill it on the fly. Guess what, at the end of the loop, you have a tree structure, just gotta display it. In One query. And the resulting code is like, just two pages, half of what it was before. Did it in Python too, just for laugh, it was like 3 times smaller and faster. Category counts can be added by joining to products and a group by, but I didn't need them.

Nice try but once again that is a HUGE and server intensive table scan. The only way I would even thing of using that is if you cached the data.

 

Bobby

Share this post


Link to post
Share on other sites
Hehe. Sorry if I offended you ;)

It's just that I'm so... underwhelmed by the quality of the code in osCommerce. So yes, I'm ranting... But then again it's the only free and open-source project available, so I used it. And in the end the results are not that bad.

 

I just wish it was better designed.

 

 

not offended at all, you may even be right. You presented it badly though.

 

Still, I am looking forward to your first "speed improvement" contribution.

 

I am running my site on a 800mhz 256MB desktop over an ADSL line so I need all the good stuff you have to offer.


Treasurer MFC

Share this post


Link to post
Share on other sites

Sorry I'll go to vacation this night ;)

Anyway I've bookmarked this forum for when I'll come back. I'll post my categorybox...

 

As a side note: your logic is flawed and is not scalable. I presented left joins earlier in the thread since I believe most stores are small to medium sized with less than a few hundred categories / products. The reason you would want a separate function is to keep the code scalable for large stores

 

Well, I'd think the contrary. A properly designed query with joins will be fast, a lot faster than a series of small queries giving the same result. If the databse layout is well done. For instance, joining products with products descriptions should be a LOT faster than a lot of small queries. No matter if you select 10 products out of 30 or out of 100.000... because you don't join the whole table, just little bits, it uses indexes... where is the problem ?

 

Of course, queries joining the entire table are another matter, and these can be catastrophic, but normally you should have none except in the admin reports generation...

 

So, yes, a well written query will be faster and more scalable than a burst of small queries, one per product.

 

Especially, with tax rates, listing the products in a category usually fetches just one or two rows in the tax rate table...

 

not offended at all, you may even be right. You presented it badly though.

 

For instance, I was thinking about the products options. Options should really be classes, which should be able to modify the products price, weight, etc. Right now, the options is a string, and the parser which parses it is copypasted in various places in the code... Ugly !

 

Since the osC database is overnormalized a left join if used improperly on a large store increases the table scan exponentially

 

Can you give an example of overnormalization ? It's true there is a lot of **_to_** tables, some of them feel a bit redundant...

 

I personally would like to see how you implemented the result cache

 

Silly me !

I implemented this six months ago and didn't remember. In fact I put the cache in tep_get_tax_rate ;) Sorry for the bull. So I just put a static array in tep_get_tax_rate that memorizes the previous parameters and returned values.

 

Now that you set me on the query result cache... mmm, yes, PHP says they are resources... No, I don't want to implement this in PHP ;)

 

The MySQL query cache would not be useful because osC updates some counters in the tables quite often (products viewed, bought...)

 

Nice try but once again that is a HUGE and server intensive table scan.

 

Huge ?

The categories table has, like, 20 rows. I don't call that huge.

It's a lot faster to grab 20 rows from a table in one query (especially if the rows are small : category id and name) and throw away 10 of them in PHP than to make 5-6 queries to get just the 10 rows you want in these 20 ! Really, on such a small table, I guess there is more overhead in parsing the query than in executing it... after all the table is, like, 1 disk page ? And joining the whole categories with categories_description (normally it should be seq scan + merge join) should take... on the order of a millisecond or less... Postgres would say 0.15 ms which is about as long as the query parsing and planning time...

 

Now if you have 800 categories or something, yes you're right !

But in that case, drop MySQL 4, and do it in a stored procedure !

Category boxes are basically tree walking. If the total rowcount is small, it's faster to grab the whole table and do it in PHP. If it's larger, a stored proc does it. I've tested this in postgres. It's about 5-20 times faster depending on the depth of the tree.

 

Counting products in categories, on the other hand, incurs a seq scan on the products table, which I'd avoid. I don't like products count anyway. If you have like 5000 products and want product counts in categories, I'd use a trigger on the prducts table to update count fields in the categories table. Or a cron update.

 

Have you played with postgres ?

When you join small tables in Postgres (like one-disk-page, 8 kbytes tables), most of the time it will not even use indexes, it will just sequentially scan the tables, sort them and join them in memory, or use a Hash Join. And then you force it to use the index and realize it's a lot slower. I think MySQL does the same.

Share this post


Link to post
Share on other sites

I've turned off item count in the categories and the articles contri I installed, but am still getting queries for them in the debug info. Should I be doing something else in addition to turning them off in the admin?

 

Here is my debug info... any tips on cutting down my queries and load time would be appreciated. I use STS so I can't really use the page cache, but any other tips would be great.

 

My Debug:

 
? ? 
? ? ? ? ? ?[0] => select configuration_key as cfgKey, configuration_value as cfgValue from configuration
? ? ? ? ? ?[1] => select value from sessions where sesskey = '7a328e63ee4c62fef08e6644a4a544bc' and expiry > '1102993913'
? ? ? ? ? ?[2] => select code, title, symbol_left, symbol_right, decimal_point, thousands_point, decimal_places, value from currencies
? ? ? ? ? ?[3] => delete from whos_online where time_last_click < '1102993013'
? ? ? ? ? ?[4] => select count(*) as count from whos_online where session_id = '7a328e63ee4c62fef08e6644a4a544bc'
? ? ? ? ? ?[5] => update whos_online set customer_id = '0', full_name = 'Guest', ip_address = '66.177.39.55', time_last_click = '1102993913', last_page_url = '/index.php' where session_id = '7a328e63ee4c62fef08e6644a4a544bc'
? ? ? ? ? ?[6] => select specials_id from specials where status = '1' and now() >= expires_date and expires_date > 0
? ? ? ? ? ?[7] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.parent_id = '0' and c.categories_id = cd.categories_id and cd.language_id='1' order by sort_order, cd.categories_name
? ? ? ? ? ?[8] => select count(*) as count from categories where parent_id = '23'
? ? ? ? ? ?[9] => select count(*) as count from categories where parent_id = '24'
? ? ? ? ? ?[10] => select count(*) as count from categories where parent_id = '21'
? ? ? ? ? ?[11] => select count(*) as count from categories where parent_id = '22'
? ? ? ? ? ?[12] => select count(*) as count from categories where parent_id = '26'
? ? ? ? ? ?[13] => select count(*) as count from categories where parent_id = '27'
? ? ? ? ? ?[14] => select authors_id, authors_name from authors order by authors_name
? ? ? ? ? ?[15] => select t.topics_id, td.topics_name, t.parent_id from topics t, topics_description td where t.parent_id = '0' and t.topics_id = td.topics_id and td.language_id = '1' order by sort_order, td.topics_name
? ? ? ? ? ?[16] => select count(*) as count from topics where parent_id = '1'
? ? ? ? ? ?[17] => select count(*) as count from topics where parent_id = '2'
? ? ? ? ? ?[18] => select count(*) as count from topics where parent_id = '4'
? ? ? ? ? ?[19] => select count(*) as count from topics where parent_id = '3'
? ? ? ? ? ?[20] => select products_id, products_price, products_tax_class_id, products_weight from products where products_id = '30'
? ? ? ? ? ?[21] => select sum(tax_rate) as tax_rate from tax_rates tr left join zones_to_geo_zones za on (tr.tax_zone_id = za.geo_zone_id) left join geo_zones tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '223') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '18') and tr.tax_class_id = '1' group by tr.tax_priority
? ? ? ? ? ?[22] => select specials_new_products_price from specials where products_id = '30' and status = '1'
? ? ? ? ? ?[23] => select options_values_price, price_prefix from products_attributes where products_id = '30' and options_id = '2' and options_values_id = '2'
? ? ? ? ? ?[24] => select manufacturers_id, manufacturers_name from manufacturers order by manufacturers_name
? ? ? ? ? ?[25] => select products_id, products_image, products_tax_class_id, products_price from products where products_status = '1' order by products_date_added desc limit 10
? ? ? ? ? ?[26] => select products_name from products_description where products_id = '41' and language_id = '1'
? ? ? ? ? ?[27] => select specials_new_products_price from specials where products_id = '41' and status
? ? ? ? ? ?[28] => select p.products_id, pd.products_name, p.products_model, p.products_image, p.products_price, p.products_weight, p.products_tax_class_id from products p, products_description pd where p.products_id = '30' and pd.products_id = p.products_id and pd.language_id = '1'
? ? ? ? ? ?[29] => select specials_new_products_price from specials where products_id = '30' and status = '1'
? ? ? ? ? ?[30] => select options_values_price, price_prefix from products_attributes where products_id = '30' and options_id = '2' and options_values_id = '2'
? ? ? ? ? ?[31] => select products_id, products_price, products_tax_class_id, products_weight from products where products_id = '30'
? ? ? ? ? ?[32] => select specials_new_products_price from specials where products_id = '30' and status = '1'
? ? ? ? ? ?[33] => select options_values_price, price_prefix from products_attributes where products_id = '30' and options_id = '2' and options_values_id = '2'
? ? ? ? ? ?[34] => select distinct p.products_id, pd.products_name from products p, products_description pd where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '1' order by p.products_ordered desc, pd.products_name limit 10
? ? ? ? ? ?[35] => select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from products p, products_description pd, specials s where p.products_status = '1' and p.products_id = s.products_id and pd.products_id = s.products_id and pd.language_id = '1' and s.status = '1' order by s.specials_date_added desc limit 10
? ? ? ? ? ?[36] => select languages_id, name, code, image, directory from languages order by sort_order
? ? ? ? ? ?[37] => select c.categories_id, cd.categories_name, c.categories_image
? ? ? ? ? ? ? ? ? ? ? ? from categories c, categories_description cd
? ? ? ? ? ? ? ? ? ? ? ?where c.categories_id = cd.categories_id
? ? ? ? ? ? ? ? ? ? ? ? ?and cd.language_id ='1'
? ? ? ? ? ? ? ? ? ? ? ? ?and c.parent_id = '0'
? ? ? ? ? ? ? ? ? ? ? ?order by c.sort_order, cd.categories_name
? ? ? ? ? ?[38] => select c.categories_id, cd.categories_name, c.parent_id
? ? ? ? ? ? ? ? ? ? ? from categories c, categories_description cd
? ? ? ? ? ? ? ? ? ? ?where c.parent_id = cd.categories_id
? ? ? ? ? ? ? ? ? ? ? ?and cd.language_id ='1'
? ? ? ? ? ? ? ? ? ? ? ?and c.categories_id = '0'
? ? ? ? ? ?[39] => select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from products p left join specials s on p.products_id = s.products_id where products_status = '1' order by p.products_date_added desc limit 3
? ? ? ? ? ?[40] => select products_name from products_description where products_id = '43' and language_id = '1'
? ? ? ? ? ?[41] => select products_name from products_description where products_id = '42' and language_id = '1'
? ? ? ? ? ?[42] => select products_name from products_description where products_id = '41' and language_id = '1'
? ? ? ? ? ?[43] => select p.products_id, pd.products_name, products_date_available as date_expected from products p, products_description pd where to_days(products_date_available) >= to_days(now()) and p.products_id = pd.products_id and pd.language_id = '1' order by date_expected desc limit 10
? ? ? ? ? ?[44] => select r.reviews_id, r.reviews_rating, p.products_id, p.products_image, pd.products_name from reviews r, reviews_description rd, products p, 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 = '1' and p.products_id = pd.products_id and pd.language_id = '1' order by r.reviews_id desc limit 10
? ? ? ? ? ?[45] => select startdate, counter from counter
? ? ? ? ? ?[46] => update counter set counter = '2786'
? ? ? ? ? ?[47] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '0' order by c.sort_order, cd.categories_name
? ? ? ? ? ?[48] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '23' order by c.sort_order, cd.categories_name
? ? ? ? ? ?[49] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '24' order by c.sort_order, cd.categories_name
? ? ? ? ? ?[50] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '21' order by c.sort_order, cd.categories_name
? ? ? ? ? ?[51] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '22' order by c.sort_order, cd.categories_name
? ? ? ? ? ?[52] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '26' order by c.sort_order, cd.categories_name
? ? ? ? ? ?[53] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '27' order by c.sort_order, cd.categories_name
? ? ? ? ? ?[54] => SELECT categories_id, categories_name FROM categories_description
? ? ? ? ? ?[55] => select count(*) as total from sessions where sesskey = '7a328e63ee4c62fef08e6644a4a544bc'
? ? ? ? ? ?[56] => update sessions set expiry = '1102995353', value = 'cart|O:12:\"shoppingcart\":5:{s:8:\"contents\";a:1:{s:6:\"30{2}2\";a:2:{s:3:\"qty\";i:1;s:10:\"attributes\";a:1:{i:2;s:1:\"2\";}}}s:5:\"total\";d:2.5;s:6:\"weight\";d:0;s:6:\"cartID\";s:5:\"00194\";s:12:\"content_type\";b:0;}language|s:7:\"english\";languages_id|s:1:\"1\";currency|s:3:\"USD\";navigation|O:17:\"navigationhistory\":2:{s:4:\"path\";a:1:{i:0;a:4:{s:4:\"page\";s:9:\"index.php\";s:4:\"mode\";s:6:\"NONSSL\";s:3:\"get\";a:0:{}s:4:\"post\";a:0:{}}}s:8:\"snapshot\";a:4:{s:4:\"page\";s:21:\"checkout_shipping.php\";s:4:\"mode\";s:6:\"NONSSL\";s:3:\"get\";a:0:{}s:4:\"post\";a:0:{}}}' where sesskey = '7a328e63ee4c62fef08e6644a4a544bc'
? ? ? ?


? ? ? ?
? ? ? ? ? ?[0] => 0.002957
? ? ? ? ? ?[1] => 0.001516
? ? ? ? ? ?[2] => 0.000785
? ? ? ? ? ?[3] => 0.000645
? ? ? ? ? ?[4] => 0.000330
? ? ? ? ? ?[5] => 0.000347
? ? ? ? ? ?[6] => 0.000766
? ? ? ? ? ?[7] => 0.001793
? ? ? ? ? ?[8] => 0.000411
? ? ? ? ? ?[9] => 0.000477
? ? ? ? ? ?[10] => 0.000258
? ? ? ? ? ?[11] => 0.000532
? ? ? ? ? ?[12] => 0.000532
? ? ? ? ? ?[13] => 0.000601
? ? ? ? ? ?[14] => 0.000580
? ? ? ? ? ?[15] => 0.001113
? ? ? ? ? ?[16] => 0.000483
? ? ? ? ? ?[17] => 0.000213
? ? ? ? ? ?[18] => 0.000208
? ? ? ? ? ?[19] => 0.000206
? ? ? ? ? ?[20] => 0.000586
? ? ? ? ? ?[21] => 0.000841
? ? ? ? ? ?[22] => 0.000198
? ? ? ? ? ?[23] => 0.000426
? ? ? ? ? ?[24] => 0.000551
? ? ? ? ? ?[25] => 0.000536
? ? ? ? ? ?[26] => 0.000432
? ? ? ? ? ?[27] => 0.000185
? ? ? ? ? ?[28] => 0.000569
? ? ? ? ? ?[29] => 0.000283
? ? ? ? ? ?[30] => 0.000322
? ? ? ? ? ?[31] => 0.000481
? ? ? ? ? ?[32] => 0.000259
? ? ? ? ? ?[33] => 0.000373
? ? ? ? ? ?[34] => 0.001673
? ? ? ? ? ?[35] => 0.000572
? ? ? ? ? ?[36] => 0.000830
? ? ? ? ? ?[37] => 0.001055
? ? ? ? ? ?[38] => 0.000894
? ? ? ? ? ?[39] => 0.000645
? ? ? ? ? ?[40] => 0.000487
? ? ? ? ? ?[41] => 0.000433
? ? ? ? ? ?[42] => 0.000680
? ? ? ? ? ?[43] => 0.000833
? ? ? ? ? ?[44] => 0.001212
? ? ? ? ? ?[45] => 0.000728
? ? ? ? ? ?[46] => 0.000342
? ? ? ? ? ?[47] => 0.000869
? ? ? ? ? ?[48] => 0.000589
? ? ? ? ? ?[49] => 0.000641
? ? ? ? ? ?[50] => 0.000544
? ? ? ? ? ?[51] => 0.000494
? ? ? ? ? ?[52] => 0.000535
? ? ? ? ? ?[53] => 0.000514
? ? ? ? ? ?[54] => 0.000625
? ? ? ? ? ?[55] => 0.001218
? ? ? ? ? ?[56] => 0.000642
? ? ? ?

 

Thanks again, really feel this is the most important subject to a store.

 

I would love to remove the counter, the language (if it helps a lot) since I don't use anything but english, who's online (do I need this?), anything really to help. I have browse categories on the main page so that pulls a bit, but it already does it else where so maybe I can cut this as well.

Share this post


Link to post
Share on other sites

Chemo and PeuFeu...you guys have some great suggestions. I already did a lot of what Chemo mentioned on my own before I saw this post and he's definately on the right track without getting too into the code so others can follow. My pages are typically too dynamic for the page cache to be effective so without I'd have to say my page parse time are still subpar. And looking forward to PeuFeu's category code.

 

Optimization Performed So Far:

 

1) Added indexes wherever they are needed.

2) Commented out banner expiring/activation function requests to be ran via CRON every 30 mins or so.

3) Cache in session or hardcore repetitively queried data such as tax rate, customer discount (from a badly coded contribution), etc.

4) Modified a couple queries into straight joins where needed.

5) Removed any unneccessary queries that can be replaced by joins (especially in the new product boxes).

6) Modified the code for products_viewed to update/insert on a separate table so as to not reset any mysql cached queries.

7) Commented out the language class and hardcoded language since my store has no intention of being multi-lingual.

8) Added sql_no_cache to all queries that are frequently flushed (don't know if it provides any benefit like helping reduce some mysql server overhead...but I don't think it hurts).

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.

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.

 

TO DO

 

A) Remove rarely used function and class requests from application_top and include only when needed (i.e. email, validations, password_func, etc.)

B) Try combining all left and column box code into 2 php files (if #11 actually does work on a production server and not just my Win XP fluke <_< )

 

Results

 

Pretty much I eliminated all unnecessary and redundant queries in OSC (only breadcrumb handling isn't perfect).

 

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.

 

Mod #10 is pretty crazy and its not forever especially on a shared server setup or people that don't have their mysql_query_cache set up properly or if you have thousands of products per page (its still pretty fast though...just a huge array being tossed around).

 

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.

 

Remaining Problem

 

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.

 

So any suggestions? Is querying all rows of products (~200-1000) in a given category just too much at one time? Is having an array in memory that big going to cause problems with php or memory allocation? Is the script using too much memory per session so I'm limiting how many simultaneous users?

 

It looks great when query cached :D and acceptable uncached...but I'm only a newbie at this mysql/php thing.


I ♥ PHP/MYSQL/CSS

Share this post


Link to post
Share on other sites

first of all thank you to all of you specially chemo. Amazing work, this is an amazing contribution.

I am trying to put together my store and im hoping to go live by mid january 2005 and since i dont use taxes and or multilanguages i was wondering if i could reduce some queries by eliminiting those that relate to taxes and languages... im not a programmer, developer or anything alike so if anyone could give me a place to read and learn or a hint that puts me on the right track i would highly appreciate it.

 

regarding the languages, is it safe for me to delete english and german languages from the admin? i tried it on a previous instalation and it kind of messed everything around...

 

thanks in advance,

O.

 

(where can i learn about the join function?)


Cheers,

O.

Share this post


Link to post
Share on other sites

i have found a problem and i cant really find what could have caused it.

i have installed :

 

queries_debug_v1.6

Optimize_tep_get_tax_ratev1.1_1

configuration_cache_1_2

 

here's my My Webpage if anyone would like to take a look. the only language im trying to display is spanish.


Cheers,

O.

Share this post


Link to post
Share on other sites
i have found a problem and i cant really find what could have caused it.

i have installed :

 

queries_debug_v1.6

Optimize_tep_get_tax_ratev1.1_1

configuration_cache_1_2

 

here's my My Webpage if anyone would like to take a look. the only language im trying to display is spanish.

 

what problem are you refering to ?


Treasurer MFC

Share this post


Link to post
Share on other sites
i have found a problem and i cant really find what could have caused it.

i have installed :

 

queries_debug_v1.6

Optimize_tep_get_tax_ratev1.1_1

configuration_cache_1_2

 

here's my My Webpage if anyone would like to take a look. the only language im trying to display is spanish.

The queries debug is functional. In the admin settings turn off the display page parse time setting and the output will not be sent to screen. Then you can call it with REQUEST like ?output=1

 

The tep_get_tax_rate() mod is also functional. I noticed on the output it is called only once and is a sure sign of correct functionality.

 

The configuration cache is also functional since the query is not being executed. This tells me the file exists and is being read properly.

 

What is your problem so we can replicate it...

 

Bobby

Share this post


Link to post
Share on other sites
The queries debug is functional.  In the admin settings turn off the display page parse time setting and the output will not be sent to screen.  Then you can call it with REQUEST like ?output=1

 

The tep_get_tax_rate() mod is also functional.  I noticed on the output it is called only once and is a sure sign of correct functionality.

 

The configuration cache is also functional since the query is not being executed.  This tells me the file exists and is being read properly.

 

What is your problem so we can replicate it...

 

Bobby

 

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.


Cheers,

O.

Share this post


Link to post
Share on other sites

×