MySQL Forums
Forum List  »  Optimizer & Parser

union, where and indices
Posted by: Mattia Belletti
Date: March 12, 2010 09:21AM

I have a case in which a UNION is badly optimized, even though the optimization itself should be really simple to derive automatically. Here is an example, which I'm testing on a (Debian stable) MySQL 5.0 (5.0.51a-24+lenny3-log):

CREATE TABLE first (id INT, INDEX index_id (id));
CREATE TABLE second (id INT, INDEX index_id (id));
INSERT INTO first VALUES (1), (2), (3);
INSERT INTO second VALUES (4), (5), (6);
mysql> EXPLAIN SELECT * FROM ((SELECT * FROM first) UNION (SELECT * FROM second)) AS u WHERE id = 2;
+----+--------------+------------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+-------+---------------+----------+---------+------+------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 6 | Using where |
| 2 | DERIVED | first | index | NULL | index_id | 5 | NULL | 3 | Using index |
| 3 | UNION | second | index | NULL | index_id | 5 | NULL | 3 | Using index |
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+-------+---------------+----------+---------+------+------+-------------+

As you can see, all 6 rows are examined by the query, even though an index for the 'id' field exists in each single subquery of the union. Indeed, if I explicitely move the WHERE condition inside, I get a much better result:

mysql> EXPLAIN SELECT * FROM ((SELECT * FROM first WHERE id = 2) UNION (SELECT * FROM second WHERE id = 2)) AS u;
+----+--------------+------------+--------+---------------+----------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+--------+---------------+----------+---------+------+------+--------------------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | first | ref | index_id | index_id | 5 | | 1 | Using where; Using index |
| 3 | UNION | second | ref | index_id | index_id | 5 | | 1 | Using where; Using index |
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+--------+---------------+----------+---------+------+------+--------------------------+

Now it is clear that the indices are used. So, MySQL, instead of filtering the results and then merge the result, first merge the results and then filter them, on a temporary table which now contains no index at all.

All good and well, if I could re-formulate the queries by myself so that I move the WHERE condition inside the subqueries as for the second example, but I cannot, since the union is indeed a view in my case - and I cannot change at runtime the view definition (also because, as you can guess, the WHERE condition can change).

Have you any suggestion to give about how can I "force" MySQL to perform this kind of optimization (filter-than-merge rather than merge-than-filter), given the condition that the UNION is a view?

Thanks in advance for any reply,

- Mattia Belletti

Options: ReplyQuote


Subject
Views
Written By
Posted
union, where and indices
9416
March 12, 2010 09:21AM
2200
March 13, 2010 11:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.