A Store Speed Optimization in Progress

[Jan Zonjee]

Thanks! I did what you suggested and it narrowed down the problem. It appears to be in the product_listing.php file (in /includes/modules/). I guess that's why it only occurs on category and manuf. pages, not main index or product pages, as those don't have product listings.

 

I tried the code in various spots in that file and found that on line 78 is where it starts to get bad (so I guess that's the code right above it which is listing split numbers of rows, etc... And depending if I put it on line 78 or below I get anywhere from 20 to 50 rows of this: [20] => time till product_listings is included

 

Does this mean anything to anyone?

If you put it on line 78 you put it inside a loop, whereas the starting point stays the same. So it doesn't mean there is 4, 5, 6, 7 seconds between earch passing (say from 22-40) but it seems it takes around 8 seconds to build the product listing after the query. Since we don't know what your product listing looks like there there is not much we can say then that something in there takes a long time (something with a picture?).

[outlawtraders]

Tricky. I'm not familiar with that contribution but probably you are going to need the category name. I will post the adapted code needed for Monika's version of the categories box so you can see what changes were made (I mostly commented out the original code).

 

 

Hi Jan,

 

 

Many thanks - I did try to merge the changes with my modified categories php but I wasn't too successful with that. So i accepted my limitations and just used your adapted code instead.

 

I am now down to 27 queries.

 

Many thanks for your advice and help.

Paula

[jbasszd]

hello so i fallowed all the things on here but i'm still getting alot of queries

Current Parse Time: 0.575 s with 47 queries

I can see there is a lot coming from newsdesk... anyone know some fixes for this? i have STS which seems to make some difference, (with doing the sujested commenting out of the STS file)

 

Any sujestions?

 

QUERY DEBUG: 
Array
(
[QUERIES] => Array
	(
		[0] => select value from sessions where sesskey = 'dmnstq2rtair7om28cjs4a9b42' and expiry > '1198922679'
		[1] => select c.categories_id, c.parent_id from categories c order by c.parent_id
		[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 < '1198921779'
		[4] => select count(*) as count from whos_online where session_id = 'dmnstq2rtair7om28cjs4a9b42'
		[5] => update whos_online set customer_id = '0', full_name = 'Guest', ip_address = '127.0.0.1', time_last_click = '1198922679', last_page_url = '/Corbens_Products/catalog/index.php?osCsid=dmnstq2rtair7om28cjs4a9b42' where session_id = 'dmnstq2rtair7om28cjs4a9b42'
		[6] => select banners_id, date_scheduled from banners where date_scheduled != ''
		[7] => select b.banners_id, b.expires_date, b.expires_impressions, sum(bh.banners_shown) as banners_shown from banners b, banners_history bh where b.status = '1' and b.banners_id = bh.banners_id group by b.banners_id
		[8] => select specials_id from specials where status = '1' and now() >= expires_date and expires_date > 0
		[9] => select configuration_key as cfgKey, configuration_value as cfgValue from configuration where configuration_group_id='333' or configuration_group_id='4'
		[10] => select * from supertracker where browser_string ='Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 2.0.50727; .NET CLR 1.1.4322)' and ip_address like '127.0%' and last_click > '2007-12-29 01:34:39'
		[11] => UPDATE supertracker set last_click='2007-12-29 02:04:39', exit_page='/Corbens_Products/catalog/index.php', num_clicks='8', added_cart='false', categories_viewed='b:0;', products_viewed='', customer_id='0', completed_purchase='false', cart_contents='', cart_total = '0', order_id = '0' where tracking_id='18'
		[12] => select information_title, information_description from information where language_id = '1' and information_group_id = '2'
		[13] => 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" order by sort_order, cd.categories_name
		[14] => select manufacturers_id, manufacturers_name from manufacturers order by manufacturers_name
		[15] => 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
		[16] => select products_name from products_description where products_id = '64' and language_id = '1'
		[17] => select specials_new_products_price from specials where products_id = '64' and status
		[18] => 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 = '1') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '22') and tr.tax_class_id = '0' group by tr.tax_priority
		[19] => SELECT information_id, information_title, parent_id FROM information WHERE visible='1' and language_id='1' and information_group_id = '1' ORDER BY sort_order
		[20] => 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
		[21] => select configuration_key as cfgKey, configuration_value as cfgValue from newsdesk_configuration
		[22] => select configuration_key as cfgKey, configuration_value as cfgValue from newsdesk_configuration
		[23] => select p.newsdesk_id, pd.language_id, pd.newsdesk_article_name, pd.newsdesk_article_description, pd.newsdesk_article_shorttext, pd.newsdesk_article_url, pd.newsdesk_article_url_name, 
p.newsdesk_image, p.newsdesk_date_added, p.newsdesk_last_modified, 
p.newsdesk_date_available, p.newsdesk_status  from newsdesk p, newsdesk_description 
pd WHERE pd.newsdesk_id = p.newsdesk_id and pd.language_id = "1" and newsdesk_status = 1 ORDER BY newsdesk_date_added DESC LIMIT 5
		[24] => select configuration_key as cfgKey, configuration_value as cfgValue from newsdesk_configuration
		[25] => select c.categories_id, cd.categories_name, c.parent_id from newsdesk_categories c, newsdesk_categories_description cd where c.catagory_status = '1' and c.parent_id = '' 
and c.categories_id = cd.categories_id and cd.language_id='1' order by sort_order, cd.categories_name
		[26] => select c.categories_id, cd.categories_name, c.parent_id from newsdesk_categories c, newsdesk_categories_description cd 
where c.catagory_status = '1' and c.parent_id = '0' and c.categories_id = cd.categories_id and cd.language_id='1' order by sort_order, cd.categories_name
		[27] => select count(*) as count from newsdesk_categories where parent_id = '10'
		[28] => select count(*) as count from newsdesk_categories where parent_id = '1'
		[29] => select count(*) as count from newsdesk_categories where parent_id = '2'
		[30] => select count(*) as count from newsdesk_categories where parent_id = '9'
		[31] => select count(*) as count from newsdesk_categories where parent_id = '4'
		[32] => select count(*) as count from newsdesk_categories where parent_id = '5'
		[33] => 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
		[34] => 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 = '1') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '22') and tr.tax_class_id = '1' group by tr.tax_priority
		[35] => select languages_id, name, code, image, directory from languages order by sort_order
		[36] => 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
		[37] => select substring(reviews_text, 1, 60) as reviews_text from reviews_description where reviews_id = '1' and languages_id = '1'
		[38] => select configuration_key as cfgKey, configuration_value as cfgValue from newsdesk_configuration
		[39] => select p.newsdesk_id, pd.language_id, pd.newsdesk_article_name, pd.newsdesk_article_description, pd.newsdesk_article_shorttext, pd.newsdesk_article_url, pd.newsdesk_article_url_name, 
