Comparison of B-Tree and Hash Indexes some of those statements modify the table, MySQL may collect statistics. (This may occur for bulk inserts or deletes, or some  ALTER TABLE  statements, for example.) If this happens, the statistics are collected using whatever value  innodb_stats_method  or  myisam_stats_method  has at the time. Thus, if you collect statistics using one method, but the system variable is set to the other method when a table’s statistics are collected automatically later, the other method is used. Understanding the B-tree and hash data structures can help predict how different queries perform on different storage engines that use these data structures in their indexes, particularly for the  MEMORY storage engine that lets you choose B-tree or hash indexes. A B-tree index can be used for column comparisons in expressions that use the  = ,  > ,  >= ,  < ,  <= , or BETWEEN  operators. The index also can be used for  LIKE  comparisons if the argument to  LIKE  is a constant string that does not start with a wildcard character. For example, the following  SELECT  statements use indexes: SELECT * FROM  tbl_name  WHERE  key_col  LIKE ’Patrick%’; SELECT * FROM  tbl_name  WHERE  key_col  LIKE ’Pat%_ck%’; In the first statement, only rows with  ’Patrick’ <=  key_col  < ’Patricl’  are considered. In the second statement, only rows with  ’Pat’ <=  key_col  < ’Pau’  are considered. The following  SELECT  statements do not use indexes: SELECT * FROM  tbl_name  WHERE  key_col  LIKE ’%Patrick%’; SELECT * FROM  tbl_name  WHERE  key_col  LIKE  other_col ; In the first statement, the  LIKE  value begins with a wildcard character. In the second statement, the  LIKE value is not a constant. If you use  ... LIKE ’% string %’  and  string  is longer than three characters, MySQL uses the  Turbo Boyer-Moore algorithm  to initialize the pattern for the string and then uses this pattern to perform the search more quickly. A search using  col_name  IS NULL  employs indexes if  col_name  is indexed. Any index that does not span all  AND  levels in the  WHERE  clause is not used to optimize the query. In other words, to be able to use an index, a prefix of the index must be used in every  AND  group. The following  WHERE  clauses use indexes: ... WHERE  index_part1 =1 AND  index_part2 =2 AND  other_column =3 /*  index  = 1 OR  index  = 2 */ ... WHERE  index =1 OR A=10 AND  index =2 /* optimized like " index_part1 =’hello’" */ 1722 B-Tree Index Characteristics 8.3.9 Comparison of B-Tree and Hash Indexes •There is no way to tell which method was used to generate statistics for a given table. •These variables apply only to  InnoDB  and  MyISAM  tables. Other storage engines have only one method for collecting table statistics. Usually it is closer to the  nulls_equal  method. •B-Tree Index Characteristics •Hash Index Characteristics