Any ways to Improve Speed on my site further?

[Stealth1]

I have done alot of reading and implementing contributions and code to optimize my site for performance and speed and it seems to work well so far I think. I currently use FWR's KissER Error Handling and Debugger which is a great contribution and very easy to install.

 

So I will start with the index.php page, here are the current stats.

 

Fresh Page Load (ctrl + F5):

Total Queries: 12

Slowest Query Number 9

Time: 0.0013 Seconds.

Total query Time 0.0079 Seconds

 

On Refreshes it ranges from: 0.0056-0.009 Seconds

 

Here is the Query Output

Number: 1
Time: 0.001 Seconds.
Query: select configuration_key as cfgKey, configuration_value as cfgValue from configuration
Number: 2
Time: 0.0005 Seconds.
Query: select value from sessions where sesskey = 'be049e13cd26d14ae6f9a1c7109908a6' and expiry > '1268796155'
Number: 3
Time: 0.0007 Seconds.
Query: select c.categories_id, c.parent_id from categories c order by c.parent_id
Number: 4
Time: 0.0003 Seconds.
Query: select code, title, symbol_left, symbol_right, decimal_point, thousands_point, decimal_places, value from currencies
Number: 5
Time: 0.0004 Seconds.
Query: select specials_id from specials where status = '1' and now() >= expires_date and expires_date > 0
Number: 6
Time: 0.0009 Seconds.
Query: select p.products_id, p.products_image, p.products_tax_class_id, pd.products_name, 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, products_description pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' order by p.products_date_added desc limit 6
Number: 7
Time: 0.0009 Seconds.
Query: 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 = '38') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '74') and tr.tax_class_id = '1' group by tr.tax_priority
Number: 8
Time: 0.001 Seconds.
Query: 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
Number: 9
Time: 0.0011 Seconds.
Query: 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
Number: 10
Time: 0.0005 Seconds.
Query: 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' and r.approved = '1' order by r.reviews_id desc limit 10
Number: 11
Time: 0.0002 Seconds.
Query: select count(*) as total from sessions where sesskey = 'be049e13cd26d14ae6f9a1c7109908a6'
Number: 12
Time: 0.0002 Seconds.
Query: update sessions set expiry = '1268797595', value = 'cart|O:12:\"shoppingCart\":5:{s:8:\"contents\";a:0:{}s:5:\"total\";i:0;s:6:\"weight\";i:0;s:6:\"cartID\";N;s:12:\"content_type\";b:0;}language|s:7:\"english\";languages_id|s:1:\"1\";currency|s:3:\"CAD\";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:1:{s:11:\"tougeoutput\";s:0:\"\";}s:4:\"post\";a:0:{}}}s:8:\"snapshot\";a:0:{}}' where sesskey = 'be049e13cd26d14ae6f9a1c7109908a6'

[Stealth1]

Any input?

 

I saw some code from Chemo about removing "white space", is this beneficial still?

[♥kymation]

The query times look quite good. None of them stand out as being slow, and there are only 12. I would say you've done that part right.

 

Getting rid of whitespace will give you a noticeable boost in the HTML speed since it cuts down the number of characters sent. It may not be worth doing, since HTML accounts for such a small portion of the time. It will probably help more to turn on compression, unless you already have.

 

Images are a major part of your download overhead, so make sure that all of your images are the size being shown (no browser resizing) and that they are optimized.

 

PHP code can also be optimized, but the result is likely to be tiny. Probably not worth it unless you have some bulky code in a loop that iterates 10,000 times.

 

Regards

Jim

[Stealth1]

Thanks Jim, I figure I have gone this far that if running getting rid of the white space might help even a bit then it's worth it as long as it won't pose any issues. Alot of the tricks are years old though so im concerned they may be obsolete or there may be a better method.

 

Right now I have Gzip Turned on and set to level 1.

 

So here is what I get with viewing a first level category.

 