p.newsdesk_image, p.newsdesk_image_two, p.newsdesk_image_three, p.newsdesk_date_added, p.newsdesk_last_modified, pd.newsdesk_article_viewed, 
p.newsdesk_date_available, p.newsdesk_status  from newsdesk p, newsdesk_description 
pd WHERE pd.newsdesk_id = p.newsdesk_id and pd.language_id = "1" and newsdesk_status = 1 and p.newsdesk_sticky = 0 ORDER BY newsdesk_date_added DESC LIMIT 3
		[40] => 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, pd.products_name from products p left join specials s on p.products_id = s.products_id left join products_description pd on p.products_id = pd.products_id where products_status = '1' and pd.language_id = '1' order by p.products_date_added desc limit 9
		[41] => 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
		[42] => select startdate, counter from counter
		[43] => update counter set counter = '1391'
		[44] => select banners_id, banners_title, banners_image, banners_html_text from banners where status = '1' and banners_group = '468x50'
		[45] => select count(*) as total from sessions where sesskey = 'dmnstq2rtair7om28cjs4a9b42'
		[46] => update sessions set expiry = '1198924119', value = 'SESSION_USER_AGENT|s:89:\"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 2.0.50727; .NET CLR 1.1.4322)\";SESSION_IP_ADDRESS|s:9:\"127.0.0.1\";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:\"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:1:{s:6:\"osCsid\";s:26:\"dmnstq2rtair7om28cjs4a9b42\";}s:4:\"post\";a:0:{}}}s:8:\"snapshot\";a:4:{s:4:\"page\";s:11:\"account.php\";s:4:\"mode\";s:6:\"NONSSL\";s:3:\"get\";a:1:{s:6:\"osCsid\";s:26:\"dmnstq2rtair7om28cjs4a9b42\";}s:4:\"post\";a:0:{}}}' where sesskey = 'dmnstq2rtair7om28cjs4a9b42'
	)

