Slow site caused by too many options?

[tobybailey]

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

[♥geoffreywalton]

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

[krnl]

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!

[chipshot]

also check out Chemo's cache something or other (speed up database queries)

[tobybailey]

Many thanks for those three useful responses. I will do some work on the matter along the lines suggested as soon as I have some time.

 

Toby