opencart/opencart — pull request #986 by chrisatomix (2013-11-07 00:52:06+00:00)
Title: Dramatically boost load performance of products and categories on large stores

Dramatically boost load performance of products and categories on large stores (10,000+ products, 500+ nested categories, product filters and options) due to multi-column indexes, as explained in detail here:

http://bloke.org/php/opencart-is-slow-with-many-categories/
https://github.com/opencart/opencart/issues/177

I have tested these changes across 13 different OpenCart installations, there was a substantial reduction in page load time (even with caching enabled). Furthermore, MySQL load was reduced by an average 15-20% (sometimes more).

As explained in the links above, when a MySQL index has multiple columns, only the first column will perform as an indexed column. The remaining columns will appear un-indexed, unless the first column is also filtered during the query.
For example, if the table has an index on product_id,category_id:

Selecting all categories "WHERE product_id = 123" IS indexed, because product_id comes first in the index.

Selecting all categories "WHERE product_id = 123 AND category_id = 7" IS indexed, because both indexed columns are specified.

Selecting all products "WHERE category_id = 7" is NOT indexed, because the first column in the index is 'product_id' and that has not been specified, which makes the second index useless.

This is all backed up and explained very clearly by the MySQL documentation:
http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

> MySQL cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index. Suppose that you have the SELECT statements shown here:

> ```SQL
SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
```
> If an index exists on (col1, col2, col3), only the first two queries use the index. The third and fourth queries do involve indexed columns, but (col2) and (col2, col3) are not leftmost prefixes of (col1, col2, col3).

There is obviously going to be a minor performance hit when creating new products (as the indexes are updated), however this is so negligible as to be almost unworthy of mention (bulk-adding 10,000 products may take a few seconds longer).