MySQL: does Index Merge Sort-Union access algorithm use temporary table?

Question

My question is about index_merge optimization in MySQL.

I have a query with a set of range conditions connected with OR. EXPLAIN of the query shows that index_merge method is used and the Extra column contains only:

'Using sort_union({list of merged indexes}); Using where'

The question is: "Does this query use a temporary table?"

In the 8.2.1.4.3 The Index Merge Sort-Union Access Algorithm section of the MySQL manual it is written that:

The difference between the sort-union algorithm and the union algorithm is that the sort-union algorithm must first fetch row IDs for all rows and sort them before returning any rows.

That looks like the RDBMS is going to use a temporary table. But in the section 8.4.4 How MySQL Uses Internal Temporary Tables we can read:

To determine whether a query requires a temporary table, use EXPLAIN and check the Extra column to see whether it says Using temporary (see Section 8.8.1, “Optimizing Queries with EXPLAIN”). EXPLAIN will not necessarily say Using temporary for derived or materialized temporary tables.

But there is no 'Using temporary' in the Extra column in this case. There are no subqueries or UNIONs in the query. The query looks like:

SELECT *
FROM A
JOIN B USING(pk)
WHERE (B.c1 IN ({list_of_values}) OR B.c2 IN ({list_of_values}) OR ... OR B.c9 IN ({list_of_values})) AND {some_other_conditions};

There are 9 single-column INDEXes on B.c1, B.c2,... , B.c9 columns.

Answer 1

I've performed an experiment: rewrote the query from using 9 ORs in a single query to a UNION DISTINCT of 9 subqueries. As expected, EXPLAIN shows for a rewritten query in the last row: select_type = "UNION RESULT", Extra = "Using temporary". Such change was done to have a query that surely uses temporary table to compare with.

Now, when I launched an app using this query in its original form (9 ORs) - each check Created_tmp_tables increased only by 1 (from the manual: invocation of the SHOW STATUS increments the global Created_tmp_tables value by 1).

When I launched the app using rewritten query Created_tmp_tables grows very fast.

I think this experiment is enough to show that temporary tables are not being created in the case of index_merge Sort-Union.

Conclusion: no, it does not.