March 2010 Archives

MySQL's braindead subselect handling...

| | Comments (3) | TrackBacks (0)
Or: Why MySQL is not a database but only a "database".

Imagine this query:

mysql> 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' );

+--------+--------+-----------+------------+-----------+--------------------------+
| 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)
1 Minute!!!!! After seperating the outer and inner join the same table displays just after some seconds:
Easy:

pascal@pgt02:~# smbios -t SMB_TYPE_BASEBOARD
ID    SIZE TYPE
2     202  SMB_TYPE_BASEBOARD (base board)

  Manufacturer: Sun Microsystems
  Product: Sun Fire X4200 M2                    
  Version: To Be Filled By O.E.M.         
  Serial Number: 1005LCB-0807GB0U08             
  Asset Tag: To Be Filled By O.E.M.         
  Location Tag: To Be Filled By O.E.M.         

  Chassis: 3
  Flags: 0x9
        SMB_BBFL_MOTHERBOARD (board is a motherboard)
        SMB_BBFL_REPLACABLE (board is field-replacable)
  Board Type: 0xa (motherboard)


Just in case you need it...

December 2015

Sun Mon Tue Wed Thu Fri Sat
    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31    

About

This blog is owned by:

Pascal Gienger
J├Ągerstrasse 77
8406 Winterthur
Switzerland


Google+: Profile
YouTube Channel: pascalgienger