Fun MySQL fact of the day: got it covered

Yesterday, we considered a data access pattern that relied on a secondary index to find a MySQL row that needed to be read at least 3 times: twice by InnoDB and once by MySQL. I also promised we'd find a way to get that number down to just one read: a 67% reduction in effort.

To accomplish this epic feat, we may leverage a "covering index optimisation," which is implemented by MySQL and a storage engine to prevent having to perform a lookup on the primary key. Sometimes, you'll hear terms like "covering index" or "covered index", and while those are both completely valid terms, I try to avoid them since there really is no such thing as a covering index: any b+tree secondary index can be used as a covering index just as simply as not. And why is that? Let's go back to our on-going example index of (a,b): as you may recall, each record in an InnoDB secondary index stores the values for a and b (in addition to the primary key), and, like we reasoned about yesterday, there's no reason why we can't use those fields when querying from the secondary index.

So, rather simply, to reduce our row read rate by at least 67%, we "just" need to CREATE INDEX abc_idx ON my_table(a, b, c), and our SELECT a, b, c WHERE a = 1 AND b = 1 AND c = 1 query can now avoid having to perform a supplemental lookup by primary key in PRIMARY and need not be additionally filtered in MySQL. All because InnoDB doesn't have to do any work. Because (a,b,c) still has a prefix of (a,b), you may even wish to consider and evaluate dropping the original (a,b) query, as (a,b,c) can still cover queries for (a,b).

This may have seemed obvious, but it's easy to forget and not always apparent. The biggest thing to remember is that there is no such a thing as a covering index, but if you're in the good habit of EXPLAINing all of your new queries, you can see whether or not MySQL has optimised your query to leverage "covering index optimisation" by looking for Using index in the Extra column. And if your query isn't Using index, you may want to find out why.