Optimization and Indexes 8.3 Optimization and Indexes 8.3.1 How MySQL Uses Indexes 1715 •To find the  MIN()  or  MAX()  value for a specific indexed column  key_col . This is optimized by a preprocessor that checks whether you are using  WHERE  key_part_N  =  constant  on all key parts •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. • 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) . For more information, see Section 8.3.6, “Multiple-Column Indexes” . •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 (the most selective index). •To find the rows matching a  WHERE  clause quickly. The best way to improve the performance of  SELECT  operations is to create indexes on one or more of the columns that are tested in the query. The index entries act like pointers to the table rows, allowing the query to quickly determine which rows match a condition in the  WHERE  clause, and retrieve the other column values for those rows. All MySQL data types can be indexed. Although it can be tempting to create an indexes for every possible column used in a query, unnecessary indexes waste space and waste time for MySQL to determine which indexes to use. Indexes also add to the cost of inserts, updates, and deletes because each index must be updated. You must find the right balance to achieve fast queries using the optimal set of indexes. Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. This is much faster than reading every row sequentially. Most MySQL indexes ( PRIMARY KEY ,  UNIQUE ,  INDEX , and  FULLTEXT ) are stored in B-trees. Exceptions: Indexes on spatial data types use R-trees;  MEMORY  tables also support hash indexes;  InnoDB  uses inverted lists for  FULLTEXT  indexes. In general, indexes are used as described in the following discussion. Characteristics specific to hash indexes (as used in  MEMORY  tables) are described in Section 8.3.9, “Comparison of B-Tree and Hash Indexes” . MySQL uses indexes for these operations: For comparisons between nonbinary string columns, both columns should use the same character set. For example, comparing a  utf8  column with a  latin1  column precludes use of an index. Comparison of dissimilar columns (comparing a string column to a temporal or numeric column, for example) may prevent use of indexes if values cannot be compared directly without conversion. For a given value such as  1  in the numeric column, it might compare equal to any number of values in the string column such as  ’1’ ,  ’ 1’ ,  ’00001’ , or  ’01.e1’ . This rules out use of any indexes for the string column.