In August 2014 I started an article on improving the speed and performance of your OpenCart store by improving your OpenCart database indexing, shockingly it appears OpenCart did not set required indexes that are vital for larger OpenCart stores.
MySQL uses indexes to speed up queries, every join or search using an index is cached so that the data can be retrieved without overhead next time it is called, more importantly an overall cache is created for the indexes making searching much faster. Strangely though OpenCart does have a very good database design, so it is bewildering that they failed to implement such a vital feature into their tables, fortunately though it is not hard to setup the required indexes throughout your OpenCart store.
Below is the original query I built which will improve the performance of your OpenCart MySQL database, this is especially noticeable on large OpenCart stores of 50 products or more!
However I have now put together a small script that will perform all the required indexes and also some other very important database tasks, this includes changing the tables to use INNODB instead of MYISM, as MYISM is old and outdated in today's world. The script will also change some field types to draw even more performance from your database.
OpenCart 1.5.x Database Tuner
Purchase the script below for an instant download, place the file in the root of your OpenCart installation and run the script from your browser. All settings will be drawn from your OpenCart installation and the script will optimize all the tables, indexes and field types.
Download OpenCart 1.5.x Database Tuner
Click the link below to purchase this software.
Original Tuning Code
Below is my original tuning code, which to some degree will still increase performance for your OpenCart store, however I would suggest purchasing my new tuner software as it will do much more than the below code and you have no risk of messing it up!
-- OpenCart MySQL Performance Improvement Payload - By Deano.me
ALTER TABLE category ADD INDEX status ( status );
ALTER TABLE category_to_store ADD INDEX store_id ( store_id ); -- required. most impotant
ALTER TABLE category_description ADD INDEX language_id ( language_id ); -- required. most impotant
ALTER TABLE product_attribute ADD INDEX attribute_id ( attribute_id );
ALTER TABLE product_attribute ADD INDEX language_id ( language_id );
ALTER TABLE information ADD INDEX status ( status );
ALTER TABLE information ADD INDEX sort_order ( sort_order );
ALTER TABLE information_to_store ADD INDEX store_id ( store_id ); -- required. most impotant.
ALTER TABLE product ADD INDEX status ( status );
ALTER TABLE product_description ADD INDEX language_id ( language_id );
ALTER TABLE product_image ADD INDEX product_id ( product_id ); -- required. most impotant.
ALTER TABLE product_image ADD INDEX sort_order ( sort_order );
ALTER TABLE product_reward ADD INDEX product_id ( product_id ); -- required. most impotant.
ALTER TABLE product_reward ADD INDEX customer_group_id ( customer_group_id );
ALTER TABLE product_option ADD INDEX product_id (product_id); -- required. most impotant.
ALTER TABLE product_option ADD INDEX option_id (option_id);
ALTER TABLE product_option_value ADD INDEX product_option_id (product_option_id);
ALTER TABLE product_option_value ADD INDEX product_id (product_id); -- required. most impotant.
ALTER TABLE product_option_value ADD INDEX option_id (option_id);
ALTER TABLE product_option_value ADD INDEX option_value_id (option_value_id);
ALTER TABLE product_option_value ADD INDEX subtract (subtract);
ALTER TABLE product_option_value ADD INDEX quantity (quantity);
ALTER TABLE product_to_category ADD INDEX category_id ( category_id ); -- required. most impotant.
ALTER TABLE product_to_store ADD INDEX store_id ( store_id ); -- required. most impotant.
ALTER TABLE setting ADD INDEX store_id ( store_id );
ALTER TABLE setting ADD INDEX `group` ( `group` );
ALTER TABLE setting ADD INDEX `key` ( `key` );
ALTER TABLE setting ADD INDEX serialized ( serialized );
ALTER TABLE url_alias ADD INDEX query ( query ); -- required. most impotant.
Twitter
Facebook
Linkedin