union, where and indices
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