Very slow subselect (parser bug)?

[Pascal Gienger]

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'20100322T00Z' and 
(bwrecurren0_.end_date'20100315T00Z' or 
bwrecurren0_.start_date=bwrecurren0_.end_date and 
bwrecurren0_.end_date='20100315T00Z') or 
(bwrecurren0_.bw_rstart_floating is null) and 
bwrecurren0_.start_date'20100321T23Z' and 
(bwrecurren0_.end_date'20100314T23Z' or 
bwrecurren0_.start_date=bwrecurren0_.end_date and 
bwrecurren0_.end_date='20100314T23Z')) 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'20100321T00Z' and 
(bwrecurren0_.end_date'20100314T00Z' or 
bwrecurren0_.start_date=bwrecurren0_.end_date and 
bwrecurren0_.end_date='20100314T00Z') or 
(bwrecurren0_.bw_rstart_floating is null) and 
bwrecurren0_.start_date'20100320T23Z' and 
(bwrecurren0_.end_date'20100313T23Z' or 
bwrecurren0_.start_date=bwrecurren0_.end_date and 
bwrecurren0_.end_date='20100313T23Z'));


+-+
| 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 

Re: Very slow subselect (parser bug)?

[Johan De Meersman]

On Mon, Mar 15, 2010 at 4:22 PM, Pascal Gienger 
pascal.gien...@uni-konstanz.de wrote:

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


Not so much a bug as a missing feature: the parser is currently unable to
recognize any subselect as being fully independent, and will thus execute it
for each and every row in your primary select.

You may or may not get better results by rewriting it so the subselect is a
virtual table. If that doesn't help, subselect into temptable and use that,
or do the reconstruction in code, or other dirty tricks. YMMV.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel