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 ... 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; /*  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 These  WHERE  clauses do  not  use indexes: Use of Index Extensions Hash indexes have somewhat different characteristics from those just discussed: InnoDB  automatically extends each secondary index by appending the primary key columns to it. Consider this table definition: 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. 1723 8.3.10 Use of Index Extensions 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; •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.) •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. •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.) •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.