MySQL index hit rule

Change to: 77574600

First, make it clear: why use a federated index?

For the query statement "Select e.* from E WHERE e.e1=1 and e.e3=2" involves two columns, this time we generally take a federated index (E1, E3), instead of two single-column indexes, This is because a query statement should tend to use only one index for the MySQL optimizer's relationship, and even if you have two indexes, he only uses one; The federated index is faster than a single-column index on a single basis;

Let's talk about the usage rules for federated indexes and the conditions that will not hit the federated index

Examples are shown below. First create the table:
CREATE TABLE E (E1 int, E2 VARCHAR (9), E3 int, PRIMARY KEY (E1, E3));
This establishes a federated index: E1,E3

Test data

INSERT into E
(E1, E2, E3)
VALUES (1, ' AA ', 2);

Triggering a federated index is conditional:
1. Use all index keys of the federated index to trigger the use of the index.
Example: SELECT e.* from E WHERE e.e1=1 and e.e3=2

2. Use the prefix part index key of the federated index, such as "Key_part_1 <op> constants", to trigger the use of the index.
Example: SELECT e.* from E WHERE e.e1=1

3. Use part of the index key, but not the prefix part of the federated index, such as "Key_part_2 <op> constants", not triggering the use of the index.
Example: SELECT e.* from E WHERE e.e3=1

4. Use all the index keys of the federated Index, but the index key is not an and operation and cannot trigger the use of the index.
For example: SELECT e.* from E WHERE e.e3=2 OR e.e1=1

The results can be seen through the explain test.

MySQL index hit rule