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.
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` ) ;
see -> http://www.opencart.com/forum/viewtopic ... 31&t=39194
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
Code: Select all
ALTER TABLE `product_tag` ADD INDEX ( `product_id` ) ;
ALTER TABLE `product_tag` ADD INDEX ( `tag` ) ;
-Ryan
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` ) ;
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.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.
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
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
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.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.
v1.5.5.1
Unfortuantely, that is incorrect.bluelayer wrote:that's wrong, if you add more products they will not be indexed, search will not find them. tested by me
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.
ok... so why i have products with months that don't appear on search results until i rerun the sql command?uksitebuilder wrote:Unfortuantely, that is incorrect.bluelayer wrote:that's wrong, if you add more products they will not be indexed, search will not find them. tested by me
indexes are a permanent fixture to the columns in the table they are assigned to.
v1.5.5.1
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
fixed, thank youuksitebuilder 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
v1.5.5.1
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` ) ;
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
Users browsing this forum: No registered users and 295 guests