Remove redundant queries? PLEASE!

[Woloj]

Hi, I have installed both the page_cache and cache_configuration contributions.

 

I searched the forum several times to find a solution but it seems like a dead end... somebody here should have optimized the queries no? Please share if you did!!

 

What ticks me off the is amount of queries still being made, it's very ugly imo.

 

I read you could use a LEFT JOIN to reduce them? I'm not that good to learn, edit the existing code and fix the SQL queries myself, perhaps someone else could give it a try?

 

I believe you can remove about 16 queries or so if you figure out how to modify the code properly,

I tried but then the links for the cPATH= screws up removing _ etc. And the menu categories stops being highlighted in bold. So no luck here...

 

Having below or about 20 per page is probably doable and more acceptable!

 

I can't imagine how many queries my store would have if I had over 100 products listed..

 

34 queries on first page, here are some redundant ones for index.php:

 

[7] => select count(*) as count from categories where parent_id = '1'
		[8] => select count(*) as count from categories where parent_id = '2'
		[9] => select count(*) as count from categories where parent_id = '3'
		[10] => select manufacturers_id, manufacturers_name from manufacturers order by manufacturers_name
		[11] => 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
		[12] => select products_name from products_description where products_id = '26' and language_id = '4'
		[13] => select specials_new_products_price from specials where products_id = '26' and status
		[14] => 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 = '203') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '0') and tr.tax_class_id = '1' group by tr.tax_priority
		[15] => 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 9
		[16] => select products_name from products_description where products_id = '28' and language_id = '4'
		[17] => 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 = '203') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '0') and tr.tax_class_id = '0' group by tr.tax_priority
		[18] => select products_name from products_description where products_id = '26' and language_id = '4'
		[19] => select products_name from products_description where products_id = '1' and language_id = '4'
		[20] => 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 = '203') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '0') and tr.tax_class_id = '3' group by tr.tax_priority
		[21] => select products_name from products_description where products_id = '15' and language_id = '4'
		[22] => select products_name from products_description where products_id = '4' and language_id = '4'
		[23] => select products_name from products_description where products_id = '18' and language_id = '4'
		[24] => select products_name from products_description where products_id = '7' and language_id = '4'
		[25] => select products_name from products_description where products_id = '21' and language_id = '4'
		[26] => select products_name from products_description where products_id = '10' and language_id = '4'
		[27] => 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 = '4' order by date_expected desc limit 10
		[28] => 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 = '4' order by p.products_ordered desc, pd.products_name limit 10
		[29] => 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 = '4' and s.status = '1' order by s.specials_date_added desc limit 10

 

 

63 queries on first category, here are some redundant ones index.php?cPath=1:

 

You could probably get it down to at least 30!!

 

[11] => 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
		[12] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.parent_id = '1' and c.categories_id = cd.categories_id and cd.language_id='1' order by sort_order, cd.categories_name
		[13] => select count(*) as count from categories where parent_id = '1'
		[14] => select count(*) as count from categories where parent_id = '17'
		[15] => select count(*) as count from categories where parent_id = '4'
		[16] => select count(*) as count from categories where parent_id = '8'
		[17] => select count(*) as count from categories where parent_id = '16'
		[18] => select count(*) as count from categories where parent_id = '9'
		[19] => select count(*) as count from categories where parent_id = '6'
		[20] => select count(*) as count from categories where parent_id = '5'
		[21] => select count(*) as count from categories where parent_id = '7'
		[22] => select count(*) as count from categories where parent_id = '2'
		[23] => select count(*) as count from categories where parent_id = '3'

