1. To find the rows matching a WHERE
clause quickly.
2.To eliminate rows from consideration. If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows。
3.If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (col1, col2, col3)
, you have indexed search capabilities on (col1)
, (col1, col2)
, and (col1, col2, col3)
4.To retrieve rows from other tables when performing joins. MySQL can use indexes on columns more efficiently if they are declared as the same type and size. In this context,
VARCHAR
and CHAR
are considered the same if they are declared as the same size. For example, VARCHAR(10)
and CHAR(10)
are the same size, but VARCHAR(10)
and CHAR(15)
are not.