Verifying Index Usage SELECT * FROM test WHERE last_name=’Jones’ OR first_name=’John’; Suppose that you issue the following  SELECT  statement: SELECT * FROM  tbl_name WHERE col1= val1  AND col2= val2 ; If a multiple-column index exists on  col1  and  col2 , the appropriate rows can be fetched directly. If separate single-column indexes exist on  col1  and  col2 , the optimizer attempts to use the Index Merge optimization (see Section 8.2.1.3, “Index Merge Optimization” ), or attempts to find the most restrictive index by deciding which index excludes more rows and using that index to fetch the rows. 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) . MySQL cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index. Suppose that you have the  SELECT  statements shown here: SELECT * FROM  tbl_name  WHERE col1= val1 ; SELECT * FROM  tbl_name  WHERE col1= val1  AND col2= val2 ; SELECT * FROM  tbl_name  WHERE col2= val2 ; SELECT * FROM  tbl_name  WHERE col2= val2  AND col3= val3 ; If an index exists on  (col1, col2, col3) , only the first two queries use the index. The third and fourth queries do involve indexed columns, but do not use an index to perform lookups because  (col2)  and (col2, col3)  are not leftmost prefixes of  (col1, col2, col3) . Always check whether all your queries really use the indexes that you have created in the tables. Use the EXPLAIN  statement, as described in Section 8.8.1, “Optimizing Queries with EXPLAIN” . Storage engines collect statistics about tables for use by the optimizer. Table statistics are based on value groups, where a value group is a set of rows with the same key prefix value. For optimizer purposes, an important statistic is the average value group size. MySQL uses the average value group size in the following ways: As the average value group size for an index increases, the index is less useful for those two purposes because the average number of rows per lookup increases: For the index to be good for optimization purposes, it is best that each index value target a small number of rows in the table. When a given index value yields a large number of rows, the index is less useful and MySQL is less likely to use it. The average value group size is related to table cardinality, which is the number of value groups. The  SHOW INDEX  statement displays a cardinality value based on  N/S , where  N  is the number of rows in the table (...) JOIN  tbl_name  ON  tbl_name . key  =  expr 1720 8.3.7 Verifying Index Usage 8.3.8 InnoDB and MyISAM Index Statistics Collection •To estimate how many rows must be read for each  ref  access •To estimate how many rows a partial join produces, that is, the number of rows produced by an operation of the form