100 sql queries for index.php?

ilok · December 2, 2004

While I was looking at mysql sql log, I noticed that every page access for index.php is using around 100 sql queries!!!

Is this by design? This seem horrible unscalable....

041202 13:30:52 1 Query select cd.categories_name from categories c, categories_description cd where c.categories_id = '0' and cd.categories_id = '0' and cd.language_id = '1'

1 Query select manufacturers_name from manufacturers where manufacturers_id = '0'

1 Query 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

1 Query select count(*) as count from categories where parent_id = '1'

1 Query select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '1'

1 Query select categories_id from categories where parent_id = '1'

1 Query select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '4'

1 Query select categories_id from categories where parent_id = '4'

1 Query select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '5'

1 Query select categories_id from categories where parent_id = '5'

1 Query select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '6'

1 Query select categories_id from categories where parent_id = '6'

1 Query select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '7'

1 Query select categories_id from categories where parent_id = '7'

1 Query select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '8'

1 Query select categories_id from categories where parent_id = '8'

1 Query select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '9'

1 Query select categories_id from categories where parent_id = '9'

1 Query select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '16'

1 Query select categories_id from categories where parent_id = '16'

1 Query select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '17'

1 Query select categories_id from categories where parent_id = '17'

1 Query select count(*) as count from categories where parent_id = '2'

1 Query select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '2'

1 Query select categories_id from categories where parent_id = '2'

1 Query select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '18'

1 Query select categories_id from categories where parent_id = '18'

1 Query select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '19'

1 Query select categories_id from categories where parent_id = '19'

1 Query select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '20'

1 Query select categories_id from categories where parent_id = '20'

1 Query select count(*) as count from categories where parent_id = '3'

1 Query select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '3'

1 Query select categories_id from categories where parent_id = '3'

1 Query select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '10'

1 Query select categories_id from categories where parent_id = '10'

1 Query select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '11'

1 Query select categories_id from categories where parent_id = '11'

1 Query select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '12'

1 Query select categories_id from categories where parent_id = '12'

1 Query select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '13'

1 Query select categories_id from categories where parent_id = '13'

1 Query select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '14'

1 Query select categories_id from categories where parent_id = '14'

1 Query select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '15'

1 Query select categories_id from categories where parent_id = '15'

1 Query select manufacturers_id, manufacturers_name from manufacturers order by manufacturers_name

1 Query 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 s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and s.status = '1' order by s.specials_date_added DESC limit 9

1 Query select products_name from products_description where products_id = '29' and language_id = '1'

1 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 = '1') 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

1 Query select products_name from products_description where products_id = '3' and language_id = '1'

1 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 = '1') 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

1 Query select products_name from products_description where products_id = '5' and language_id = '1'

1 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 = '1') 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

1 Query select products_name from products_description where products_id = '6' and language_id = '1'

1 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 = '1') 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

1 Query select products_name from products_description where products_id = '16' and language_id = '1'

1 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 = '1') 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

1 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

1 Query 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 = '12' and pd.products_id = p.products_id and pd.language_id = '1'

1 Query select specials_new_products_price from specials where products_id = '12' and status = '1'

1 Query select products_id, products_price, products_tax_class_id, products_weight from products where products_id = '12'

1 Query select products_model from products where products_id = '12'

1 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 = '1') 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

1 Query select specials_new_products_price from specials where products_id = '12' and status = '1'

1 Query select amount from coupon_gv_customer where customer_id = '2'

1 Query select distinct op.products_id from orders o, orders_products op, products p where o.customers_id = '2' and o.orders_id = op.orders_id and op.products_id = p.products_id and p.products_status = '1' group by products_id order by o.date_purchased desc limit 6

1 Query select products_id, products_name from products_description where products_id in (26,3,2,12,27) and language_id = '1' order by products_name

1 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

1 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' order by r.reviews_id desc limit 10

1 Query select substring(reviews_text, 1, 60) as reviews_text from reviews_description where reviews_id = '1' and languages_id = '1'

1 Query select startdate, counter from counter

1 Query update counter set counter = '646'

041202 13:30:53 1 Query select banners_id, banners_title, banners_image, banners_html_text from banners where status = '1' and banners_group = '468x50'

1 Query select count(*) as total from sessions where sesskey = 'b8b059a74042b8b21735833ccdce09d5'

1 Query update sessions set expiry = '1101948893', value = 'cart|O:12:\"shoppingcart\":7:{s:8:\"contents\";a:1:{i:12;a:1:{s:3:\"qty\";s:1:\"1\";}}s:5:\"total\";d:39.99000000000000198951966012828052043914794921875;s:6:\"weight\";d:7;s:6:\"cartID\";N;s:12:\"content_type\";b:0;s:13:\"total_virtual\";d:39.99000000000000198951966012828052043914794921875;s:14:\"weight_virtual\";d:21;}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:0:{}}customer_id|s:1:\"2\";customer_default_address_id|s:1:\"2\";customer_first_name|s:9:\"adgtvhbcd\";customer_country_id|s:1:\"1\";customer_zone_id|s:1:\"0\";' where sesskey = 'b8b059a74042b8b21735833ccdce09d5'

♥Vger · December 2, 2004

If you look at index.php you'll find that it checks a lot of parameters. Other pages may not check so many, and some just in single digits.

Vger

ilok · December 2, 2004

After searching for the location of the sqls, tep_count_products_in_category seem to generate 40 of the 100 queries.

I hope the site won't die when it goes live....

UPDATE:

looks like http://www.oscommerce.com/community/contributions,2417

can get rid of 9 more redundent sql....

that leaves 50 to go...

December 2, 2004

1. Turn off category counts

2. Use the cache system for boxes

3. Install the tax class (which you have already done)

4. Modify other parts of the store to use EXPLICIT JOINS to pull product name

...instantly down to 20 queries or less

ilok · December 2, 2004

1. Turn off category counts
2. Use the cache system for boxes

3. Install the tax class (which you have already done)

4. Modify other parts of the store to use EXPLICIT JOINS to pull product name

...instantly down to 20 queries or less

<{POST_SNAPBACK}>

What is the cache system for boxes?

and for number 4, which sql queries are you refering to?

I was hoping to be able to have the category counts... maybe I can try and create a cache for the count like in the tax class...

Harald Ponce de Leon · December 2, 2004

Actually, modifying queries to return product names ("joins") might slow things down instead of speeding them up when there are a lot of products in the database.

Having one query to get product related data and another for product names will rise the number of queries made, but helps on performance.

I've written a short update regarding performance in CVS on my blog:

http://blogs.oscommerce.com/hpdl/index.php...howentry&eid=42

December 2, 2004

Harald,

I have found that clients with a large products table garner faster performance and lower overhead by using explicit joins in combination with unique multi-column indexes (of course paying attention to left most prefixing).

A 10,000 row table scan is never faster than a 1 row direct access.

100 sql queries for index.php?

Recommended Posts

ilok

Link to comment

Share on other sites

♥Vger

Link to comment

Share on other sites

ilok

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

ilok

Link to comment

Share on other sites

Harald Ponce de Leon

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Archived

Browse

Activity