Too many rows examined in product listing query?

[simplesimon4]

This store is modified with a number of contributions including ultimate seo urls, sts templating system, header tags, all manufacturers, and featured sets to name a few. I've installed chemo's page caching, am using caching for the manufacturer's and category boxes, no category count, and have added the index to the specials table, but am getting complaints from the web host and getting entries in the slow query log like this:

 

# Time: 081010 14:15:07

# User@Host: root[root] @ localhost []

# Query_time: 27 Lock_time: 0 Rows_sent: 2 Rows_examined: 23571

select p.products_image, pd.products_name, p.products_id, p.manufacturers_id, 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 from products p, products_description pd, manufacturers m, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '26' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '9' order by pd.products_name limit 0, 20;

 

Looks to be from the product listing. Is the number of rows examined unusually high? I don’t believe this query has been modified in a way that would require a new index. Is there anything obviously wrong? Thanks.

[Guest]

which version of osc are you using? The RC versions introduced an update which added a number of new indexes to improve database performance - if you are running an earlier version it is worth doing the indexes update

[simplesimon4]

which version of osc are you using? The RC versions introduced an update which added a number of new indexes to improve database performance - if you are running an earlier version it is worth doing the indexes update

 

Whoops forgot a chunk of info. MS 2.2 PHP5 and MYSQL4. Is there a patch for the update? I haven't been able to find anything. Thanks for the helpful response.

[Guest]

Download the package from http://www.oscommerce.com/solutions/downloads

 

In the archive there are update instructions.

[simplesimon4]

Download the package from http://www.oscommerce.com/solutions/downloads

 

In the archive there are update instructions.

 

Thanks. I applied the update and it looks like parse time has improved slightly for some pages. Is it unusual for the product listing query to examine on the order of 10,000 table rows for a store with 15,000 products?

[Guest]

try using phpMyAdmin and get mysql to EXPLAIN the query to find out if it could be optimised i.e.

EXPLAIN select p.products_image, pd.products_name, p.products_id, p.manufacturers_id, 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 from products p, products_description pd, manufacturers m, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '26' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '9' order by pd.products_name limit 0, 20;

[simplesimon4]

try using phpMyAdmin and get mysql to EXPLAIN the query to find out if it could be optimised i.e.

EXPLAIN select p.products_image, pd.products_name, p.products_id, p.manufacturers_id, 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 from products p, products_description pd, manufacturers m, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '26' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '9' order by pd.products_name limit 0, 20;

 

Thanks! Worked like a charm. Found a field that needed to be indexed and was causing a full table scan when the query was run. The Explain command allowed me to easily find the problem table, and then test how many rows were being examined by the query after I added the index. 10,000 rows were indeed way too much. Down below 500 now.