897 replies to this topic

[revamp]

pbor1234, on Jun 2 2009, 09:26 PM, said:

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

No problem, Paul, I have noticed that but I wanted to give the results till now anyway...

[revamp]

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

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

Hello Jan,

I tried this but then the pages are disrupted(no left column anymore and the querie debug does not show up anymore...) and when I refresh the page I get:

1114 - The table 'temp_H1hoCXn5QB1C' is full

insert into temp_H1hoCXn5QB1C select pd.products_id, pd.products_name from products_description pd where pd.language_id = '1'

[TEP STOP]

Am I doing something wrong, or?

I have page cache from Chemo on and the standard OSC cache off

Edited by revamp, 07 June 2009, 13:53.

[Jan Zonjee]

revamp, on Jun 7 2009, 03:51 PM, said:

I tried this but then the pages are disrupted(no left column anymore and the querie debug does not show up anymore...) and when I refresh the page I get:

1114 - The table 'temp_H1hoCXn5QB1C' is full

insert into temp_H1hoCXn5QB1C select pd.products_id, pd.products_name from products_description pd where pd.language_id = '1'

[TEP STOP]

Am I doing something wrong, or?

Of course you have a lot more products than I could ever test with. Perhaps there is maximum limit for such temporary tables or perhaps you have run out of disk space for your database?. Try leaving the ENGINE=MEMORY part out then it becomes an ordinary MySQL table (which should be "dropped" after having done it's job so you should never see those tables in your database).

Edited by Jan Zonjee, 07 June 2009, 14:07.

[revamp]

Jan Zonjee, on Jun 7 2009, 03:02 PM, said:

Of course you have a lot more products than I could ever test with. Perhaps there is maximum limit for such temporary tables or perhaps you have run out of disk space for your database?. Try leaving the ENGINE=MEMORY part out then it becomes an ordinary MySQL table (which should be "dropped" after having done it's job so you should never see those tables in your database).

I have tried that too: the same result but without the tep stop. When I look at the database I see a lot of temp_xxx_tabels, so they weren't dropped...

[Jan Zonjee]

revamp, on Jun 7 2009, 05:40 PM, said:

I have tried that too: the same result but without the tep stop. When I look at the database I see a lot of temp_xxx_tabels, so they weren't dropped...

At least drop them to get some room in your database. It could be that your database is close to the limit of the filesystem. Ever tried to back the database up?

[revamp]

Jan Zonjee, on Jun 7 2009, 05:28 PM, said:

At least drop them to get some room in your database. It could be that your database is close to the limit of the filesystem. Ever tried to back the database up?

Of course I have dropped all the temp_tables.
I have made a complete backup of the website and database with the backup-program from the provider. When I try to dump the entire database with phpmyadmin I get a empty file...
Small tables are no problem but the big products tabel and products_description tables are a problem. I think this is due to the shared server (cannot change the settings) and the big tables ofcourse.

[Jan Zonjee]

revamp, on Jun 7 2009, 08:34 PM, said:

When I try to dump the entire database with phpmyadmin I get a empty file...

You could try this version of the Database backup manager but if you can make backups with the program of your provider you obviously do not need it. Just curious if it would work

[schoent]

I have created this code for product listing.
what it does: it shows the colors(products_options_id=2) for each item available in the product listing.

this is curcial in my shop.

the bad part of the code is that it's makes a query for each color per product.

i think it can be simplified with an "left outer join" but i can't get it done.

this is the code that i have now:

please help


case 'PRODUCT_LIST_MULTIPLE':
$lc_align = 'right';
$lc_valign = 'top';
$lc_text = ('');


$products_attributes_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_ATTRIBUTES . " patrib where patrib.products_id='" . $listing['products_id'] . "' and patrib.options_id = popt.products_options_id and products_options_id=2 and popt.language_id = '" . $languages_id . "'");
$products_attributes = tep_db_fetch_array($products_attributes_query);

if ($products_attributes['total'] > 0) {
$lc_text .= '<table border="0" cellpadding="1" cellspacing"0"><tr>';
$products_options_name_query = tep_db_query("select distinct popt.products_options_id, popt.products_options_name, popt.products_options_images_enabled from " . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_ATTRIBUTES . " patrib where products_options_id=2 and patrib.products_id='" . $listing['products_id'] . "' and patrib.options_id = popt.products_options_id and popt.language_id = '" . $languages_id . "'");

while ($products_options_name = tep_db_fetch_array($products_options_name_query)) {
$selected = 0;
$products_options_array = array();
$lc_text .= '';
$products_options_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pov.products_options_values_thumbnail from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov where pa.products_id = '" . $listing['products_id'] . "' and pa.options_id = '" . $products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . $languages_id . "'");
while ($products_options = tep_db_fetch_array($products_options_query)) {
$products_options_array[] = array('id' => $products_options['products_options_values_id'], 'text' => $products_options['products_options_values_name'], 'thumbnail' => $products_options['products_options_values_thumbnail']);


}

$count=0;
foreach ($products_options_array as $opti_array){
$lc_text .= '<td bgcolor="#CCCCCC" align="center">' . tep_image(DIR_WS_IMAGES . 'options/thumb/' . $opti_array['thumbnail'], $opti_array['text'], 10, 10) . '</td>';
$count++;
if ($count%OPTIONS_IMAGES_NUMBER_PER_ROW == 0) {
echo '';
$count = 0;
}
}

[sfarhan]

Monika in Germany, on Aug 10 2007, 03:55 PM, said:

hum hum ... I did not use a command (less mess). I just went to the table structure of the specials table in phpmyadmin and add the new index there (bottom left)

name (your choice)
link to column products_id
save

the official command would be

create index IDX_SPECIALS_PRODUCTS_ID on specials (products_id)

Thanks Monika it worked for me. I found my site very slow after installing Special_to_category contribution. after adding index to special table my site is amazing browing faster then everbefore . Good work.

[pbor1234]

Recently i got a complaint from our hoster about a query taking as long as 2 seconds It was actually easy to reproduce on my local workstation so i was able to track down the cause. The query is created by the advanced_search_result where a price-range is given (with display price with tax set to true). Query becomes as follows (for vanilla 2.2rc2 installation):

select distinct p.products_image, m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price , SUM(tr.tax_rate) as tax_rate from products p left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id left join tax_rates tr on p.products_tax_class_id = tr.tax_class_id left join zones_to_geo_zones gz on tr.tax_zone_id = gz.geo_zone_id and (gz.zone_country_id is null or gz.zone_country_id = '0' or gz.zone_country_id = '223') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '18'), products_description pd, categories c, products_to_categories p2c where p.products_status = '1' 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 (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) >= 50) and (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) <= 250) group by p.products_id, tr.tax_priority order by pd.products_name limit 0, 20;

