Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Can you test by checking the Created_tmp_tables variable? –  ypercube Mar 6 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. –  Sergii Golubev Mar 6 at 14:11
    
You can submit your tests and conclusion as an answer :) –  ypercube Mar 6 at 14:14
    
@ypercube Thanks again! Need to wait some time to be able to answer my own question with current reputation :) –  Sergii Golubev Mar 6 at 14:23

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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