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