Bug #11254 Subquery IN Crashes MySQL (Service Pegs CPU)

• Submitted: 10 Jun 2005 20:59
• Modified: 22 Jan 2008 23:46
• Reporter: Garrett Heaver
• Status: Closed
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 4.1.10 and 4.1.12/BK source
• OS: Windows (Windows XP Pro SP2/Linux)
• CPU Architecture: Any
• Tags: subquery benchmark

[10 Jun 2005 20:59] Garrett Heaver

Description:
Any Query with a SubQuery of IN appears to cause the MySQL Service to PEG the CPU at 99%. 

Only by killing the service can control be regained.

Tested with both a single subquery clause and twin subquery clause.

All tables used in the tests have more than 100,000 rows

How to repeat:
SELECT * FROM tbl_words WHERE ident IN 
(SELECT foreignkey_words_ident FROM tbl_words_substrings_length2 WHERE substring = 'AB')
AND ident IN 
(SELECT foreignkey_words_ident FROM tbl_words_substrings_length3 WHERE substring = 'ABC')

[10 Jun 2005 23:13] MySQL Verification Team

Could you please provide a test case (database schema & data)
for to run that query on our side ?

You can upload the zip file with a name identifying this bug report
at:

ftp://ftp.mysql.com/pub/mysql/upload/

Thanks in advance.

[14 Jun 2005 8:46] Sergei Golubchik

thanks.
(file BUG_11254.rar, 8707133 bytes)

[17 Jun 2005 22:30] MySQL Verification Team

This is an optimization issue tested against latest BK, below the
result on Linux:

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

mysql>

Then actually the server not crashes and the client just "hangs" while
the query is processed for the server.

[19 Jun 2005 22:40] Garrett Heaver

16 minutes for a query which should be completable in less than a second is a little shocking. Has this issue been earmarked for addressing in a future build

Thanks
Garrett

[1 Feb 2006 11:34] Lucio Crusca

I could still reproduce this bug up until 4.1.16/windows.
Has this optimization been introduced in 5.0 or later?

[22 Jan 2008 23:46] Sergey Petrunya

This subquery is covered by new subquery optimizations in MySQL 6.0

EXPLAIN in current 6.0-bk ( ChangeSet@1.2773, 2007-12-20 16:02:38+01:00, [e-mail] +6 -0):

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: moto_tbl_registrations_substrings_2
         type: ref
possible_keys: ix_moto_tbl_registrations_substrings_2_fk_registration_ident,ix_moto_tbl_registrations_substrings_2_substring
          key: ix_moto_tbl_registrations_substrings_2_substring
      key_len: 2
          ref: const
         rows: 10
        Extra: Using index condition; Start temporary
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: moto_tbl_registrations_info
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: mysql_bug_11254.moto_tbl_registrations_substrings_2.fk_registration_ident
         rows: 1
        Extra: End temporary
2 rows in set (0.00 sec)

Query run time: 0.08 sec, (second run, when the data is in the OS disk cache).
Query run time with new optimizations disabled: 12 min 24 sec. 
It's 9305 times faster now :-).

Changing status to Closed.

[15 Dec 2009 16:52] Valeriy Kravchuk

Bug #11624 was marked as a duplicate of this one.