Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Dramatically Increase Search Speed


mattmcb

Recommended Posts

I finally figured out what was bogging down my database when searching for products. I had to post this. I have about 220,000 active products in my store seekshopping.com... parse time is at the bottom if anyone wants to checkout the performance.

 

I have optimized so many queries and continue to monitor and do so. Whe I started, my search queries were about 20-40 seconds... that's a life time on the net. Including all the variables in the search url with help decrease query speed by about 50%...

 

keywords=wars&search_in_description=1&categories_id=&inc_subcat=1&medium=&pfrom=&pto=&dfrom=&dto=

 

Now I'm down to about 11 or 12 seconds for a search which is still not good enough. 6 seconds should be the maximum. Just remove products_model from the search string. Here is the code to change in advanced_search_result.php...

 

Comment this out:
//$where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%'";

Simply change to this:
$where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%'";

 

I hope this helps someone out. My queries are running under half a second and my search page parses in about 2 seconds. That's a 1500% imporvement.

Link to comment
Share on other sites

You could look at using an index on the products table so you still have the ability to search on the model as well... that's what I have done.

 

The model was indexed... the entire time. From 40 seconds all the way down to less then a second. I have no need to search the model.

Link to comment
Share on other sites

Well actually the indexing doesn't do a thing due to the structure of the query (on second look).

 

The database only uses an index on equality matching. This is my experience from the Oracle database so I am expecting MySQL would be the same, it would have to be. Correct me if I am wrong someone!

 

I have 4000 products on my site and I also have it searching by model and it's very fast but I guess having over 200k products means a lot more work for the database to do.

 

In saying that I doubt many people would be in a position where they have 200k+ products?

 

 

As a test I ran explain plans on these:

 

EXPLAIN SELECT *

FROM `products_description`

WHERE products_name = 'test'

 

table type possible_keys key key_len ref rows Extra

products_description ref products_name products_name 255 const 1 Using where

 

 

 

EXPLAIN SELECT *

FROM `products_description`

WHERE products_name

LIKE '%test%'

 

table type possible_keys key key_len ref rows Extra

products_description ALL NULL NULL NULL NULL 3024 Using where

 

 

 

 

So yes NO index is used...

Dan

Link to comment
Share on other sites

Well actually the indexing doesn't do a thing due to the structure of the query (on second look).

 

The database only uses an index on equality matching. This is my experience from the Oracle database so I am expecting MySQL would be the same, it would have to be. Correct me if I am wrong someone!

 

I have 4000 products on my site and I also have it searching by model and it's very fast but I guess having over 200k products means a lot more work for the database to do.

 

In saying that I doubt many people would be in a position where they have 200k+ products?

As a test I ran explain plans on these:

 

EXPLAIN SELECT *

FROM `products_description`

WHERE products_name = 'test'

 

table  type  possible_keys  key  key_len  ref  rows  Extra 

products_description ref products_name products_name 255 const 1 Using where

EXPLAIN SELECT *

FROM `products_description`

WHERE products_name

LIKE '%test%'

 

table  type  possible_keys  key  key_len  ref  rows  Extra 

products_description ALL NULL NULL NULL NULL 3024 Using where

So yes NO index is used...

 

it will only use an index if using like 'test%'

Treasurer MFC

Link to comment
Share on other sites

  • 1 year later...

Good stuff! I have 115,000 products and the search was dragging. I took out the manufacturer string and the model and it made a huge improvement.

 

How is your product listing speed, my categories load fast, but when i click on the category to display the products, the product listing is a tad bit slow, not terrible by any means, but not up to par with the rest of the site.

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...