A table in MySQL supports multiple indexes.
The optimizer chooses indexes to find an optimal execution plan and to execute statements with the least cost.
The number of rows scanned in the database is one of the factors that affects the cost of being executed.
The number of lines scanned is not the only criterion. The optimizer also determines whether to use temporary tables, whether to sort, and so on.
In MySQL, there are two ways to store indexes, which can be selected by setting the value of innodb_status_persistant.
If this parameter is set to on, statistics will be stored persistently. By default, N is 20 and M is 10.
If this parameter is set to off, statistics are stored only in the memory. By default, N is 8 and M is 16.
N: Data page selected during InnoDB sampling statistics.
M: When the number of changed rows exceeds 1/M, the system automatically triggers index statistics again.
Using a normal index takes into account the cost of returning to the table.
Index selection and processing
1. Use force index to forcibly select an index.
2. Modify the statement to direct MySQL to use the index we expect.
3. Create a more suitable index to provide the optimizer with options, or delete the index that is misused.