Fun MySQL fact of the day: the table is a lie
Over the course of the last several weeks, you may have caught me telling partial truths or using "weird" or inconsistent words when talking about MySQL and InnoDB. And now, before we move on to the rest of this week, it's time to clear a few things up.
Let's start with 3 fun facts (or harsh truths, depending on your world view):
- There is no such a thing as a table. In InnoDB, all we have are indices; b+trees to be precise. Any time you think "table", what you really mean is "
PRIMARY
index", where all data are sorted in ascending order of primary key. - There is no such thing as a row. In InnoDB, all we have are records inside indices. A record is a structured thing that contains all the "user data" in addition to a some metadata.
- There is such a thing as an index. In InnoDB, an "index" is still an "index", but what we usually refer to as an "index" is a "secondary index" (also (usually) a b+tree) composed of, you guessed it, sorted records that point back to records in the
PRIMARY
index.
Of course, "table", "index", and "row" are still useful terms, but when we're talking about how InnoDB works, we'll mostly want to stick to "indices" (of both the primary and secondary varietal) and "records".