Fun MySQL fact of the day: secondary responsibilities

We've still got more ground to cover on secondary indices, but first, we're going to take a quick detour to understand a little bit more about how MySQL interacts with the storage engines.

When MySQL receives a query, it will parse it and formulate an execution plan, which may consist of any number of optimisations that we'll get into in later weeks. For now, we only want to understand what happens after MySQL chooses an index from which to read data. Once MySQL has chosen an index, MySQL will pass along the selection criteria to a storage engine, like InnoDB, and ask for the first row matching a specific set of criteria. If we consider InnoDB, that "match" of "specific criteria" is the prefix match of an index. This can be a partial or full left-to-right match, but the match must be sequential from the first byte of the first field. We'll discuss this part a bit more next week, but for now, you can consider that an index of (a,b,c) can match (a), (a,b), or (a,b,c), or any number of bytes from a through c (for example, the first 2 bytes of a timestamp).

When InnoDB finds a match, it will save its current index position, return the record as a MySQL row back to MySQL, and then return flow control back to MySQL. Then, when MySQL gets the row, it will evaluate that the entire row does, indeed, match the query's entire WHERE clause. If not, MySQL will discard it, but if it does match completely, MySQL will retain it. At this point, MySQL will ask the storage engine for the next record with a prefix match, and so on, and so on. You'll know this is happening when the Extra field of your query plan (did you know you can EXPLAIN SELECT ...?) contains Using where. This means MySQL is tasked with performing additional filtering on the rows InnoDB returns.