Why is MAX working on a column in the middle of the index

Question

From what I understand about multi-column indexes, they are only useful if you use columns starting on the left and not skipping any. As with if you have an index for (a, b, c), you can request at a, a, bor a, b, c.

But today I found out that when there is an index ( BTREEin the InnoDB table) on:

some_varchar, some_bigint, other_varchar

I can request:

SELECT MAX(some_bigint) FROM the_table

and the plan for him says:

           id: 1
  select_type: SIMPLE
        table: the_table
         type: index
possible_keys: NULL
          key: index_some_varchar_some_bigint_other_varchar
      key_len: 175
          ref: NULL
         rows: 1
        Extra: Using index

This does not seem to be consistent with the docs . It is also confusing as it is keyinstalled, but is possible_keysnot.

How does it work in practice? If the key is ordered first some_varchar(or its prefix), how can MySQL get a MAXsecond column from it ?

(, MySQL , - ?)

Answer 1

, , Using index, .

Using index , . , . type - index, Extra - Using index, , :

:

ALL, , .

MAX, , :

           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Select tables optimized away