Bug #11624 Follow up on Bug #11254
Submitted: 29 Jun 2005 0:26 Modified: 15 Dec 2009 16:51
Reporter: Garrett Heaver Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:4.1.12 OS:Windows (Windows Server 2003)
Assigned to: CPU Architecture:Any

[29 Jun 2005 0:26] Garrett Heaver
Description:
IN SubQueries which include results from large rowcount tables are not optimized properly and result in huge queries timewise. 

Results from #11254 performed by MySQL people show a query taking 16 minutes when it is completable in less than a second if the subqueries results are explicitly entered into the IN set

Below are the results from #11254

*************************** 1647. row ***************************
                       ident: 147827
                        guid: F02F2E90
           fk_merchant_ident: 2
fk_registration_format_ident: 3
   fk_date_restriction_ident: 0
                registration: MR02VOW
                       price: 499
               addition_date: 2005-06-01 12:00:00
                   sale_date: NULL
            last_change_date: 2005-06-01 12:00:00
                 impressions: 0
                      active: 1
1647 rows in set (16 min 28.14 sec)

mysql> 

and Windows:

*************************** 1647. row ***************************
                       ident: 147827
                        guid: F02F2E90
           fk_merchant_ident: 2
fk_registration_format_ident: 3
   fk_date_restriction_ident: 0
                registration: MR02VOW
                       price: 499
               addition_date: 2005-06-01 12:00:00
                   sale_date: NULL
            last_change_date: 2005-06-01 12:00:00
                 impressions: 0
                      active: 1
1647 rows in set (17 min 29.94 sec)

How to repeat:
Use Data and Queries provided for BUG #11254
[29 Jun 2005 17:46] Igor Babaev
This is a query optimization problem to be resolved in 5.2.
[15 Dec 2009 16:51] Valeriy Kravchuk
I'd say this is a duplicate (another manifestation/effect of) Bug #11254.