7.2.2.1 Determining the Join Type

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 table

• const: a constant table

• eq_ref: a unique or primary index with an equality relation

• ref: an index with an equality relation, where the index value cannot be NULL

• ref_or_null: an index with an equality relation, where it is possible for the index value to be NULL

• range: an index with a relation such as BETWEEN, IN, >=, LIKE, and so on.

• index: a sequential scan on an index

• ALL: 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.