Page 1 of 2
[SOLVED] search is very slow
Posted: Tue Aug 30, 2011 2:48 pm
by gowtham_glx
Hi
I am using open cart version 1.5.0.3. in VPS server.
The issue is, initially the website had no problems. when i completed the website with 100 categories and 3500 products, the website became little slow. the main issue is, for searching a product the website takes 40-50 secs.
i didn't get a clear solution for this in forum.
So please help me regarding this.
Thanks in advance.
Re: search is very slow
Posted: Tue Aug 30, 2011 2:54 pm
by uksitebuilder
Run these commands in PHPMyAdmin
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` ) ;
Re: search is very slow
Posted: Tue Aug 30, 2011 3:50 pm
by gowtham_glx
Wow !!! you are Awesome!!!
Now it takes only 5 sec to search a product
Thank you very much...
Re: [SOLVED] search is very slow
Posted: Tue Aug 30, 2011 3:52 pm
by uksitebuilder
Re: [SOLVED] search is very slow
Posted: Thu Sep 08, 2011 10:02 am
by spitos
Thanks Simon, sorted the search speed issues out perfectly
Re: [SOLVED] search is very slow
Posted: Wed Sep 28, 2011 2:35 am
by rph
FYI this appears to be 100% related to product tags so you should be able to get away with just:
Code: Select all
ALTER TABLE `product_tag` ADD INDEX ( `product_id` ) ;
ALTER TABLE `product_tag` ADD INDEX ( `tag` ) ;
This dropped search query time from 4.5 seconds down to 0.08 seconds.
Re: search is very slow
Posted: Fri Oct 21, 2011 11:10 pm
by marcusa007
rph, I first ran the two lines recommended by you and ran a search and the time was still awful and then I ran the rest of the commands as recommended by uksitebuilder and the search was very fast. I have 10000+ products in hundreds of categories and speed is a huge issue. I recommend running all the commands below since that is the way it really made a difference. Thanks for the help.
uksitebuilder wrote:Run these commands in PHPMyAdmin
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` ) ;
Re: search is very slow
Posted: Sat Oct 22, 2011 8:47 am
by rph
marcusa007 wrote:rph, I first ran the two lines recommended by you and ran a search and the time was still awful and then I ran the rest of the commands as recommended by uksitebuilder and the search was very fast.
Remember that OpenCart's cache comes into play when you search for something a second time. To better benchmark you need to directly run the SQL queries.
There are a number of good suggestions in there but several aren't used for any queries, aren't used for search queries or have low cardinality so have limited use.
Re: [SOLVED] search is very slow
Posted: Tue Nov 15, 2011 7:30 am
by bluelayer
we need to run this "code" every time we add a new product, is there any better solution?
Re: [SOLVED] search is very slow
Posted: Tue Nov 15, 2011 9:41 am
by rph
bluelayer wrote:we need to run this "code" every time we add a new product, is there any better solution?
Indexes are a permanent change. You do not need to re-run it.
Re: [SOLVED] search is very slow
Posted: Tue Nov 15, 2011 8:48 pm
by bluelayer
that's wrong, if you add more products they will not be indexed, search will not find them. tested by me
Re: [SOLVED] search is very slow
Posted: Tue Nov 15, 2011 9:54 pm
by spikeachu
I think cache expires after an hour. Alternatively, if you need the cache clearing right away, I have a mod in my footer that will clear it for you.
Re: [SOLVED] search is very slow
Posted: Tue Nov 15, 2011 9:59 pm
by bluelayer
spikeachu wrote:I think cache expires after an hour. Alternatively, if you need the cache clearing right away, I have a mod in my footer that will clear it for you.
cache expiration don't apply here, i have products with months (so more than one hour)that don't appear on search results until i rerun the sql command.
Re: [SOLVED] search is very slow
Posted: Tue Nov 15, 2011 10:00 pm
by uksitebuilder
bluelayer wrote:that's wrong, if you add more products they will not be indexed, search will not find them. tested by me
Unfortuantely, that is incorrect.
indexes are a permanent fixture to the columns in the table they are assigned to.
reference:
http://www.mysqlfaqs.net/mysql-faqs/Ind ... s-in-MySQL
When new rows are added to the table, the indexed columns get rebuilt.
This will slightly decrease response time when editing/saving a product etc in admin
But those performance hits are far outweighed by the front-end performance increase.
Re: [SOLVED] search is very slow
Posted: Tue Nov 15, 2011 10:07 pm
by bluelayer
uksitebuilder wrote:bluelayer wrote:that's wrong, if you add more products they will not be indexed, search will not find them. tested by me
Unfortuantely, that is incorrect.
indexes are a permanent fixture to the columns in the table they are assigned to.
ok... so why i have products with months that don't appear on search results until i rerun the sql command?
Re: [SOLVED] search is very slow
Posted: Tue Nov 15, 2011 10:23 pm
by uksitebuilder
check your database tables, optimise them where needed/recommended by phpmyadmin is all I can suggest.
If you have a lot of products, then the indexes for them will be huge and it could be that you are running low on disk space for the tables
Please also check that you have not got multiple of the same index on a table, which you may have if you have been repeatedly running the index command/sql
Re: [SOLVED] search is very slow
Posted: Tue Nov 15, 2011 10:44 pm
by bluelayer
uksitebuilder wrote:
Please also check that you have not got multiple of the same index on a table, which you may have if you have been repeatedly running the index command/sql
fixed, thank you
Re: [SOLVED] search is very slow
Posted: Tue Jan 10, 2012 7:01 am
by nodric
If it helps anyone finding this, our search, and general site speed was slowing down now we had grown to +300 categories, and over 1000 products, with over 300 options.
I added most of what Simon suggested (my suggestions are below) and the site is now like lighting! Near instantaneous, and here I was bitching to my host
I should have remembered to be a good DBA!
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` ) ;
Re: [SOLVED] search is very slow
Posted: Sat Mar 03, 2012 5:01 pm
by Ang22
My skill with SQL is quite limited can you provide me with a little bit more info?
I am using bluehost and when I access the phpmyadmin section , I go to the database where my cart is (opn1)
when I click on the "SQL" Tab to run the command I get a #1146-table store_opn1.category" doesn't exist'
Do I just change the code from ALTER TABLE `option_value_description` ADD INDEX ( `option_id` ) ; to something else ?
any help would be great
Thanks
Re: [SOLVED] search is very slow
Posted: Sat Mar 03, 2012 5:13 pm
by uksitebuilder
If your tables are prefixed, you should add the prefix to each table name in the code posted above