Some conditions can work with indexes, but over a (possibly
wide) range of keys. These are known as
range conditions, and are most often
encountered with expressions involving these operators:
>, >=, <, <=, IN, LIKE, BETWEEN
To the optimizer, this expression:
column1 IN (1,2,3)
is the same as this one:
column1 = 1 OR column1 = 2 OR column1 = 3
and MySQL treats them the same — there is no need to change IN to OR for a query, or vice versa.
The optimizer will use an index (range search) for
column1 LIKE 'x%'
but not for
column1 LIKE '%x'
That is, there is no range search if the first character in the pattern is a wildcard.
To the optimizer,
column1 BETWEEN 5 AND 7
is the same as this expression
column1 >= 5 AND column1 <= 7
and again, MySQL treats both expressions the same.
The optimizer may change a Range
to an
ALL
join type if a condition would examine
too many index keys. Such a change is particularly likely for
<
and >
conditions
and multiple-level secondary indexes.
See: (for MyISAM
indexes) /myisam/mi_range.c
,
mi_records_in_range()
.