Understanding Common MySQL EXPLAIN EXTRA Hints: Using filesort, Using temporary, and Using join buffer

This article is the final installment of a series that explains the meanings of the EXTRA column in MySQL EXPLAIN output, focusing on three frequently‑seen hints.

1 Using filesort

Using filesort indicates that MySQL must perform an explicit sort operation because the query orders by a column that is not covered by an index. It typically appears when a query forces ordering on a non‑indexed column.

Example:

(mysql)>desc select * from t1 order by r1 limit 10\G
*************************** 1. row ***************************
    id: 1
  select_type: SIMPLE
    table: t1
  partitions: NULL
    type: ALL
possible_keys: NULL
      key: NULL
  key_len: NULL
      ref: NULL
    rows: 57918
  filtered: 100.00
    Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

Optimization is not always required; if the sort fits in memory and concurrency is low, it may be acceptable. However, when memory is insufficient and sorting spills to disk, adding an index on the sort column or rewriting the query to sort by the primary key becomes essential.

2 Using temporary

Using temporary means MySQL creates an implicit temporary table to hold intermediate results, which commonly occurs when a GROUP BY clause lacks a suitable index.

Example:

(mysql)>desc select count(log_date) from t1 group by r1\G
*************************** 1. row ***************************
    id: 1
  select_type: SIMPLE
    table: t1
  partitions: NULL
    type: ALL
... 
    rows: 57918
  filtered: 100.00
    Extra: Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)

In MySQL 8.0, GROUP BY no longer adds an implicit ORDER BY , so adding an index on the grouping column removes the Using temporary hint:

(mysql)>desc select count(log_date) from t1 group by r1\G
*************************** 1. row ***************************
    id: 1
  select_type: SIMPLE
    table: t1
  partitions: NULL
    type: index
possible_keys: idx_r1
      key: idx_r1
  key_len: 5
      ref: NULL
    rows: 57918
  filtered: 100.00
    Extra: NULL
1 row in set, 1 warning (0.01 sec)

More complex queries may require additional optimizations beyond simply adding an index.

3 Using join buffer

Using join buffer appears when MySQL cannot use an index for a join key and must allocate a memory buffer. Three variants exist:

Using join buffer (Block Nested Loop)

This is the slowest join method, a plain nested‑loop join that scans the larger table for each row of the smaller table.

Example:

(mysql)>desc select * from t1 a join t2 b using(r2)\G
... 
    Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)

Using join buffer (Batched Key Access)

Also known as BKA, this algorithm leverages an index on the join key of the driven table to convert random I/O into sequential I/O.

Example (join key r2 indexed):

(mysql)>desc select * from t1 a join t2 b using(r2)\G
... 
    Extra: Using where; Using join buffer (Batched Key Access)
2 rows in set, 1 warning (0.01 sec)

Using join buffer (hash join)

Available in MySQL 8.0+, this replaces the block nested loop with a hash join when the join key of the driven table is indexed.

Example (join key r3 without index):

(mysql)>desc select * from t1 join t2 using(r3)\G
... 
    Extra: Using where; Using join buffer (hash join)
2 rows in set, 1 warning (0.00 sec)

In recent MySQL versions, hash joins are the default for such scenarios, and BKA is used when the join key has an index.

These examples cover the most common EXPLAIN EXTRA hints; readers are encouraged to follow the series for deeper optimization techniques.