Search Very Slow

Hi everybody,

last week my website very slow but now fixed buy new dedicated server cpanel very fast now.

now start second problem

search very slow not slow very bad,

admin panel search any product only 1 second , front panel more than 40 second why?

i need help thank you very much

please check my website

http://www.madlime.com

it's going to be because of your tags. You might be able to help it a bit by indexing the tag field, but still the way tags are implemented I don't think the will ever really be efficient for searching.

The following SQL helped one of my sites a lot
site was using 1.5.0.5

I concur with Xsecrets that the tags is most likely your problem.
I was having the same issue myself on my development version (v1.5.1.1) of opencart and we have 2-5 tags on most of our 1200 products. It was taking almost a minute to do a search on localhost server!!!
After adding further indexes into the tag table this dropped searches to around 15 seconds but I still found this unnacceptable so took out the searching of tags altogether and the searches now typically only take around .01-.02 of a second instead!!!

uksitebuilder wrote:The following SQL helped one of my sites a lot

Code: Select all

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` ) ;

site was using 1.5.0.5

LOL what you couldn't find an index all command?

My search mod hits a lot of columns hehe

Although I may have overdone it on some

I can't believe Daniel still hasn't indexed query and keyword in the url_alias table.

uksitebuilder wrote:The following SQL helped one of my sites a lot

Code: Select all

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` ) ;

site was using 1.5.0.5

how to add it ?

via PHPMyAdmin in your hosting control panel

uksitebuilder wrote:via PHPMyAdmin in your hosting control panel

thank you.

Hey that's awesome, thanks uksitebuilder, certainly seems to work!

I like it better than my solution since you don't have to modify code

Only trouble is, am getting more results than really necessary now since tags are working again, oh well guess i'll have to add some more filters to search results page

Hi, UKSB.
Is that the same as in your search mod? If so, it seems to be working for me. I was unsure how to use it, but this post helped. I have 2 tables. The 1st one didn't have all these table id's available, but the second one did.

cantykiwi wrote:I concur with Xsecrets that the tags is most likely your problem.
I was having the same issue myself on my development version (v1.5.1.1) of opencart and we have 2-5 tags on most of our 1200 products. It was taking almost a minute to do a search on localhost server!!!
After adding further indexes into the tag table this dropped searches to around 15 seconds but I still found this unnacceptable so took out the searching of tags altogether and the searches now typically only take around .01-.02 of a second instead!!!

and how to totally remove tag searching? Please explain

I did this and now items don't show up when you click on them they just show white blank page no error or anything im using 1.5.3 any ideas?

The above SQL Commands will do little if anything for 1.5.1.3 because Daniel finally got around to adding indexes to the tag table

Not sure why you are getting a blank page - check your server error logs to see what error you are getting.

Actually search function searches only inside "tags" field which limits the searching capability. You can't tell what he customer is going to write in search box and therefore you cannot add every thing under tags.
That was the reason when I got my searching function tweaked a bit and now it searches insie tags, names, vategories and SKU. After implementing this change now my every search returns results.