Detailed Explanation of Common JOIN Scenarios in the EXPLAIN TYPE Column

This article, part of an ongoing series, explains the EXPLAIN TYPE column in MySQL execution plans, focusing on common JOIN scenarios and how they influence query performance.

A sample table t1 is created with a composite primary key (f0, f1) and 100,000 rows:

CREATE TABLE `t1` (
  `f0` int NOT NULL,
  `f1` int NOT NULL,
  `r1` int DEFAULT NULL,
  `r2` int DEFAULT NULL,
  `r3` int DEFAULT NULL,
  `log_date` date DEFAULT NULL,
  PRIMARY KEY (`f0`,`f1`)
) ENGINE=InnoDB;

1. type: const

When both join columns are constant values, the optimizer chooses type=const , scanning the table using the primary key and returning a single row. Example:

SQL 1: SELECT * FROM t1 WHERE f0=110 AND f1=778;

Execution plan shows type: const , key: PRIMARY , rows: 1 .

id: 1
select_type: SIMPLE
table: t1
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const,const
rows: 1
filtered: 100.00

2. type: eq_ref

Used for a join between two tables where the join columns are the full primary (or unique) key of the referenced table, guaranteeing at most one matching row. Example:

SQL 2: SELECT * FROM t1 JOIN t2 USING (f0, f1);

The plan for t1 shows type: eq_ref , key: PRIMARY , rows: 1 .

id: 1
select_type: SIMPLE
table: t1
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: ytt.t2.f0, ytt.t2.f1
rows: 1
filtered: 100.00

3. type: ref

When the join key is a non‑unique index, the optimizer uses type=ref . This scenario should be avoided if possible, or the join condition should be refined to reduce row count. Example:

SQL 3: SELECT * FROM t1 a JOIN t2 b USING (r1);

Plan shows type: ref , key: idx_r1 , rows: 19838 .

id: 1
select_type: SIMPLE
table: b
type: ref
possible_keys: idx_r1
key: idx_r1
key_len: 5
ref: ytt.a.r1
rows: 19838
filtered: 100.00

4. type: range

A range scan is used when the condition defines a value interval. Example:

SQL 4: SELECT * FROM t1 WHERE f0 < 120;

The plan shows type: range , key: PRIMARY , rows: 93 . By converting the range to an equivalent constant condition ( f0=110 ), the optimizer can switch to type=ref , halving the cost (18.93 → 9.62).

SQL 4 cost: 18.93 rows: 93
SQL 5 cost: 9.62 rows: 93

5. type: index

An index‑only scan (covering index) is reported as type=index . Example:

SQL 6: SELECT r1 FROM t1 LIMIT 10;

Even though only column r1 is needed, MySQL scans all 106,313 rows because there is no ordering clause. Adding ORDER BY r1 allows early termination:

SQL 7: SELECT r1 FROM t1 ORDER BY r1 LIMIT 10;

SQL 6 rows scanned: 106313
SQL 7 rows scanned: 10

The article concludes that understanding the EXPLAIN TYPE values helps developers write more efficient JOIN queries and encourages readers to follow the next part of the series.