From what I understand about multi-column indexes, they are only useful if you're using columns starting from the left and not skipping any. As in, when you have an index for (a, b, c), you can query on a, a, b, or a, b, c.
But today I found out that when there's an index (BTREE on an InnoDB table) on:
some_varchar, some_bigint, other_varchar
I can query:
SELECT MAX(some_bigint) FROM the_table
and the plan for it says:
Extra: Using index
This seems to disagree with the docs. It's also confusing since the key is set, but possible_keys isn't.
How does this work in practice? If the key is ordered by some_varchar first, (or a prefix of it) how can MySQL get a MAX of the second column from it?
(a guess would be that MySQL collects some extra information about all columns in an index, but if that's true - is it possible to see it directly?)
Why does MAX work on a column in the middle of an index
nr: #1 dodano: 2018-01-03 01:01
My understanding about the indexes was correct, but the understanding of what Using index means was wrong.
Using index doesn't necessarily mean that the value was accessed via a fast lookup. It simply means that the row data was not accessed. When the type is index and the Extra is Using index, it still means that the whole index is being scanned: