B 树索引可用于在使用 =、 >、 >=、 <、 <=或BETWEEN运算符的表达式中进行列比较。LIKE 如果参数 LIKE是不以通配符开头的常量字符串，索引也可用于比较。例如，以下SELECT语句使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

在第一个语句中，只考虑带有的行。在第二条语句中，只考虑带有的行。 'Patrick' <= key_col < 'Patricl''Pat' <= key_col < 'Pau'

以下SELECT语句不使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

在第一条语句中，LIKE 值以通配符开头。在第二个语句中，LIKE值不是常量。

如果使用and 超过三个字符，MySQL 使用Turbo Boyer-Moore 算法初始化字符串的模式，然后使用该模式更快地执行搜索。 ... LIKE '%string%'string

col_name IS NULL如果已编入索引，则使用使用索引 的搜索col_name。

任何未跨越子句中所有 AND级别的 索引都WHERE不会用于优化查询。换句话说，为了能够使用索引，必须在每个 AND组中使用索引的前缀。

以下WHERE子句使用索引：

... 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'" */
... 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;

这些WHERE子句 不使用索引：

    /* 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 indexes have somewhat different characteristics from those just discussed: