Post by gowtham_glx » Tue Aug 30, 2011 2:48 pm

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.
Last edited by gowtham_glx on Tue Aug 30, 2011 3:51 pm, edited 1 time in total.

New member

Posts

Joined
Fri Jul 08, 2011 6:24 pm

Post by uksitebuilder » Tue Aug 30, 2011 2:54 pm

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

User avatar
Guru Member

Posts

Joined
Thu Jun 09, 2011 11:37 pm
Location - United Kindgom

Post by gowtham_glx » Tue Aug 30, 2011 3:50 pm

Wow !!! you are Awesome!!!
Now it takes only 5 sec to search a product :)

Thank you very much...

New member

Posts

Joined
Fri Jul 08, 2011 6:24 pm

Post by uksitebuilder » Tue Aug 30, 2011 3:52 pm

You may also want to remove category counts

see -> http://www.opencart.com/forum/viewtopic ... 31&t=39194

User avatar
Guru Member

Posts

Joined
Thu Jun 09, 2011 11:37 pm
Location - United Kindgom

Post by spitos » Thu Sep 08, 2011 10:02 am

Thanks Simon, sorted the search speed issues out perfectly

Image
Google Analytics Expert - Advanced e-commerce tracking, Product & options reporting, transaction/conversion reporting, Google Adwords conversion & profit reporting, goal & funnel reporting, event tracking, site search tracking, multi-store compatibility, EU Cookie Law compliance and works with any theme or checkout! Easy vqmod install. Get it here


User avatar
Active Member

Posts

Joined
Mon May 23, 2011 6:19 pm
Location - UK

Post by rph » Wed Sep 28, 2011 2:35 am

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.

-Ryan


rph
Expert Member

Posts

Joined
Fri Jan 08, 2010 5:05 am
Location - Lincoln, Nebraska

Post by marcusa007 » Fri Oct 21, 2011 11:10 pm

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

Newbie

Posts

Joined
Thu Oct 13, 2011 8:14 am

Post by rph » Sat Oct 22, 2011 8:47 am

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.

-Ryan


rph
Expert Member

Posts

Joined
Fri Jan 08, 2010 5:05 am
Location - Lincoln, Nebraska

Post by bluelayer » Tue Nov 15, 2011 7:30 am

we need to run this "code" every time we add a new product, is there any better solution?

v1.5.5.1


New member

Posts

Joined
Sun Oct 17, 2010 8:11 pm

Post by rph » Tue Nov 15, 2011 9:41 am

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.

-Ryan


rph
Expert Member

Posts

Joined
Fri Jan 08, 2010 5:05 am
Location - Lincoln, Nebraska

Post by bluelayer » Tue Nov 15, 2011 8:48 pm

that's wrong, if you add more products they will not be indexed, search will not find them. tested by me

v1.5.5.1


New member

Posts

Joined
Sun Oct 17, 2010 8:11 pm

Post by spikeachu » Tue Nov 15, 2011 9:54 pm

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.

Wedding Invitations and Stationery by Love2print

Commercial Mods
Product Colours on Category Page
Cardsave Direct Gateway
Clear Cache
Promotional Watermarks on Images
Multiple Category / Product Templates ** Popular **
Log Failed Login Attempts
Display Eligible Coupons with Products
Twitter Feeds

Have I helped you out or saved you some time? Please donate


Active Member

Posts

Joined
Fri Mar 12, 2010 6:31 am

Post by bluelayer » Tue Nov 15, 2011 9:59 pm

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.

v1.5.5.1


New member

Posts

Joined
Sun Oct 17, 2010 8:11 pm

Post by uksitebuilder » Tue Nov 15, 2011 10:00 pm

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.

User avatar
Guru Member

Posts

Joined
Thu Jun 09, 2011 11:37 pm
Location - United Kindgom

Post by bluelayer » Tue Nov 15, 2011 10:07 pm

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?

v1.5.5.1


New member

Posts

Joined
Sun Oct 17, 2010 8:11 pm

Post by uksitebuilder » Tue Nov 15, 2011 10:23 pm

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

User avatar
Guru Member

Posts

Joined
Thu Jun 09, 2011 11:37 pm
Location - United Kindgom

Post by bluelayer » Tue Nov 15, 2011 10:44 pm

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 :)
Last edited by bluelayer on Fri May 18, 2012 9:46 pm, edited 1 time in total.

v1.5.5.1


New member

Posts

Joined
Sun Oct 17, 2010 8:11 pm

Post by nodric » Tue Jan 10, 2012 7:01 am

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

The Best 4x4xfar


Active Member

Posts

Joined
Wed Jul 06, 2011 10:39 pm

Post by Ang22 » Sat Mar 03, 2012 5:01 pm

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

Newbie

Posts

Joined
Sat Dec 10, 2011 5:49 am

Post by uksitebuilder » Sat Mar 03, 2012 5:13 pm

If your tables are prefixed, you should add the prefix to each table name in the code posted above

User avatar
Guru Member

Posts

Joined
Thu Jun 09, 2011 11:37 pm
Location - United Kindgom
Who is online

Users browsing this forum: Bing [Bot] and 118 guests