MySQL EXPLAIN EXTRA: Interpreting SEMI JOIN Hints and Related Optimizations

The author continues the series on interpreting MySQL's EXPLAIN EXTRA column, focusing on several SEMI JOIN related hints and other optimization notes.

1. SEMI JOIN Extra Hints

FirstMatch : Indicates that the outer query only needs to find the first matching row in the inner query, typical for EXISTS subqueries.

Example:

localhost:ytt>desc select count(*) from t1 where exists (select * from y1 limit 10)\G
*************************** 1. row ***************************
        id: 1
  select_type: SIMPLE
        table: y1
   partitions: NULL
        type: index
possible_keys: NULL
          key: id
      key_len: 8
        ref: NULL
       rows: 461756
  filtered: 100.00
        Extra: Using index; FirstMatch
*************************** 2. row ***************************
...

Start temporary, End temporary : MySQL treats the SEMI JOIN as an INNER JOIN, builds a temporary table with a primary key to deduplicate rows, then joins the outer table.

Example:

localhost:ytt>desc select * from t1 a where (a.f0,a.f1) in (select b.r1,b.r2 from t1 b)\G
*************************** 1. row ***************************
        id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
        type: ALL
possible_keys: idx_r1,idx_r2
          key: NULL
      key_len: NULL
        ref: NULL
       rows: 101700
  filtered: 100.00
        Extra: Using where; Start temporary
*************************** 2. row ***************************
        id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
        type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
        ref: ytt.b.r1,ytt.b.r2
       rows: 1
  filtered: 100.00
        Extra: End temporary
...

LooseScan : Similar to the loose index scan used for GROUP BY . The optimizer scans a covering index once, groups by the indexed column, and only the first row of each group is used for the outer query.

Example (tables b and a join on r1 using LooseScan):

localhost:ytt>desc select * from t1 a where r1 in (select r1 from t2 b)\G
*************************** 1. row ***************************
        id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
        type: index
possible_keys: idx_r1
          key: idx_r1
      key_len: 5
        ref: NULL
       rows: 1000
  filtered: 97.50
        Extra: Using where; Using index; LooseScan
...

2. Range checked for each record

This hint appears when MySQL cannot use a pure index scan (e.g., due to data‑type or collation mismatches) and falls back to a hybrid approach that is faster than a full table scan but slower than an index scan.

Example where the join columns have different character sets:

localhost:ytt>desc select a.* from y1 a left join y1_sub b on binary a.r1 = b.r1\G
*************************** 1. row ***************************
        id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
        type: index
possible_keys: NULL
          key: idx_r1
      key_len: 43
        ref: NULL
       rows: 461740
  filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
        id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
        type: ALL
possible_keys: idx_r1
          key: NULL
        ref: NULL
       rows: 100316
  filtered: 100.00
        Extra: Range checked for each record (index map: 0x8)
...

The article ends by noting that further analysis of the EXTRA column will continue in the next issue.