Re: Why not use index

[Øystein Grøvlen]

Hi,

MySQL will only use index lookup (so-called ref access) when join condition is a conjunction of equality predicates. Otherwise, if join condition contains OR or range predicates (e.g., t1.x < t2.y), it may decide whether to use index dynamically for every look-up. Then, whether the index is used or not, will be based on the estimated number of rows that will have to be fetched for the given value.

In your case, for example, it will, for each row in t1, ask the storage engine for an estimate of how many rows in t2 will have the value of either t1.mid or t1.sid. If the total is less than 15% or so of the total number of rows in t2, it will normally use the index for lookup. When this approach is used, EXPLAIN will show "Range checked for each record" in the Extra column. Does it not do that in your case?

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway