SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND
age='17'; SELECT peopleid FROM people WHERE firstname='Mike' AND
lastname='Sullivan'; SELECT peopleid FROM people WHERE firstname='Mike'; The
following queries cannot use the index at all: SELECT peopleid FROM people WHERE
lastname='Sullivan'; SELECT peopleid FROM people WHERE age='17'; SELECT peopleid
FROM people WHERE lastname='Sullivan' AND age='17';
SELECT people.age, ##不使用索引 town.name ##不使用索引 FROM people LEFT JOIN town ON
people.townid=town.townid ##考虑使用索引 WHERE firstname='Mike' ##考虑使用索引 AND
lastname='Sullivan' ##考虑使用索引
那么,我们是否可以简单地认为应该索引WHERE子句和join子句中出现的每一个列呢?差不多如此,但并不完全。我们还必须考虑到对列进行比较的操作符类型。MySQL只有对以下操作符才使用索引:<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE。可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符(%或者_)开头的情形。例如,“SELECT peopleid FROM people WHERE firstname LIKE 'Mich%';”这个查询将使用索引,但“SELECT peopleid FROM people WHERE firstname LIKE '%ike';”这个查询不会使用索引。