[TIME] => Array
	(
		[0] => 0.002311
		[1] => 0.000639
		[2] => 0.000660
		[3] => 0.001105
		[4] => 0.001335
		[5] => 0.040672
		[6] => 0.001067
		[7] => 0.000552
		[8] => 0.000942
		[9] => 0.001044
		[10] => 0.001163
		[11] => 0.000972
		[12] => 0.000658
		[13] => 0.000928
		[14] => 0.000638
		[15] => 0.001191
		[16] => 0.000861
		[17] => 0.000923
		[18] => 0.000637
		[19] => 0.000673
		[20] => 0.000691
		[21] => 0.000665
		[22] => 0.000679
		[23] => 0.000648
		[24] => 0.000757
		[25] => 0.001328
		[26] => 0.000645
		[27] => 0.000619
		[28] => 0.000604
		[29] => 0.000584
		[30] => 0.000593
		[31] => 0.000569
		[32] => 0.000591
		[33] => 0.000655
		[34] => 0.000673
		[35] => 0.000635
		[36] => 0.000650
		[37] => 0.000590
		[38] => 0.000676
		[39] => 0.000626
		[40] => 0.001043
		[41] => 0.001151
		[42] => 0.000903
		[43] => 0.044184
		[44] => 0.000626
		[45] => 0.001564
		[46] => 0.073710
	)

)

[♥FWR Media]

Query 5 is taking way too long for a simple update.

 

[5] => 0.040672

 

Have you optimized and analyzed your DB recently?

 

46 is just horrible too ..

 

[46] => 0.073710

[♥Monika in Germany]

you mean, first analyzed and THEN optimized ;-).

 

In any case, looks to me as if the table whos_online could do with an index for the where clause...

[♥FWR Media]

you mean, first analyzed and THEN optimized ;-).

 

Slip of the keyboard :P

 

In any case, looks to me as if the table whos_online could do with an index for the where clause...

 

Before running the analyze > optimize

[♥FWR Media]

It may be worth mentioning the reason why both Monika and I jumped on those queries.

 

If you look at query 15 as an example ..

 

[15] => 0.001191

 

Then at query 46

 

[46] => 0.073710

 

Query 46 is taking about (excluding time for the seperate queries) 61 times the amount of time to execute.

 

Therefore your time is far better spent dealing with these than reducing query count at this stage.

[♥Monika in Germany]

Yup, before the table maintenance. I have had great results with recreating the indexes on other tables regularly, and then doing the analyze/optimize. I have even cron jobbed this in some custom scripts to execute nightly on very busy stores.

 

If you just want to do the recreating of indexes for one table in phpmyadmin, the syntax is

repair table TABLE_NAME quick;

[jbasszd]

thanks good idea.... but do i just type in "repair table "TABLE_NAME" quick;"? or is there a contrib to do this?

After that... then work on the newsdesk querys?

[♥Monika in Germany]

you will obviously have to use the name of the table you want to clean up .. and no, this is not a contrib. most of my ideas I either post here - like now - or I write a book ;-)

[♥FWR Media]

thanks good idea.... but do i just type in "repair table "TABLE_NAME" quick;"? or is there a contrib to do this?

After that... then work on the newsdesk querys?

 

Post deleted due to Monikas unearthly speed :D

[jbasszd]

Post deleted due to Monikas unearthly speed :D

I'd say thanks Monika... i'll give that a try and repost... laters

[♥FWR Media]

After that... then work on the newsdesk querys?

 

No

 

You still haven't read this thread properly .. e.g. you still have multiple select count(*) queries.

 

Let us know when you have found and solved it :D

[jbasszd]

Hello, i'm back after using that repair table TABLE_NAME quick; on every table, the time went up... ??? I seem to be missing the point big time... i know i need to get rid of the extra queries and try to get the time per queries down, i'm not sure as I'm just a little new at this so i'm still wineding up. Thanks for the help :)

 

Current Parse Time: 0.601 s with 47 queries
QUERY DEBUG: 
Array
(
[QUERIES] => Array
	(
		[0] => select value from sessions where sesskey = 'dmnstq2rtair7om28cjs4a9b42' and expiry > '1198962838'
		[1] => select c.categories_id, c.parent_id from categories c order by c.parent_id
		[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 < '1198961938'
		[4] => select count(*) as count from whos_online where session_id = 'dmnstq2rtair7om28cjs4a9b42'
		[5] => update whos_online set customer_id = '0', full_name = 'Guest', ip_address = '127.0.0.1', time_last_click = '1198962838', last_page_url = '/Corbens_Products/catalog/index.php?osCsid=dmnstq2rtair7om28cjs4a9b42' where session_id = 'dmnstq2rtair7om28cjs4a9b42'
		[6] => select banners_id, date_scheduled from banners where date_scheduled != ''
		[7] => select b.banners_id, b.expires_date, b.expires_impressions, sum(bh.banners_shown) as banners_shown from banners b, banners_history bh where b.status = '1' and b.banners_id = bh.banners_id group by b.banners_id
		[8] => select specials_id from specials where status = '1' and now() >= expires_date and expires_date > 0
		[9] => select configuration_key as cfgKey, configuration_value as cfgValue from configuration where configuration_group_id='333' or configuration_group_id='4'
		[10] => select * from supertracker where browser_string ='Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 2.0.50727; .NET CLR 1.1.4322)' and ip_address like '127.0%' and last_click > '2007-12-29 12:43:58'
		[11] => UPDATE supertracker set last_click='2007-12-29 13:13:58', exit_page='/Corbens_Products/catalog/index.php', num_clicks='4', added_cart='false', categories_viewed='a:1:{i:4;i:1;}', products_viewed='', customer_id='0', completed_purchase='false', cart_contents='', cart_total = '0', order_id = '0' where tracking_id='20'
		[12] => select information_title, information_description from information where language_id = '1' and information_group_id = '2'
		[13] => 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" order by sort_order, cd.categories_name
		[14] => select manufacturers_id, manufacturers_name from manufacturers order by manufacturers_name
		[15] => 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
		[16] => select products_name from products_description where products_id = '70' and language_id = '1'
		[17] => select specials_new_products_price from specials where products_id = '70' and status
		[18] => 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 = '1') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '22') and tr.tax_class_id = '0' group by tr.tax_priority
		[19] => SELECT information_id, information_title, parent_id FROM information WHERE visible='1' and language_id='1' and information_group_id = '1' ORDER BY sort_order
		[20] => 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
		[21] => select configuration_key as cfgKey, configuration_value as cfgValue from newsdesk_configuration
		[22] => select configuration_key as cfgKey, configuration_value as cfgValue from newsdesk_configuration
		[23] => select p.newsdesk_id, pd.language_id, pd.newsdesk_article_name, pd.newsdesk_article_description, pd.newsdesk_article_shorttext, pd.newsdesk_article_url, pd.newsdesk_article_url_name, 
p.newsdesk_image, p.newsdesk_date_added, p.newsdesk_last_modified, 
p.newsdesk_date_available, p.newsdesk_status  from newsdesk p, newsdesk_description 
pd WHERE pd.newsdesk_id = p.newsdesk_id and pd.language_id = "1" and newsdesk_status = 1 ORDER BY newsdesk_date_added DESC LIMIT 5
		[24] => select configuration_key as cfgKey, configuration_value as cfgValue from newsdesk_configuration
		[25] => select c.categories_id, cd.categories_name, c.parent_id from newsdesk_categories c, newsdesk_categories_description cd where c.catagory_status = '1' and c.parent_id = '' 
and c.categories_id = cd.categories_id and cd.language_id='1' order by sort_order, cd.categories_name
		[26] => select c.categories_id, cd.categories_name, c.parent_id from newsdesk_categories c, newsdesk_categories_description cd 
where c.catagory_status = '1' and c.parent_id = '0' and c.categories_id = cd.categories_id and cd.language_id='1' order by sort_order, cd.categories_name
		[27] => select count(*) as count from newsdesk_categories where parent_id = '10'
		[28] => select count(*) as count from newsdesk_categories where parent_id = '1'
		[29] => select count(*) as count from newsdesk_categories where parent_id = '2'
		[30] => select count(*) as count from newsdesk_categories where parent_id = '9'
		[31] => select count(*) as count from newsdesk_categories where parent_id = '4'
		[32] => select count(*) as count from newsdesk_categories where parent_id = '5'
		[33] => 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
		[34] => 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 = '1') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '22') and tr.tax_class_id = '1' group by tr.tax_priority
		[35] => select languages_id, name, code, image, directory from languages order by sort_order
		[36] => 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
		[37] => select substring(reviews_text, 1, 60) as reviews_text from reviews_description where reviews_id = '1' and languages_id = '1'
		[38] => select configuration_key as cfgKey, configuration_value as cfgValue from newsdesk_configuration
		[39] => select p.newsdesk_id, pd.language_id, pd.newsdesk_article_name, pd.newsdesk_article_description, pd.newsdesk_article_shorttext, pd.newsdesk_article_url, pd.newsdesk_article_url_name, 
