Contributions
Dramatically Improve Search Speed
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.
check it out at http://www.seekshopping.com/catalog/
 Dramatically Improve Search Speed |
24 Aug 2005 |
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.
check it out at http://www.seekshopping.com/catalog/
Note: Contributions are used at own risk.
