2

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.

4
  • Can you test by checking the Created_tmp_tables variable? Mar 6 '14 at 11:23
  • @ypercube Thanks a lot! 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". Now, when I launched an app using this query in its original form (9 ORs) - each check Created_tmp_tables increased only by 1 (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. Conclusion: no, it does not. Mar 6 '14 at 14:11
  • You can submit your tests and conclusion as an answer :) Mar 6 '14 at 14:14
  • @ypercube Thanks again! Need to wait some time to be able to answer my own question with current reputation :) Mar 6 '14 at 14:23
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.