From: Pascal Gienger
Date: March 15 2010 3:22pm
Subject: Very slow subselect (parser bug)?
List-Archive: http://lists.mysql.com/mysql/220950
Message-Id: <4B9E50B0.5070201@uni-konstanz.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

We're testing a bedework system here with an underlying MySQL Database:
Version 5.1.44, Linux x64 (64bit), icc binary from www.mysql.com.

This statement uses 1 minute of 100% CPU:

select summaries0_.bw_eventid as bw1_1_, summaries0_.bw_strid as bw2_1_, 
bwstring1_.bw_id as bw1_70_0_, bwstring1_.bwseq as bwseq70_0_, 
bwstring1_.bw_lang as bw3_70_0_, bwstring1_.bw_value as bw4_70_0_ from 
bw_event_summaries summaries0_ left outer join bw_strings bwstring1_ on 
summaries0_.bw_strid=bwstring1_.bw_id where summaries0_.bw_eventid in 
(select bweventobj1_.eventid from bw_recurrences bwrecurren0_ inner join 
bw_events bweventobj1_ on bwrecurren0_.masterid=bweventobj1_.eventid 
where (bwrecurren0_.bw_rstart_floating='T' and 
bwrecurren0_.start_date<'20100322T000000Z' and 
(bwrecurren0_.end_date>'20100315T000000Z' or 
bwrecurren0_.start_date=bwrecurren0_.end_date and 
bwrecurren0_.end_date>='20100315T000000Z') or 
(bwrecurren0_.bw_rstart_floating is null) and 
bwrecurren0_.start_date<'20100321T230000Z' and 
(bwrecurren0_.end_date>'20100314T230000Z' or 
bwrecurren0_.start_date=bwrecurren0_.end_date and 
bwrecurren0_.end_date>='20100314T230000Z')) and 
bweventobj1_.bw_col_path='/user/pascal.gienger/Training' );

Result:

+--------+--------+-----------+------------+-----------+--------------------------+
| bw1_1_ | bw2_1_ | bw1_70_0_ | bwseq70_0_ | bw3_70_0_ | bw4_70_0_ 
           |
+--------+--------+-----------+------------+-----------+--------------------------+
|   2251 |   2674 |      2674 |          0 | NULL      | MLBf 
           |
|   2493 |   2933 |      2933 |          0 | NULL      | Cvlqxpqz 
           |
|   2848 |   3322 |      3322 |          0 | NULL      | Vlqehfhva 
           |
|   2927 |   3405 |      3405 |          0 | NULL      | fb.nt-Lkqivqm 
           |
|   3057 |   3557 |      3557 |          0 | NULL      | Glqsd 
           |
|   3116 |   3616 |      3616 |          0 | NULL      | KGWG 
           |
|   3212 |   3718 |      3718 |          0 | NULL      | MlwfQPE 
           |
|   3361 |   3873 |      3873 |          0 | NULL      | Eklmzmmlsfeei 
           |
|   3573 |   4098 |      4098 |          0 | NULL      | 
HYX-Rjdmmtyubvbqeczvwxz  |
|   3622 |   4149 |      4149 |          0 | NULL      | Uayfhjjlge 
           |
|   3741 |   4283 |      4283 |          0 | NULL      | Sflnh 
QK/Flkudduiwmkbdqz |
+--------+--------+-----------+------------+-----------+--------------------------+
11 rows in set (1 min 1.69 sec)


Executing only the second select, I'll get this result:


select bweventobj1_.eventid from bw_recurrences bwrecurren0_ inner join 
bw_events bweventobj1_ on bwrecurren0_.masterid=bweventobj1_.eventid 
where (bwrecurren0_.bw_rstart_floating='T' and 
bwrecurren0_.start_date<'20100321T000000Z' and 
(bwrecurren0_.end_date>'20100314T000000Z' or 
bwrecurren0_.start_date=bwrecurren0_.end_date and 
bwrecurren0_.end_date>='20100314T000000Z') or 
(bwrecurren0_.bw_rstart_floating is null) and 
bwrecurren0_.start_date<'20100320T230000Z' and 
(bwrecurren0_.end_date>'20100313T230000Z' or 
bwrecurren0_.start_date=bwrecurren0_.end_date and 
bwrecurren0_.end_date>='20100313T230000Z'));

+---------+
| eventid |
+---------+
|    3622 |
|    3573 |
|    3212 |
|    3116 |
|    2251 |
|    2927 |
|    2493 |
|    3057 |
|    2848 |
|    3212 |
|    3361 |
|    2493 |
|    3057 |
|    3741 |
|    3212 |
+---------+

Using this result to reconstruct the first left outer join from the 
initial statement, I get this result:

select summaries0_.bw_eventid as bw1_1_, summaries0_.bw_strid as bw2_1_, 
bwstring1_.bw_id as bw1_70_0_, bwstring1_.bwseq as bwseq70_0_, 
bwstring1_.bw_lang as bw3_70_0_, bwstring1_.bw_value as bw4_70_0_ from 
bw_event_summaries summaries0_ left outer join bw_strings bwstring1_ on 
summaries0_.bw_strid=bwstring1_.bw_id WHERE summaries0_.bw_eventid IN 
(3622,3573,3212,3116,2251,2927,2493,3057,2848,3212,3361,2493,3057,3741,3212);

+--------+--------+-----------+------------+-----------+--------------------------+
| bw1_1_ | bw2_1_ | bw1_70_0_ | bwseq70_0_ | bw3_70_0_ | bw4_70_0_ 
           |
+--------+--------+-----------+------------+-----------+--------------------------+
|   2251 |   2674 |      2674 |          0 | NULL      | MLBf 
           |
|   2493 |   2933 |      2933 |          0 | NULL      | Cvlqxpqz 
           |
|   2848 |   3322 |      3322 |          0 | NULL      | Vlqehfhva 
           |
|   2927 |   3405 |      3405 |          0 | NULL      | fb.nt-Lkqivqm 
           |
|   3057 |   3557 |      3557 |          0 | NULL      | Glqsd 
           |
|   3116 |   3616 |      3616 |          0 | NULL      | KGWG 
           |
|   3212 |   3718 |      3718 |          0 | NULL      | MlwfQPE 
           |
|   3361 |   3873 |      3873 |          0 | NULL      | Eklmzmmlsfeei 
           |
|   3573 |   4098 |      4098 |          0 | NULL      | 
HYX-Rjdmmtyubvbqeczvwxz  |
|   3622 |   4149 |      4149 |          0 | NULL      | Uayfhjjlge 
           |
|   3741 |   4283 |      4283 |          0 | NULL      | Sflnh 
QK/Flkudduiwmkbdqz |
+--------+--------+-----------+------------+-----------+--------------------------+
11 rows in set (0.00 sec)

The same result, but not in 1 Minute but in less than the tenth of a 
second, including the inner select step.

Is this a bug in the SQL parser?

-- 
Pascal Gienger
University of Konstanz, IT Services Department ("Rechenzentrum")
Electronic Communications and Web Services
Building V, Room V404, Phone +49 7531 88 5048, Fax +49 7531 88 3739