Dynamic range access (aka “Range checked for each record” since that is what EXPLAIN will say in the ‘Extra’ column) is one of the big mysteries to MySQL users. The reason is that it is used for queries that are less common, such as queries with non-equality join predicates. The following query is an example; it finds the number of messages sent since the user was last logged in:
EXPLAIN SELECT user.user_id, COUNT(message.id) FROM message, user WHERE message.send_date >= user.last_activity GROUP BY user.user_id;
However, although such join predicates are less common, it doesn’t mean that the dynamic range access method is not important.
How the dynamic range access method works
It is easier to explain dynamic range access if we consider a “normal” join first, so let’s take a look at this[Read more...]