OpenCart: Improve MySQL performance

OpenCart is an awesome eCommerce system, however there are a few shortcomings due to the apparent lack of knowledge the OpenCart development team have (or perhaps the incompleteness of the OpenCart software?).

Unfortunately the database structure of OpenCart does not implement MySQL indexing as it should, the database design however is extremely well implemented and this brings down what ultimately is a great use of MySQL and database design.

Fortunately it is very simple to assign indexing to tables in MySQL and all you need is a little foresight on how indexing works and where best to assign indexes.

Below I have put together a SQL query which will assign indexing to all tables I feel could benefit from indexing, it will make your system faster and optimize OpenCart for large databases and/or heavy usage.

All you have to do is run the following query on your MySQL database:

-- 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.

Author: Dean WilliamsI'm a Web Developer, Graphics Designer and Gamer, this is my personal site which provides PHP programming advice, hints and tips

Twitter Facebook Linkedin