Total Queries: 13
Slowest Query Number 11
Time: 0.0183 Seconds.
Total query Time 0.0529 Seconds.
Number: 1
Time: 0.0111 Seconds.
Query: select configuration_key as cfgKey, configuration_value as cfgValue from configuration
Number: 2
Time: 0.0004 Seconds.
Query: select value from sessions where sesskey = '0f4e13505d66d957eda1aac7190576ed' and expiry > '1269149813'
Number: 3
Time: 0.0006 Seconds.
Query: select c.categories_id, c.parent_id from categories c order by c.parent_id
Number: 4
Time: 0.0002 Seconds.
Query: select code, title, symbol_left, symbol_right, decimal_point, thousands_point, decimal_places, value from currencies
Number: 5
Time: 0.0005 Seconds.
Query: select specials_id from specials where status = '1' and now() >= expires_date and expires_date > 0
Number: 6
Time: 0.0003 Seconds.
Query: select categories_name from categories_description where categories_id = '80' and language_id = '1'
Number: 7
Time: 0.0002 Seconds.
Query: select cd.categories_name, c.categories_image from categories c, categories_description cd where c.categories_id = '80' and cd.categories_id = '80' and cd.language_id = '1'
Number: 8
Time: 0.0003 Seconds.
Query: select c.categories_id, cd.categories_name, c.categories_image, c.parent_id from categories c, categories_description cd where c.parent_id = '80' and c.categories_id = cd.categories_id and cd.language_id = '1' order by sort_order, cd.categories_name
Number: 9
Time: 0.0123 Seconds.
Query: select distinct p.products_id, p.products_image, p.products_tax_class_id, pd.products_name, 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, products_description pd, products_to_categories p2c, categories c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '80' and p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' order by p.products_date_added desc limit 6
Number: 10
Time: 0.0083 Seconds.
Query: select distinct p.products_id, pd.products_name from products p, products_description pd, products_to_categories p2c, categories c where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and '80' in (c.categories_id, c.parent_id) order by p.products_ordered desc, pd.products_name limit 10
Number: 11
Time: 0.0183 Seconds.
Query: 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' and r.approved = '1' order by r.reviews_id desc limit 10
Number: 12
Time: 0.0001 Seconds.
Query: select count(*) as total from sessions where sesskey = '0f4e13505d66d957eda1aac7190576ed'
Number: 13
Time: 0.0003 Seconds.
Query: update sessions set expiry = '1269151253', value = 'cart|O:12:\"shoppingCart\":5:{s:8:\"contents\";a:0:{}s:5:\"total\";i:0;s:6:\"weight\";i:0;s:6:\"cartID\";N;s:12:\"content_type\";b:0;}language|s:7:\"english\";languages_id|s:1:\"1\";currency|s:3:\"CAD\";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:2:{s:5:\"cPath\";s:2:\"80\";s:11:\"tougeoutput\";s:0:\"\";}s:4:\"post\";a:0:{}}}s:8:\"snapshot\";a:0:{}}' where sesskey = '0f4e13505d66d957eda1aac7190576ed'

 

Subsequent refreshes of the page were around 0.022-0.025 Seconds

[♥kymation]

I wouldn't be concerned about that unless it was several times larger. Still, if you want to optimize further, go ahead. Check that you have all of the latest fixes to the database, as several indexes were added to speed things up. Optimize your tables, and set that to happen often enough to keep them up to date. I usually optimize weekly, but a site that constantly adds products might need to do that daily.

 

I still think that your results look very good.

 

Regards

Jim

[Stealth1]

I wouldn't be concerned about that unless it was several times larger. Still, if you want to optimize further, go ahead. Check that you have all of the latest fixes to the database, as several indexes were added to speed things up. Optimize your tables, and set that to happen often enough to keep them up to date. I usually optimize weekly, but a site that constantly adds products might need to do that daily.

 

I still think that your results look very good.

 

Regards

Jim

 

Jim,

 

thanks for the reply, I'm just going through the different pages rather than post each one up at once to get answers.

 

I am a noob when it comes to code, I can follow instructions and fix minor things but beyond that I am useless. Where would I look for the fixes to the database? What would be required to optimize my tables and where would I find this info?

 

Here is the info from a product info page, first viewing.

 