This query takes about 2 seconds on our database with +/- 2500 products. Notice that i extended the query a lot but i will leave the details out for now since i don't think it adds anything to the issue at hand.

When i simply remove the last two if statements query becomes as shown below and it takes only 60ms.

select distinct p.products_image, m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price , SUM(tr.tax_rate) as tax_rate from products p left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id left join tax_rates tr on p.products_tax_class_id = tr.tax_class_id left join zones_to_geo_zones gz on tr.tax_zone_id = gz.geo_zone_id and (gz.zone_country_id is null or gz.zone_country_id = '0' or gz.zone_country_id = '223') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '18'), products_description pd, categories c, products_to_categories p2c where p.products_status = '1' 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 (IF(s.status, s.specials_new_products_price, p.products_price) * (1 + (tr.tax_rate / 100) ) >= 50) and (IF(s.status, s.specials_new_products_price, p.products_price) * (1 + (tr.tax_rate / 100) ) <= 250) group by p.products_id, tr.tax_priority order by pd.products_name limit 0, 20;

I allready tried to add some indexes on tr and gz but that only helps slightly (going down to 1.4 seconds).

I'm having difficulties to optimize the query, is there a common solution to this? What alternatives do i have for the if - condition?

Paul

[spooks]

I`m not sure if it would help in this, but you could look at the CASE statement as a more efficient alternative to IF

[addicted]

Contribution that may cost performance troubles
1. SEO-G by Enigma - generate more 3500 queries in category with 20 sub categories and 300 products
[4705] => select seo_url_get, seo_url_org from seo_url where seo_url_key = 'ee81957b881c074c4a4b496261bee9d6'
[4706] => select seo_exclude_key from seo_exclude where seo_exclude_key = 'fc8d4bb3bf7f56ca700507fee0558c16'
[4707] => select seo_url_org, seo_redirect from seo_redirect where seo_url_key = '27caa1c52b10ab84d98e417ff89769f4'
[4708] => select seo_url_get, seo_url_org from seo_url where seo_url_key = '27caa1c52b10ab84d98e417ff89769f4'
lovely
Visitor_stats - it was real problem for me - there was 20-30 sec of page load.
After doing everything, I found in this tread I have 2-3 sec page parse time (with SEO-G On and all of its thousands queries) and without page cache by chemo (didn't work correct for me)
My shop has about 500 categories and 15 000 items

[revenson]

"Store Speed Optimization in Progress" looks like a great topic I'd like to explore.

However, since it started way back in 2004, is this thread is pertinent to my version (I'm running 2.2 RC2a)?

Also, I see that 'Chemo' started it and he's part of the group "banned", so I'm wondering if his advice was deemed inappropriate in some way and not to be taken seriously.

Comments?

[addicted]

revenson, on Aug 14 2009, 02:05 AM, said:

"Store Speed Optimization in Progress" looks like a great topic I'd like to explore.

However, since it started way back in 2004, is this thread is pertinent to my version (I'm running 2.2 RC2a)?

Also, I see that 'Chemo' started it and he's part of the group "banned", so I'm wondering if his advice was deemed inappropriate in some way and not to be taken seriously.

Comments?

I'm not a guru but Chemo's staff are clean code and its working fine.
Also pay attention to Monica From Germany posts and Jan's category contribution

[Jan Zonjee]

revenson, on Aug 14 2009, 01:05 AM, said:

I'm wondering if his advice was deemed inappropriate in some way and not to be taken seriously.

The fact that Chemo got banned has nothing to do whatsoever with this thread or his contributions.

[Jamez]

Jan Zonjee, on Aug 15 2009, 06:52 PM, said:

The fact that Chemo got banned has nothing to do whatsoever with this thread or his contributions.

Id disagree on that one Jan, its my understanding there was a vote by the then moderators/team based on his contributions, "attitude" etc, with the lead sitting on the fence.

What few of his contributions are left, (most have been removed) are well worth looking at and are very valid for any oscommerce based code.

[bobsi18]

I've followed through this thread time and time again, and it has done wonders to speed up my store - thanks to all that have contributed. I'm a self-taught "hacker" - most of the mods on my store are from me observing what different contributions do, and fiddling with them until they work for me, and as a result, when my site is running slow, it's normally because of some tweak I've added.

So, whilst my site is running nice and fast, my admin side is slooooow. And I have no idea what is causing it, it's no doubt one of the tweaks I've done. I'm wondering if there is anything similar to this that shows the parse time and the database queries for the admin side?

[pbor1234]

bobsi18, on Sep 9 2009, 09:57 AM, said:

I've followed through this thread time and time again, and it has done wonders to speed up my store - thanks to all that have contributed. I'm a self-taught "hacker" - most of the mods on my store are from me observing what different contributions do, and fiddling with them until they work for me, and as a result, when my site is running slow, it's normally because of some tweak I've added.

So, whilst my site is running nice and fast, my admin side is slooooow. And I have no idea what is causing it, it's no doubt one of the tweaks I've done. I'm wondering if there is anything similar to this that shows the parse time and the database queries for the admin side?

I had no problems at all adding the "output queries debug" contribution to the admin side.... You will however be shocked on the amount of queries in the catalog sections.
Paul

[Andreas2003]

Alright, tomorrow I will definitely start to optimize my site.
Today I saw, that the index.php generates a total of 520 queries per hit - I'm shocked.

[discxpress]

Hello FWR or Jan Zonjee,

I have applied all the suggestions in this thread and they've done wonders with my large store (1355 categories,sub-cats total and over 450,000 products). However, the only thing that's slowing things up is this query that is listed hundreds of times:

RESULT Resource id #90 
QUERY select c.categories_id, cd.categories_name from categories c, categories_description cd where parent_id = '0' and c.categories_id = cd.categories_id and cd.language_id = '1' order by sort_order, cd.categories_name

The parent_id= '0' is the first from the list where 0 would be replaced with the respective category id's.

Just by looking at the code, I know it's coming from the categories table and queries for every category.

I need help deciding what I should do next. I could install FWR's SEO Popout menu which could resolve the problem. I'm leaning heavily towards this option. Then would have to install a few lines of code on every page of the catalog side. Which isn't bad at all.

Before I install that contribution, I was wondering if there was something I could try first. Maybe tweaking some code in one or two files. If not then I'll try the first option.

Thanks for any help you can provide.