MySQL 8 Administrator???s Guide

Optimizing indexes

The basic use of indexes is to quickly find the rows with specific column values. If the index is not present, MySQL begins with the first row and reads through the entire table to find all the matching rows. It takes more time, depending on how large a table is. If the index is present for the appropriate columns, MySQL is able to quickly determine the position to seek to in the middle of the data file, without looking at the whole table data.

The following is a list of operations for which MySQL uses indexes:

• To find matching rows, based on a WHERE clause, quickly.
• MySQL uses the index with the smallest number of rows (most selective index) in the case of choosing from multiple indexes to eliminate rows from consideration. ...