p.newsdesk_image, p.newsdesk_image_two, p.newsdesk_image_three, p.newsdesk_date_added, p.newsdesk_last_modified, pd.newsdesk_article_viewed, 
p.newsdesk_date_available, p.newsdesk_status  from newsdesk p, newsdesk_description 
pd WHERE pd.newsdesk_id = p.newsdesk_id and pd.language_id = "1" and newsdesk_status = 1 and p.newsdesk_sticky = 0 ORDER BY newsdesk_date_added DESC LIMIT 3
		[40] => 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, pd.products_name from products p left join specials s on p.products_id = s.products_id left join products_description pd on p.products_id = pd.products_id where products_status = '1' and pd.language_id = '1' order by p.products_date_added desc limit 9
		[41] => 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
		[42] => select startdate, counter from counter
		[43] => update counter set counter = '1398'
		[44] => select banners_id, banners_title, banners_image, banners_html_text from banners where status = '1' and banners_group = '468x50'
		[45] => select count(*) as total from sessions where sesskey = 'dmnstq2rtair7om28cjs4a9b42'
		[46] => update sessions set expiry = '1198964279', value = 'SESSION_USER_AGENT|s:89:\"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 2.0.50727; .NET CLR 1.1.4322)\";SESSION_IP_ADDRESS|s:9:\"127.0.0.1\";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:\"USD\";navigation|O:17:\"navigationHistory\":2:{s:4:\"path\";a:2:{i:0;a:4:{s:4:\"page\";s:17:\"newsdesk_info.php\";s:4:\"mode\";s:6:\"NONSSL\";s:3:\"get\";a:2:{s:6:\"osCsid\";s:26:\"dmnstq2rtair7om28cjs4a9b42\";s:11:\"newsdesk_id\";s:2:\"38\";}s:4:\"post\";a:0:{}}i:1;a:4:{s:4:\"page\";s:9:\"index.php\";s:4:\"mode\";s:6:\"NONSSL\";s:3:\"get\";a:1:{s:6:\"osCsid\";s:26:\"dmnstq2rtair7om28cjs4a9b42\";}s:4:\"post\";a:0:{}}}s:8:\"snapshot\";a:0:{}}' where sesskey = 'dmnstq2rtair7om28cjs4a9b42'
	)

