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. 

•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. 

## 8.3.9 Comparison of B-Tree and Hash Indexes 

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. 

## B-Tree Index Characteristics 

•B-Tree Index Characteristics 

•Hash Index Characteristics 

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’" */ 

