When evaluating a conditional expression, MySQL decides what join type the expression has. (Again: despite the word “join”, this applies for all conditional expressions, not just join expressions. A term like “access type” would be clearer.) These are the documented join types, in order from best to worst:
system
: a system table which is a constant tableconst
: a constant tableeq_ref
: a unique or primary index with an equality relationref
: an index with an equality relation, where the index value cannot beNULL
ref_or_null
: an index with an equality relation, where it is possible for the index value to beNULL
range
: an index with a relation such asBETWEEN
,IN
,>=
,LIKE
, and so on.index
: a sequential scan on an indexALL
: a sequential scan of the entire table
See:
/sql/sql_select.h
, enum
join_type{}
. Notice that there are a few other
(undocumented) join types too, for subqueries.
The optimizer can use the join type to pick a driver expression. For example, consider this query:
SELECT *
FROM Table1
WHERE indexed_column = 5 AND unindexed_column = 6
Since indexed_column
has a better join
type, it is more likely to be the driver. You'll see various
exceptions as this description proceeds, but this is a simple
first rule.
What is significant about a driver? Consider that there are two execution paths for the query:
The “Bad” Execution Plan:
Read every row in the table. (This is called a
sequential scan of
Table1
or simply table
scan.) For each row, examine the values in
indexed_column
and in
unindexed_column
, to see if they meet the
conditions.
The “Good” Execution Plan:
Via the index, look up the rows which have
indexed_column
= 5. (This is called an
indexed search.) For each row, examine
the value in unindexed_column to see if it meets the
condition.
An indexed search generally involves fewer accesses than a
sequential scan, and far fewer accesses if the table is large
but the index is unique. That is why it is better to access
with the “good” execution plan, and why it is
often good to choose indexed_column
as the
driver.