Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Slow site caused by too many options?


tobybailey

Recommended Posts

We have a reasonably busy oscommerce site on a dedicated server (which also handles our email). Recently, certain product pages in particular seem to be loading rather slowly (say 6 or 7 seconds over a fast connection). It seems to be mainly or entirely products with options that are the problem.

 

We have well over 1000 products and several hundred of those have options. These are mainly knitting yarns and the options are colours with shade numbers so each option value relates only to a single product.

 

As a consequence of this the database tables:

 

products_options_values;

products_options_values_to_products_options;

products_attributes,

 

each have around 3500 records.

 

I should add that the built in MS2.2 options editing is useless for our purposes and so I edit the options with a tool of my own. This has been working fine for years and so I imagine it is doing the right thing, unless there is something very subtle about how the options system is supposed to operate.

 

Is it conceivable that pulling out the 5-40 options from the database for these yarns is significantly slowing the web server?

 

 

I would be grateful for any insight on this.

 

Toby

Link to comment
Share on other sites

To find information on speeding up the database retrieval, search the forums for

 

A Store Speed Optimisation In Progress

 

Some other links are: Optimize categories box, tep_get_category_tree optimization

 

And additionally for the front-end: Optimize tep_get_tax_rate() method

 

Contributions 2093 & 2575 may also be of interest.

 

Enjoy

Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile

 

Virus Threat Scanner

My Contributions

Basic install answers.

Click here for Contributions / Add Ons.

UK your site.

Site Move.

Basic design info.

 

For links mentioned in old answers that are no longer here follow this link Useful Threads.

 

If this post was useful, click the Like This button over there ======>>>>>.

Link to comment
Share on other sites

I had a similar problem the other day. Here's what I did.

 

Restart the mysql server with logging enabled. Tail -f the logfile and point your browser at one of the problem pages. Determine which query is causing the problem (taking a long time to respond).

 

Take this query and open a mysql prompt from the command line. Run the query with the 'EXPLAIN' keyword before it, i.e.

 

EXPLAIN SELECT * from products....

 

The output from this command will tell you about your query...what indexes are used, etc. Read the following URL for some good information about the EXPLAIN query:

 

http://www.databasejournal.com/features/my...cle.php/1382791

 

I found that my query was required to traverse over 10,000 records and was using filesort and temporary as the means of reaching the result. According to the website above, this is bad. So, I found a place to add an index and added that index to my WHERE clause. Now it works 100% better.

 

There may be better ways to do this...but this is how I did it :)

 

Good luck!

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...