Understanding MySQL EXPLAIN Type Column and JOIN Types: ALL, index_merge, and Optimization Techniques

Author: Yang Taotao, ActionOpenSource technical expert.

This article continues from the previous piece on EXPLAIN TYPE column, focusing on JOIN types in MySQL execution plans.

1. type: ALL

When the type column shows ALL, MySQL performs a full table scan, which is usually inefficient. Example SQL 1 demonstrates this with select * from t1 limit 100 . The EXPLAIN output shows type=ALL, rows equal to total rows, and no indexes used.

By adding an ORDER BY clause that matches the primary key, the type changes to index, reducing scanned rows to the LIMIT value. Example SQL 2: select * from t1 order by f0,f1 limit 100 . The EXPLAIN output now shows type='index' and rows=100.

Cost comparison between SQL 1 and SQL 2 shows a dramatic reduction when using index.

2. type: index_merge

For queries that filter on multiple indexed columns, MySQL can use the INDEX_MERGE algorithm to combine indexes before fetching rows.

SQL 3 (union all of three single‑column scans) is inefficient because it creates a temporary table. Rewriting it as SQL 4 with OR conditions allows INDEX_MERGE: select * from t1 where r1=1 or r2=1 or r3=1 . The EXPLAIN shows type=index_merge, possible_keys=idx_r1,idx_r2,idx_r3.

Similarly, a range query on two columns can use SORT UNION (SQL 5) and the EXPLAIN shows type=index_merge with Extra=Using sort_union.

When the filters are all AND conditions on indexed columns, INDEX_MERGE can perform an intersect (SQL 6). The EXPLAIN shows type=index_merge and Extra=Using intersect.

However, INDEX_MERGE only works for pure UNION or pure INTERSECT patterns; mixed logical expressions like (r1=1 OR r2=1) AND r3=1 (SQL 7) cannot use this optimization.

In summary, understanding the type column and leveraging index_merge can significantly improve query performance, but the optimizer has specific requirements.

For further reading, see previous issues and the linked resources.