Bug #8001 Computer HANG when execute 'not exists' command
Submitted: 19 Jan 2005 4:54 Modified: 6 Feb 2005 9:40
Reporter: Fate Wong Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:
Version:4.1.9 OS:Windows (Windows XP Pro SP1)
Assigned to: Ingo Strüwing CPU Architecture:Any

[19 Jan 2005 4:54] Fate Wong
Description:
I am using MySQL 4.1.9, windows xp pro sp2. I have a table with 3 fields ( download_id[primary key, auto increment], game_id[varchar], telco_id[int] ) which is having 170712 records totally. When i execute SQL command below in order to select "d1.game_id" which is not found in table "d2", my computer cpu resource becomes 99~100% which used by mysqld-nt.exe, and the process keep on going and look like non-stop. I had to call out the task manager and kill the "mysql-nt" process. 

When i reduce my record to around 1300 by deleting most of them, the problem had been solved. Is the problem occurring just because of too many data resided on my mysql server?

SQL Command
===========
SELECT  DISTINCT d1.game_id
FROM download d1
WHERE ( telco_id =1 ) AND (  NOT 
EXISTS ( 
SELECT  DISTINCT d2.game_id
FROM download d2
WHERE d2.telco_id =2 AND d2.game_id = d1.game_id ) )

How to repeat:
create a table with following fields and input the data as below.

download_id         game_id             telco_id
==============================
auto_increment     11111                1
auto_increment     11111                2
auto_increment     11112                1
auto_increment     11112                2
auto_increment     11113                1
auto_increment     11114                1
auto_increment     11114                2
.......
until around hundred thousand records.
[19 Jan 2005 9:39] MySQL Verification Team
Hi,

Thank you for the report. Could you upload a compressed archive of the table dump  to ftp://ftp.mysql.com/pub/mysql/upload/ ?
[20 Jan 2005 1:02] Fate Wong
I had uploaded the table file to ftp://ftp.mysql.com/pub/mysql/upload/ with filename bug_8001.rar.

With minor correction to previous bug report, one field had been added (mobile_id[int]) into the table. so there are totally 4 fields in the table, ie. download_id, game_id, mobile_id, telco_id.
[20 Jan 2005 21:09] MySQL Verification Team
Verified with 4.1.10-debug-log.
OS: Windows, Linux
MySQL server crashes on Linux.
Back trace:

(gdb) bt
#0  String::free (this=0xa76dd8d4) at sql_string.h:146
#1  0x08197ec4 in JOIN::join_free (this=0x8cf5a98, full=true) at field.h:1246
#2  0x0819245d in JOIN::cleanup (this=0x8cf1e18) at sql_select.cc:1497
#3  0x08232ac9 in st_select_lex_unit::cleanup (this=0x8cead30) at sql_union.cc:591
#4  0x0819250f in JOIN::cleanup (this=0x8ceb5c0) at sql_select.cc:1509
#5  0x0819291c in mysql_select (thd=0x8cd0528, rref_pointer_array=0x8cd075c, tables=0x8ceaa18, wild_num=0,
    fields=@0x8cd06c0, conds=0x8ceb440, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
    select_options=8669697, result=0x8ceb5b0, unit=0x8cd0570, select_lex=0x8cd0658) at sql_select.cc:1596
#6  0x0818efbc in handle_select (thd=0x8cd0528, lex=0x8cd0564, result=0x8ceb5b0) at sql_select.cc:193
#7  0x081727cf in mysql_execute_command (thd=0x8cd0528) at sql_parse.cc:2020
#8  0x081773b9 in mysql_parse (thd=0x8cd0528,
    inBuf=0x8cea870 "SELECT  DISTINCT d1.game_id\nFROM download d1\nWHERE ( telco_id =1 ) AND (  NOT \nEXISTS ( \nSELECT  DISTINCT d2.game_id\nFROM download d2\nWHERE d2.telco_id =2 AND d2.game_id = d1.game_id ) )",
    length=186) at sql_parse.cc:4094
#9  0x08171486 in dispatch_command (command=COM_QUERY, thd=0x8cd0528,
    packet=0x8cd2761 "SELECT  DISTINCT d1.game_id\nFROM download d1\nWHERE ( telco_id =1 ) AND (  NOT \nEXISTS ( \nSELECT  DISTINCT d2.game_id\nFROM download d2\nWHERE d2.telco_id =2 AND d2.game_id = d1.game_id ) )",
    packet_length=187) at sql_parse.cc:1505
#10 0x08170da2 in do_command (thd=0x8cd0528) at sql_parse.cc:1291
#11 0x081702db in handle_one_connection (arg=0x8cd0528) at sql_parse.cc:1023
#12 0xb7e4614b in pthread_start_thread () from /lib/libpthread.so.0
#13 0xb7e461df in pthread_start_thread_event () from /lib/libpthread.so.0
#14 0xb7d7950a in clone () from /lib/libc.so.6
[6 Feb 2005 9:40] Ingo Strüwing
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

On a Pentium 4 / 2,4 GHz I got:

+---------+
| game_id |
+---------+
| 6012346 |
... 30 results left out ...
| 220016  |
+---------+
32 rows in set (5 hours 14 min 53.07 sec)

After ALTER TABLE download ADD INDEX (telco_id), ADD INDEX (game_id); I got:

+---------+
| game_id |
+---------+
| 6012346 |
... 30 results left out ...
| 220016  |
+---------+
32 rows in set (1 min 23.96 sec)

This is 225 times faster!
Without the indexes, the query needed to read the whole data file 170712 * 170712 = 29142586944 times. This is 1541936 times per second on my PC. Not bad, is it? ;)
The relative small table was copletly cached. So there was no I/O. Only the CPU was busy.
Remember that indexes are often a major accelerator.
Remeber also to use the EXPLAIN command for hints what the database tries.
I am no SQL expert. But I suspect that the query could be even more optimized.