UNION doesn't respect index with ORDER BY? bug?

[Brian Klug]

Hello,

As soon as I add an "order by" clause to my UNION statement, MySQL 4.0.20 starts behaving oddly.

Please look at this SQL statement, plus it's EXPLAIN output:

EXPLAIN
(
SELECT recent_updates.updater,recent_updates.blogid,recent_updates.timestamp as tt,subject,replies,UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(recent_updates.timestamp),url
FROM recent_updates
LEFT JOIN blog_meta ON recent_updates.updater=blog_meta.userid AND recent_updates.blogid=blog_meta.blogid
left join blog_url using (userid,blogid)
WHERE recent_updates.userid='2837876' AND subject IS NOT NULL order by tt desc LIMIT 40
)
union
(
SELECT staff_updates.updater,staff_updates.blogid,staff_updates.timestamp as tt,subject,replies,UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(staff_updates.timestamp),url
FROM staff_updates
LEFT JOIN blog_meta ON staff_updates.updater=blog_meta.userid AND staff_updates.blogid=blog_meta.blogid
left join blog_url using (userid,blogid)
where subject IS NOT NULL AND (staff_updates.updater=118 ) order by tt desc LIMIT 40
)
order by tt desc LIMIT 40;

+----------------+--------+---------------------------------+---------+---------+----------------------------------------------+--------+-----------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------------+--------+---------------------------------+---------+---------+----------------------------------------------+--------+-----------------------------+
| recent_updates | ref | userid | userid | 4 | const | 719 | Using where |
| blog_meta | eq_ref | PRIMARY | PRIMARY | 8 | recent_updates.updater,recent_updates.blogid | 1 | Using where |
| blog_url | ref | PRIMARY,url,blogid,useridblogid | blogid | 9 | ms.blog_meta.userid,ms.blog_meta.blogid | 1 | |
| staff_updates | ref | PRIMARY | PRIMARY | 4 | const | 1 | Using where; Using filesort |
| blog_meta | eq_ref | PRIMARY | PRIMARY | 8 | staff_updates.updater,staff_updates.blogid | 1 | Using where |
| blog_url | ref | PRIMARY,url,blogid,useridblogid | blogid | 9 | ms.blog_meta.userid,ms.blog_meta.blogid | 1 | |
| recent_updates | index | NULL | userid | 8 | NULL | 548628 | Using index |
| blog_meta | eq_ref | PRIMARY | PRIMARY | 8 | recent_updates.updater,recent_updates.blogid | 1 | Using index |
| blog_url | ref | PRIMARY,url,blogid,useridblogid | blogid | 9 | ms.blog_meta.userid,ms.blog_meta.blogid | 1 | Using index |
+----------------+--------+---------------------------------+---------+---------+----------------------------------------------+--------+-----------------------------+

Ouch! It scans 548628 rows.

Now here is the interesting part -- if I simply remove the "order by tt desc" part from the statement (in the outer most part of the clause), I get:

+----------------+--------+---------------------------------+---------+---------+----------------------------------------------+------+-----------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------------+--------+---------------------------------+---------+---------+----------------------------------------------+------+-----------------------------+
| recent_updates | ref | userid | userid | 4 | const | 57 | Using where |
| blog_meta | eq_ref | PRIMARY | PRIMARY | 8 | recent_updates.updater,recent_updates.blogid | 1 | Using where |
| blog_url | ref | PRIMARY,url,blogid,useridblogid | blogid | 9 | ms.blog_meta.userid,ms.blog_meta.blogid | 1 | |
| staff_updates | ref | PRIMARY | PRIMARY | 4 | const | 1 | Using where; Using filesort |
| blog_meta | eq_ref | PRIMARY | PRIMARY | 8 | staff_updates.updater,staff_updates.blogid | 1 | Using where |
| blog_url | ref | PRIMARY,url,blogid,useridblogid | blogid | 9 | ms.blog_meta.userid,ms.blog_meta.blogid | 1 | |
+----------------+--------+---------------------------------+---------+---------+----------------------------------------------+------+-----------------------------+


It seems like MySQL makes an additional (and unnecessary) pass. Actually, it is even worse: it forces my first query to do a full table scan. As if order of operations is not respected.

Regardless of what indexes I have -- isn't it true that any ORDER BY/LIMIT modifiers placed on the 'outside' should have no effect on the internal SELECT statements?

Please excuse my dear aunt sally,
Brian



Edited 2 time(s). Last edit at 01/21/2006 11:18PM by Brian Klug.