[TIME] => Array
	(
		[0] => 0.001136
		[1] => 0.000635
		[2] => 0.000782
		[3] => 0.000788
		[4] => 0.000971
		[5] => 0.031068
		[6] => 0.001462
		[7] => 0.001033
		[8] => 0.001320
		[9] => 0.001021
		[10] => 0.001731
		[11] => 0.001350
		[12] => 0.000913
		[13] => 0.001160
		[14] => 0.000643
		[15] => 0.000807
		[16] => 0.001449
		[17] => 0.001058
		[18] => 0.000979
		[19] => 0.000647
		[20] => 0.000928
		[21] => 0.000659
		[22] => 0.000598
		[23] => 0.000616
		[24] => 0.001085
		[25] => 0.002963
		[26] => 0.001475
		[27] => 0.000980
		[28] => 0.001049
		[29] => 0.001145
		[30] => 0.001004
		[31] => 0.001012
		[32] => 0.001337
		[33] => 0.000649
		[34] => 0.000990
		[35] => 0.001023
		[36] => 0.000653
		[37] => 0.000547
		[38] => 0.000690
		[39] => 0.000606
		[40] => 0.000677
		[41] => 0.001173
		[42] => 0.000901
		[43] => 0.039152
		[44] => 0.000611
		[45] => 0.001342
		[46] => 0.033799
	)

)

[♥FWR Media]

Hello, i'm back after using that repair table TABLE_NAME quick; on every table, the time went up... ??? I seem to be missing the point big time... i know i need to get rid of the extra queries and try to get the time per queries down, i'm not sure as I'm just a little new at this so i'm still wineding up. Thanks for the help

 

you ran it on what? as both the queries I mentioned have fallen considerably.

[♥Monika in Germany]

and I hope you have, right afterwards, done the analyze, then the optimize on all those tables. Only then will it really work...

[jbasszd]

i run it though phpMyAdmin in the SQL query area, i just made a text file of all the tables, add the code to it so it looked like this

repair table address_book quick;
repair table address_format quick;
repair table administrators quick;
repair table am_attributes_to_templates quick;
repair table am_templates quick;

...

Althought the three larger times [5] [43] [46] went down... the overall time went up... did i make a mistake?

[♥FWR Media]

Have to say I thought that "quick" only repaired the index file of tables that already have indexes without touching the data.

 

Ignore this I misread a post.

[jbasszd]

???? so i made a mistake??? i need to do something else???

[♥FWR Media]

you mean, first analyzed and THEN optimized ;-).

 

In any case, looks to me as if the table whos_online could do with an index for the where clause...

 

 

Have you done it?

 

If so then go to your db check all (bottom left after all the square boxes)

 

There's a dropdown menu

 

choose Analyze table

 

When it has finished do the same again but from the dropdown choose Optimize table

 

Report again

[jbasszd]

Well that went down... so is that the best i'm going to be able to do?

 

 