[31] => select parent_id from categories where categories_id = '1'
		[32] => select parent_id from categories where categories_id = '17'
		[33] => select parent_id from categories where categories_id = '1'
		[34] => select parent_id from categories where categories_id = '4'
		[35] => select parent_id from categories where categories_id = '1'
		[36] => select parent_id from categories where categories_id = '8'
		[37] => select parent_id from categories where categories_id = '1'
		[38] => select parent_id from categories where categories_id = '16'
		[39] => select parent_id from categories where categories_id = '1'
		[40] => select parent_id from categories where categories_id = '9'
		[41] => select parent_id from categories where categories_id = '1'
		[42] => select parent_id from categories where categories_id = '6'
		[43] => select parent_id from categories where categories_id = '1'
		[44] => select parent_id from categories where categories_id = '5'
		[45] => select parent_id from categories where categories_id = '1'
		[46] => select parent_id from categories where categories_id = '7'

[48] => select products_name from products_description where products_id = '28' and language_id = '1'
		[49] => 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 = '203') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '0') and tr.tax_class_id = '0' group by tr.tax_priority
		[50] => select products_name from products_description where products_id = '1' and language_id = '1'
		[51] => 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 = '203') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '0') and tr.tax_class_id = '3' group by tr.tax_priority
		[52] => select products_name from products_description where products_id = '25' and language_id = '1'
		[53] => select products_name from products_description where products_id = '2' and language_id = '1'
		[54] => select products_name from products_description where products_id = '26' and language_id = '1'
		[55] => select products_name from products_description where products_id = '3' and language_id = '1'
		[56] => select products_name from products_description where products_id = '27' and language_id = '1'

 

48 queries some redundant queries for product_info.php?products_id=1:

 

[7] => select parent_id from categories where categories_id = '4'
		[8] => select parent_id from categories where categories_id = '1'
		[9] => select categories_name from categories_description where categories_id = '1' and language_id = '1'
		[10] => select categories_name from categories_description where categories_id = '4' and language_id = '1'
		[11] => select products_model from products where products_id = '1'
		[12] => select count(*) as total from products p, products_description pd where p.products_status = '1' and p.products_id = '1' and pd.products_id = p.products_id and pd.language_id = '1'
		[13] => 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
		[14] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.parent_id = '1' and c.categories_id = cd.categories_id and cd.language_id='1' order by sort_order, cd.categories_name
		[15] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.parent_id = '4' and c.categories_id = cd.categories_id and cd.language_id='1' order by sort_order, cd.categories_name
		[16] => select count(*) as count from categories where parent_id = '1'
		[17] => select count(*) as count from categories where parent_id = '17'
		[18] => select count(*) as count from categories where parent_id = '4'
		[19] => select count(*) as count from categories where parent_id = '8'
		[20] => select count(*) as count from categories where parent_id = '16'
		[21] => select count(*) as count from categories where parent_id = '9'
		[22] => select count(*) as count from categories where parent_id = '6'
		[23] => select count(*) as count from categories where parent_id = '5'
		[24] => select count(*) as count from categories where parent_id = '7'
		[25] => select count(*) as count from categories where parent_id = '2'
		[26] => select count(*) as count from categories where parent_id = '3'

[31] => 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 = '203') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '0') and tr.tax_class_id = '1' group by tr.tax_priority
[35] => 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 = '203') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '0') and tr.tax_class_id = '3' group by tr.tax_priority


[38] => select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix from products_attributes pa, products_options_values pov where pa.products_id = '1' and pa.options_id = '4' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '1'
		[39] => select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix from products_attributes pa, products_options_values pov where pa.products_id = '1' and pa.options_id = '3' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '1'

 

And there are probably more, I just skimmed through these...

 

If someone has a solution for this badly coded mess please share, it's really needed, like now.... :(

[MarcoZorro]

There are many posts in the forums about reducing the query count. A simple search should find them fairly easily

[Woloj]

I tried to search countless times but yet I haven't found any results except these two threads and contribs which I already installed.

[♥toyicebear]

A Store Speed Optimization in Progress, Step by step from a vanilla install!

[Woloj]

Been there, done that, looking for more optimisations after that one. Still alot of redundant queries for me...