Boosting performance with covering indexes!

Covering indexes can speed up your queries a lot, how much will depend on if  your are CPU or disk bound. For those who are not familiar with covering indexes,  a covering index is a special type of index where the index itself contains all required data fields in statement. This is especially important for InnoDB which have a clustered primary key, and all secondary keys will contain the primary key at leaf node, using covering indexes means one less look-up fetching data which normally leads to less random I/O.

So, lets see how much performance improvements we can get by using covering indexes. Firstly we need to fire up a MySQL instance, I opted for a installed MySQL 5.6.12 with default settings and imported a copy of the world database 

 Next step is to create test table `big` from table City in world database and boost it up with a few more rows, see below.

mysql> source /home/ted/src/world_innodb.sql
mysql> create table big like City;
mysql> insert into big select * from City;
mysql> insert into big select NULL,Name,CountryCode,District,Population from big;
... run stmt above 5-6 times until table contains above 100.000 rows...
mysql> analyze table big;

Now we have a table big with a few 100.000 rows, my table contains just above 260.000 rows.

mysql> SELECT COUNT(*) FROM big;
+----------+
| COUNT(*) |
+----------+
|   261056 |
+----------+

Structure of table big is shown below. We have a secondary index on column CountryCode and this index will be used in our test query.

Create Table: CREATE TABLE `big` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`)
) ENGINE=InnoDB

Our test query for this purpose will be:

SELECT CountryCode, SUM(Population) FROM big GROUP BY CountryCode;

Running this query 10 times and picking out the average (on my laptop) resulted in average time of 0.28 seconds. All data is fitting in memory, no disk activity during execution of statement.

Next step, lets drop our old index and add a new  covering index on table big:

mysql> ALTER TABLE big DROP INDEX CountryCode;
mysql> ALTER TABLE big ADD INDEX cc_pop (CountryCode, Population);

And re-run test query, this time you will see that average query time is down to 0.09 seconds, wow an improvement with 3x compared to not using a covering index.

Conclusion: Using combined indexes can boost performance a lot for your most common queries. I recommend that you analyze your workload and see if you can add combined indexes for some of your more common queries. Be aware that this will consume more space and that your inserts will slow down somewhat but it should be worth investigating for frequent queries.