Very slow subselect (parser bug)?

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=gcdmg-mysql-2@m.gmane.org

Re: Very slow subselect (parser bug)?

--000e0cd242c425efa10481d8e2c8
Content-Type: text/plain; charset=ISO-8859-1

On Mon, Mar 15, 2010 at 4:22 PM, Pascal Gienger <
Pascal.Gienger@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

--000e0cd242c425efa10481d8e2c8--