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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