Current Parse Time: 0.454 s with 47 queries
QUERY DEBUG: 
Array
(
[QUERIES] => Array
	(
		[0] => select value from sessions where sesskey = 'dmnstq2rtair7om28cjs4a9b42' and expiry > '1198965158'
		[1] => select c.categories_id, c.parent_id from categories c order by c.parent_id
		[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 < '1198964258'
		[4] => select count(*) as count from whos_online where session_id = 'dmnstq2rtair7om28cjs4a9b42'
		[5] => update whos_online set customer_id = '0', full_name = 'Guest', ip_address = '127.0.0.1', time_last_click = '1198965158', last_page_url = '/Corbens_Products/catalog/index.php?osCsid=dmnstq2rtair7om28cjs4a9b42' where session_id = 'dmnstq2rtair7om28cjs4a9b42'
		[6] => select banners_id, date_scheduled from banners where date_scheduled != ''
		[7] => select b.banners_id, b.expires_date, b.expires_impressions, sum(bh.banners_shown) as banners_shown from banners b, banners_history bh where b.status = '1' and b.banners_id = bh.banners_id group by b.banners_id
		[8] => select specials_id from specials where status = '1' and now() >= expires_date and expires_date > 0
		[9] => select configuration_key as cfgKey, configuration_value as cfgValue from configuration where configuration_group_id='333' or configuration_group_id='4'
		[10] => select * from supertracker where browser_string ='Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 2.0.50727; .NET CLR 1.1.4322)' and ip_address like '127.0%' and last_click > '2007-12-29 13:22:38'
		[11] => UPDATE supertracker set last_click='2007-12-29 13:52:38', exit_page='/Corbens_Products/catalog/index.php', num_clicks='17', added_cart='false', categories_viewed='a:1:{i:4;i:1;}', products_viewed='', customer_id='0', completed_purchase='false', cart_contents='', cart_total = '0', order_id = '0' where tracking_id='20'
		[12] => select information_title, information_description from information where language_id = '1' and information_group_id = '2'
		[13] => 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" order by sort_order, cd.categories_name
		[14] => select manufacturers_id, manufacturers_name from manufacturers order by manufacturers_name
		[15] => 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
		[16] => select products_name from products_description where products_id = '70' and language_id = '1'
		[17] => select specials_new_products_price from specials where products_id = '70' and status
		[18] => 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 = '1') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '22') and tr.tax_class_id = '0' group by tr.tax_priority
		[19] => SELECT information_id, information_title, parent_id FROM information WHERE visible='1' and language_id='1' and information_group_id = '1' ORDER BY sort_order
		[20] => 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
		[21] => select configuration_key as cfgKey, configuration_value as cfgValue from newsdesk_configuration
		[22] => select configuration_key as cfgKey, configuration_value as cfgValue from newsdesk_configuration
		[23] => select p.newsdesk_id, pd.language_id, pd.newsdesk_article_name, pd.newsdesk_article_description, pd.newsdesk_article_shorttext, pd.newsdesk_article_url, pd.newsdesk_article_url_name, 
p.newsdesk_image, p.newsdesk_date_added, p.newsdesk_last_modified, 
p.newsdesk_date_available, p.newsdesk_status  from newsdesk p, newsdesk_description 
pd WHERE pd.newsdesk_id = p.newsdesk_id and pd.language_id = "1" and newsdesk_status = 1 ORDER BY newsdesk_date_added DESC LIMIT 5
		[24] => select configuration_key as cfgKey, configuration_value as cfgValue from newsdesk_configuration
		[25] => select c.categories_id, cd.categories_name, c.parent_id from newsdesk_categories c, newsdesk_categories_description cd where c.catagory_status = '1' and c.parent_id = '' 
and c.categories_id = cd.categories_id and cd.language_id='1' order by sort_order, cd.categories_name
		[26] => select c.categories_id, cd.categories_name, c.parent_id from newsdesk_categories c, newsdesk_categories_description cd 
where c.catagory_status = '1' and c.parent_id = '0' and c.categories_id = cd.categories_id and cd.language_id='1' order by sort_order, cd.categories_name
		[27] => select count(*) as count from newsdesk_categories where parent_id = '10'
		[28] => select count(*) as count from newsdesk_categories where parent_id = '1'
		[29] => select count(*) as count from newsdesk_categories where parent_id = '2'
		[30] => select count(*) as count from newsdesk_categories where parent_id = '9'
		[31] => select count(*) as count from newsdesk_categories where parent_id = '4'
		[32] => select count(*) as count from newsdesk_categories where parent_id = '5'
		[33] => 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
		[34] => 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 = '1') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '22') and tr.tax_class_id = '1' group by tr.tax_priority
		[35] => select languages_id, name, code, image, directory from languages order by sort_order
		[36] => 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
		[37] => select substring(reviews_text, 1, 60) as reviews_text from reviews_description where reviews_id = '1' and languages_id = '1'
		[38] => select configuration_key as cfgKey, configuration_value as cfgValue from newsdesk_configuration
		[39] => select p.newsdesk_id, pd.language_id, pd.newsdesk_article_name, pd.newsdesk_article_description, pd.newsdesk_article_shorttext, pd.newsdesk_article_url, pd.newsdesk_article_url_name, 