Total Queries: 30
Slowest Query Number 1
Time: 0.0009 Seconds.
Total query Time 0.0085 Seconds.
Number: 1
Time: 0.0009 Seconds.
Query: select configuration_key as cfgKey, configuration_value as cfgValue from configuration
Number: 2
Time: 0.0004 Seconds.
Query: select value from sessions where sesskey = '0f4e13505d66d957eda1aac7190576ed' and expiry > '1269186443'
Number: 3
Time: 0.0006 Seconds.
Query: select c.categories_id, c.parent_id from categories c order by c.parent_id
Number: 4
Time: 0.0003 Seconds.
Query: select code, title, symbol_left, symbol_right, decimal_point, thousands_point, decimal_places, value from currencies
Number: 5
Time: 0.0004 Seconds.
Query: select specials_id from specials where status = '1' and now() >= expires_date and expires_date > 0
Number: 6
Time: 0.0005 Seconds.
Query: select p2c.categories_id from products p, products_to_categories p2c where p.products_id = '527' and p.products_status = '1' and p.products_id = p2c.products_id limit 1
Number: 7
Time: 0.0001 Seconds.
Query: select parent_id from categories where categories_id = '198'
Number: 8
Time: 0.0001 Seconds.
Query: select parent_id from categories where categories_id = '103'
Number: 9
Time: 0.0001 Seconds.
Query: select parent_id from categories where categories_id = '83'
Number: 10
Time: 0.0001 Seconds.
Query: select parent_id from categories where categories_id = '80'
Number: 11
Time: 0.0002 Seconds.
Query: select categories_name from categories_description where categories_id = '80' and language_id = '1'
Number: 12
Time: 0.0001 Seconds.
Query: select categories_name from categories_description where categories_id = '83' and language_id = '1'
Number: 13
Time: 0.0001 Seconds.
Query: select categories_name from categories_description where categories_id = '103' and language_id = '1'
Number: 14
Time: 0.0001 Seconds.
Query: select categories_name from categories_description where categories_id = '198' and language_id = '1'
Number: 15
Time: 0.0002 Seconds.
Query: select products_name from products_description where products_id = '527' AND language_id=1
Number: 16
Time: 0.0002 Seconds.
Query: select count(*) as total from products p, products_description pd where p.products_status = '1' and p.products_id = '527' and pd.products_id = p.products_id and pd.language_id = '1'
Number: 17
Time: 0.0003 Seconds.
Query: select p.products_id, pd.products_name, pd.products_description, p.products_model, p.products_quantity, p.products_image, p.products_image_med, p.products_image_pop, p.products_image_description, pd.products_url, p.products_price, p.products_tax_class_id, p.products_date_added, p.products_date_available, p.manufacturers_id from products p, products_description pd where p.products_status = '1' and p.products_id = '527' and pd.products_id = p.products_id and pd.language_id = '1'
Number: 18
Time: 0.0002 Seconds.
Query: update products_description set products_viewed = products_viewed+1 where products_id = '527' and language_id = '1'
Number: 19
Time: 0.0001 Seconds.
Query: select specials_new_products_price from specials where products_id = '527' and status
Number: 20
Time: 0.0008 Seconds.
Query: 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 = '38') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '74') and tr.tax_class_id = '1' group by tr.tax_priority
Number: 21
Time: 0.0002 Seconds.
Query: SELECT products_id, products_image, products_image_med, products_image_pop, products_image_description FROM products WHERE products_id = '527'
Number: 22
Time: 0.0003 Seconds.
Query: select additional_images_id, images_description, thumb_images, medium_images, popup_images from additional_images where products_id = '527'
Number: 23
Time: 0.0004 Seconds.
Query: select count(*) as total from products_options popt, products_attributes patrib where patrib.products_id='527' and patrib.options_id = popt.products_options_id and popt.language_id = '1'
Number: 24
Time: 0.0002 Seconds.
Query: select count(*) as count from reviews where approved = 1 and products_id = '527'
Number: 25
Time: 0.0001 Seconds.
Query: select products_quantity from products where products_id = '527'
Number: 26
Time: 0.0006 Seconds.
Query: select m.manufacturers_id, m.manufacturers_name, m.manufacturers_image, mi.manufacturers_url from manufacturers m left join manufacturers_info mi on (m.manufacturers_id = mi.manufacturers_id and mi.languages_id = '1'), products p where p.products_id = '527' and p.manufacturers_id = m.manufacturers_id
Number: 27
Time: 0.0002 Seconds.
Query: select products_name from products_description where products_id = '527' and language_id = '1'
Number: 28
Time: 0.0004 Seconds.
Query: 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' and r.approved = '1' and p.products_id = '527' order by r.reviews_id desc limit 10
Number: 29
Time: 0.0001 Seconds.
Query: select count(*) as total from sessions where sesskey = '0f4e13505d66d957eda1aac7190576ed'
Number: 30
Time: 0.0002 Seconds.
Query: update sessions set expiry = '1269187883', value = 'cart|O:12:\"shoppingCart\":5:{s:8:\"contents\";a:0:{}s:5:\"total\";i:0;s:6:\"weight\";i:0;s:6:\"cartID\";N;s:12:\"content_type\";b:0;}language|s:7:\"english\";languages_id|s:1:\"1\";currency|s:3:\"CAD\";navigation|O:17:\"navigationHistory\":2:{s:4:\"path\";a:1:{i:0;a:4:{s:4:\"page\";s:16:\"product_info.php\";s:4:\"mode\";s:6:\"NONSSL\";s:3:\"get\";a:2:{s:11:\"products_id\";s:3:\"527\";s:11:\"tougeoutput\";s:0:\"\";}s:4:\"post\";a:0:{}}}s:8:\"snapshot\";a:0:{}}' where sesskey = '0f4e13505d66d957eda1aac7190576ed'

 

Subsequent Refreshes range from 0.0059-0.0071 Seconds

[♥kymation]

You have the database updates if you're running osCommerce 2.2RC2a. If not, upgrade.

 

Optimizing the database tables can be done manually using your host's database admin, or automatically through your host's control panel. If in doubt, ask your host how to do this.

 

That page also looks very good. You shouldn't worry about optimizing the database queries until you add a bunch of Addons that add more queries. The stock queries are pretty good as is.

 

Regards

Jim

[Stealth1]

I currently have I think 20 or so contributions added atleast but i've been selective based on feedback from members on how well something worked. I think using the stuff from FWR is helping since his add-ons do alot with very little resource