... WHERE index_part1 =’hello’ AND index_part3 =5 /* Can use index on index1  but not on index2  or index3  */ ... WHERE index1 =1 AND index2 =2 OR index1 =3 AND index3 =3; 

These WHERE  clauses do not  use indexes: 

/* index_part1  is not used */ ... WHERE index_part2 =1 AND index_part3 =2 /* Index is not used in both parts of the WHERE clause */ ... WHERE index =1 OR A=10 /* No index spans all rows */ ... WHERE index_part1 =1 OR index_part2 =10 

Sometimes MySQL does not use an index, even if one is available. One circumstance under which this occurs is when the optimizer estimates that using the index would require MySQL to access a very large percentage of the rows in the table. (In this case, a table scan is likely to be much faster because it requires fewer seeks.) However, if such a query uses LIMIT  to retrieve only some of the rows, MySQL uses an index anyway, because it can much more quickly find the few rows to return in the result. 

## Hash Index Characteristics 

Hash indexes have somewhat different characteristics from those just discussed: 

•They are used only for equality comparisons that use the =  or <=>  operators (but are very  fast). They are not used for comparison operators such as <  that find a range of values. Systems that rely on this type of single-value lookup are known as “key-value stores” ; to use MySQL for such applications, use hash indexes wherever possible. 

•The optimizer cannot use a hash index to speed up ORDER BY  operations. (This type of index cannot be used to search for the next entry in order.) 

•MySQL cannot determine approximately how many rows there are between two values (this is used by the range optimizer to decide which index to use). This may affect some queries if you change a MyISAM or InnoDB  table to a hash-indexed MEMORY  table. 

•Only whole keys can be used to search for a row. (With a B-tree index, any leftmost prefix of the key can be used to find rows.) 

## 8.3.10 Use of Index Extensions 

InnoDB  automatically extends each secondary index by appending the primary key columns to it. Consider this table definition: 

CREATE TABLE t1 ( i1 INT NOT NULL DEFAULT 0, i2 INT NOT NULL DEFAULT 0, d DATE DEFAULT NULL, PRIMARY KEY (i1, i2), INDEX k_d (d) ) ENGINE = InnoDB; 

This table defines the primary key on columns (i1, i2) . It also defines a secondary index k_d  on column (d) , but internally InnoDB  extends this index and treats it as columns (d, i1, i2) . 

The optimizer takes into account the primary key columns of the extended secondary index when determining how and whether to use that index. This can result in more efficient query execution plans and better performance. 

