(1) In one case, order by can be used when the leftmost prefix rule is not met: when the leading column is a constant.
For example, if the index is key my_idx (A, B, C), the query statement is select a, B, c from my_tbl where a = 1 order by B, C. In this way, because the first column of the index is specified as a constant, order by can be used for sorting even if it does not meet the requirements of the leftmost prefix of the index.
(2) MyISAM uses prefix compression to reduce the index volume. By default, only string indexes are compressed. The cost is that some operations become slower.
(3) Duplicate indexes refer to the creation of identical types of indexes on the same column in the same order. For example, a primary key index, unique index, and common index are created for the column ID. Redundant indexes usually occur when a new index is added to a table. If an index (col1, col2) is created and then (col1) is created, this is the redundant index.
(4) The index can lock fewer rows in the query; InnoDB locks the row only when accessing the row, and the index can reduce the number of lines accessed by InnoDB, thus reducing the number of locks, it also reduces performance overhead.
