Better Search? Too Slow for Results

[oscommercenewbie]

I have a website with over 90k products on a dedicated server. Everything runs smoothly but the search is deathly slow.

 

I have optimized as this indicates but still deathly slow. I know of no customer that would wait around a good 20 seconds for results.

 

http://www.oscommerce.com/forums/index.php?showtopic=113785&hl=

 

Any thoughts? I have searched throgh the contributions - nothing really solves this problem.

[Guest]

90,000 products will have such side effects. IMHO you could create separate dbases for the products (derived from some main products categories) and then fully customize the oscommerce core tree to connect to the right dbase that represents a products category.

You do not have to segment it too much so dropping it to few thousand products per dbase should do.

 

Your master dbase will include everything else including categories names, so once you pickup the name and match it with the cpath you connect to the 2ndary dbase. Its a lot of work I think but should take care of the speed problems.

[FalseDawn]

90,000 is nothing. I can run search queries on millions of rows that return in sub-second times.

 

You either do not have optimal indexes configured on the appropriate tables (joined column in multi-table queries MUST be indexed)

 

OR

 

Your Mysql configuration file needs tweaking - as you are on a dedicated server, you should be able to do this (or get someone to do it) - there are parameters that control the amount of RAM to use for things like key joins etc - if this is too low (and it sounds like it is), you will get disk swapping, which will kill a query.

 

Edit. Do a google for something like "tweaking mysql.cnf" for more info

[oscommercenewbie]

Hi False Dawn,

 

Looking for tweaking info now. Thank you for putting me on the right track.

 

As for --

 

optimal indexes configured on the appropriate tables (joined column in multi-table queries MUST be indexed)

 

Can you explain a little more in-depth? I am currently indexing all of the tables I think are appropriate products, product descriptions, categories, categories, descriptions, specials (I have 90k specials) etc. Anything I am missing specifically for the search results?

 

All products have a prod id, 3 - 4 sentence description, price and special, no known speed issues except searching for a product.

 

I have had 140k with no problems anywhere else - just the search.

 

Think its the tweaking issue?

 

Thank you both for your assistance.

[FalseDawn]

If I recall correctly, I think that the problem could also be due to the particular query you are running. Offhand, I think that the standard OSC search uses non-optimizeable expressions in the SQL, eg:

 

like '%expression%'

 

This happens when you search within product descriptions, and no amount of indexing is going to help.

 

The query optimizer has no way of optimizing these sort of searches, and they basically force an entire table scan - not good for 90k products, unless you allocate a load of memory for the entire table to fit into ram - again, this depends on your mysql.cnf settings.

 

SQL optimizing is a pretty tricky art and all depends on what is most important - making one query run faster often slows down other queries. It is a balancing act and far too broad a subject to explain in a few paragraphs.