[SOLVED] OpenCart 1.5 Site Search Very Slow

Posted by Paul on November 13, 2018

We came across a problem recently where an OpenCart 1.5 site with no extensions and the default theme was often running very slowly for no apparently reason. Most of the time it was running super-fast (0.1 second page loads) but every 5-10 minutes it would slow right down and pages would take 20+ seconds to load. The slownes seemed fairly random so we couldn't trace it to a scheduled cron job.

The latency looked like this, with no obvious patterns:

After a lot of Googling and investigating various things, we started recording the time taken for each request to the server. We noticed that requests to the search page were taking 20+ seconds and doing a search slowed down the entire site.

Eventually I was brought back to one of the very first things I tried - adding indexes to the database tables as described in this OpenCart forum post:
https://forum.opencart.com/viewtopic.php?t=39759

It turns out that in my first attempt to add indexes, one of the key tables used in searches was missed. The SQL query to run (can be done from phpMyAdmin) to properly index all tables needed in OpenCart 1.5 is:



ALTER TABLE `category` ADD INDEX ( `parent_id` );
ALTER TABLE `category` ADD INDEX ( `top` );
ALTER TABLE `category` ADD INDEX ( `sort_order` );
ALTER TABLE `category` ADD INDEX ( `status` );
ALTER TABLE `option` ADD INDEX ( `sort_order` );
ALTER TABLE `option_description` ADD INDEX ( `name` );
ALTER TABLE `option_value` ADD INDEX ( `option_id` );
ALTER TABLE `option_value_description` ADD INDEX ( `option_id` );
ALTER TABLE `order` ADD INDEX ( `customer_id` );
ALTER TABLE `product` ADD INDEX ( `model` );
ALTER TABLE `product` ADD INDEX ( `sku` );
ALTER TABLE `product` ADD INDEX ( `upc` );
ALTER TABLE `product` ADD INDEX ( `manufacturer_id` );
ALTER TABLE `product` ADD INDEX ( `sort_order` );
ALTER TABLE `product` ADD INDEX ( `status` );
ALTER TABLE `product_option` ADD INDEX ( `option_id` );
ALTER TABLE `product_option_value` ADD INDEX ( `product_option_id` );
ALTER TABLE `product_option_value` ADD INDEX ( `product_id` );
ALTER TABLE `product_option_value` ADD INDEX ( `option_id` );
ALTER TABLE `product_option_value` ADD INDEX ( `option_value_id` );
ALTER TABLE `product_tag` ADD INDEX ( `product_id` );
ALTER TABLE `product_tag` ADD INDEX ( `tag` );
ALTER TABLE `url_alias` ADD INDEX ( `query` );
ALTER TABLE `url_alias` ADD INDEX ( `keyword` );
ALTER TABLE `user` ADD INDEX ( `username` );
ALTER TABLE `user` ADD INDEX ( `password` );
ALTER TABLE `user` ADD INDEX ( `email` );

(Please note you may need to add the table prefix of oc_ or whatever you used when you installed OpenCart.)

After running that, we can see a much lower latency on all pages of about 0.1 (ignore the average in the screenshot which has been affected by previous slowness!):

Has this article helped you? Or is your site still slow? Let us know in the comments!