EXPLAIN EXTENDED

From Stack Overflow:

I know you can ALTER the column order in MySQL with FIRST and AFTER, but why would you want to bother?

Since good queries explicitly name columns when inserting data, is there really any reason to care what order your columns are in in the table?

Well, there is reason.

MySQL stores some types (out of which VARCHAR is probably the most important) with variable length.

That means that each variable field stored in the row is prepended with its actual length, like in Pascal string type.

Since the rows have no TOC, it means that the engine should traverse all previous values to find out where the required value begins.

If we need the value of the first column (first non-PRIMARY KEY column, of course), the engine will do it once per row.

But if we need the value of, say, 20-th column, the engine should find out the lengths of previous 19 columns and add them together.

Let's create a sample table and see how big the impact is:
Read the rest of this entry »