Fun MySQL fact of the day: so many index reads

On Friday, we discussed the separate roles that MySQL and InnoDB have while querying data. Specifically, we looked at how InnoDB reads data for MySQL and how MySQL may or may not be tasked with additional filtering of the data. We also briefly discussed how MySQL may choose to use a secondary index to answer a query.

Considering what we learned on Friday, you may have spent the weekend wondering, "if I SELECT a, b, c FROM my_table WHERE a=1 AND b=1 AND c=1 and MySQL chooses an index (a,b), where does c come form?" Well, when InnoDB is tasked with finding data for this query, it is given a list of fields that need to be returned. After finding a record in our index, (a,b), but before returning it to MySQL as a row, InnoDB will decide if all of the queried fields have data. In this case, we have data for a and b, but not c, so InnoDB will do a supplemental read from PRIMARY by primary key, which, as you recall, is stored in each and every secondary index record. This means that each row returned to MySQL requires a minimum of 2 reads: one on the secondary index and one on the PRIMARY index.

Much as we discussed on Friday, MySQL will then be tasked with filtering out the rows where c!=1. This means that each row has been evaluated not once (secondary index search), not twice (primary key lookup), but three times (MySQL filtering). Surely there's something we can do to get that number down?! Stay tuned to Fun MySQL Facts Of The Day and find out!