p.newsdesk_image, p.newsdesk_image_two, p.newsdesk_image_three, p.newsdesk_date_added, p.newsdesk_last_modified, pd.newsdesk_article_viewed, 
p.newsdesk_date_available, p.newsdesk_status  from newsdesk p, newsdesk_description 
pd WHERE pd.newsdesk_id = p.newsdesk_id and pd.language_id = "1" and newsdesk_status = 1 and p.newsdesk_sticky = 0 ORDER BY newsdesk_date_added DESC LIMIT 3
		[40] => 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, pd.products_name from products p left join specials s on p.products_id = s.products_id left join products_description pd on p.products_id = pd.products_id where products_status = '1' and pd.language_id = '1' order by p.products_date_added desc limit 9
		[41] => 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
		[42] => select startdate, counter from counter
		[43] => update counter set counter = '1411'
		[44] => select banners_id, banners_title, banners_image, banners_html_text from banners where status = '1' and banners_group = '468x50'
		[45] => select count(*) as total from sessions where sesskey = 'dmnstq2rtair7om28cjs4a9b42'
		[46] => update sessions set expiry = '1198966598', value = 'SESSION_USER_AGENT|s:89:\"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 2.0.50727; .NET CLR 1.1.4322)\";SESSION_IP_ADDRESS|s:9:\"127.0.0.1\";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:\"USD\";navigation|O:17:\"navigationHistory\":2:{s:4:\"path\";a:2:{i:0;a:4:{s:4:\"page\";s:17:\"newsdesk_info.php\";s:4:\"mode\";s:6:\"NONSSL\";s:3:\"get\";a:2:{s:6:\"osCsid\";s:26:\"dmnstq2rtair7om28cjs4a9b42\";s:11:\"newsdesk_id\";s:2:\"38\";}s:4:\"post\";a:0:{}}i:1;a:4:{s:4:\"page\";s:9:\"index.php\";s:4:\"mode\";s:6:\"NONSSL\";s:3:\"get\";a:1:{s:6:\"osCsid\";s:26:\"dmnstq2rtair7om28cjs4a9b42\";}s:4:\"post\";a:0:{}}}s:8:\"snapshot\";a:0:{}}' where sesskey = 'dmnstq2rtair7om28cjs4a9b42'
	)

[TIME] => Array
	(
		[0] => 0.000995
		[1] => 0.000911
		[2] => 0.001021
		[3] => 0.001152
		[4] => 0.001375
		[5] => 0.029591
		[6] => 0.000975
		[7] => 0.000544
		[8] => 0.000926
		[9] => 0.000695
		[10] => 0.001383
		[11] => 0.000934
		[12] => 0.000635
		[13] => 0.000640
		[14] => 0.000646
		[15] => 0.000680
		[16] => 0.000588
		[17] => 0.000559
		[18] => 0.000824
		[19] => 0.000644
		[20] => 0.000649
		[21] => 0.000679
		[22] => 0.000588
		[23] => 0.000608
		[24] => 0.000669
		[25] => 0.001288
		[26] => 0.000736
		[27] => 0.001058
		[28] => 0.000953
		[29] => 0.000627
		[30] => 0.000576
		[31] => 0.000596
		[32] => 0.000595
		[33] => 0.000636
		[34] => 0.000647
		[35] => 0.000848
		[36] => 0.000636
		[37] => 0.000596
		[38] => 0.000666
		[39] => 0.000662
		[40] => 0.000688
		[41] => 0.001144
		[42] => 0.000905
		[43] => 0.033844
		[44] => 0.001016
		[45] => 0.001344
		[46] => 0.034224
	)

)

[♥FWR Media]

Nope you haven't even started.

 

To give you an example .. my index.php home page has a page parse time of on average 0.090 yours is 0.454 your number of products/categories will have an impact.

 

Number of orders will have an impact (i.e. also purchased)

 

Also I mentioned count which you seem to have ignored.

[jbasszd]

So i found the if i comented out line 75-76 in includes/modules/sts_inc/general.php

/*  require(DIR_WS_INCLUDES . 'counter.php');
 $sts->template['numrequests'] = $counter_now . ' ' . FOOTER_TEXT_REQUESTS_SINCE . ' ' . $counter_startdate_formatted;*/

i got down to Current Parse Time: 0.439 s with 45 queries but i not thinking of any others... I think the count was mentioned before but not with the STS... pule i thought it was the count of the side of the produts not the counter at the bottom... lol

 

 

Any more ideas?

[jbasszd]

I think I was misunderstanding you count suggestions... sorry.... so I am wondering if I can combined the count on the news desk part as well as all the other ones with the news desk some how? the take up 20 queries [18] thought [38] and most of the time but really want the news desk...

[♥FWR Media]

i think i was missundertanding you count sujjestions... sorry.... so i am woundering if i can cobined the count on the news desk part some how? the take up 20 queries and most of the time but real want the news desk...

 

Nope I'm not playing any more .. read this thread and come back again when you have found and solved the "count problem"