0

We are having a issue with our car selector on our website. Currently from my understanding something like this is happening to return results from database

Currently this is what is happening
:

select * from xcart_makes;   // returns all makes


select * from xcart_models
    where makeid = 45;   // returns models for makeid 45


select * from xcart_product_makes
    where makeid = 45 and modelid = 4;   // returns years for that make and model

as there are about ~2,979,081 items

Some Make, Model Year Combos are locking the system up and causing over a 15 second response time. An example to replicate this issue would be going to http://www.ultrarev.com and selecting from the drop box this combo

Chevrolet > Corvette > 2009

That combo will lock the system up, but most others wont.

For example. This combo will pass with little response time

Audi > A3 > 2007

I hope that clears up the issue and can help us to find a solution.

skobaljic
  • 9,379
  • 1
  • 25
  • 51
Anthony Accetturo III
  • 13
  • 8
  • How do you know that the database is the bottleneck? – Mike Sherrill 'Cat Recall' Feb 28 '15 at 13:42
  • No matter if you have so many items, what you are missing (and I am almost 100% sure) are the indexes in your tables. Go on and add index to all columns you address in `where` clausule: `makeid` in `xcart_models`, also in `xcart_product_makes` and `modelid` in `xcart_product_makes`. You should get results in second. – skobaljic Apr 01 '15 at 20:34

0 Answers0