OpenCart Community

Posted: **Sun Aug 11, 2013 7:13 pm**

MySQL speed improvement. Add more indexes.

Attached queries add an indexes to most used tables in version 1.5.4.
Original tables contain very low number of indexes.

Add provided indexes save for data in tables. Also you can remove added indexes any time.

Some indexes are really helps, some of them added just in case.
I not test performance all of them, only a few of them. Anyway it safe for data.

You will get better speed but you will spend a little bit additional storage for indexes on your HDD.

Code: Select all

-- additional indexes

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 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_attribute ADD INDEX attribute_id ( attribute_id );
ALTER TABLE product_attribute ADD INDEX language_id ( language_id );

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

I'll no provide any support. I just place information want helps improve my friend shop performance. It was OpenCart 1.5.4
I think that most indexes are fit for most versions.

IMHO: We need ask developers to add some of these indexes to default installation from next build.