Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Optimising DB Queries


Guest

Recommended Posts

We have a store that currently has about 8000 products and I want to optimise our db queries in order to speed up the site and keep our ISP happy (shared server).

 

I've found that a lot of queries seem to go through each of the 8000 rows when it only needs a fraction of this.

 

The following is the MySQL explain of the query for selecting 20 products for the specials infobox.

explain 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 20

 

Which results in:

 

table | type | possible_keys | key | key_len | ref | rows | Extra

-----------------------------------------------------------------------------------------

p |ALL | PRIMARY | NULL | NULL | NULL | 8738 | where used; Using temporary; Using filesort

s | ref | products_id | products_id | 4 | p.products_id | 1 | where used

pd | eq_ref PRIMARY | PRIMARY | 8 | s.products_id,const | 1

 

 

So its going through the entire products table, sorting by specials_date_added and returning only the latest 20.

 

There must be a more efficient way to do this, what if there were 1,000,000 products - it would be a huge problem.

 

Any ideas?

 

Perhaps I'm just missing an index somewhere?

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...