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: | |
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
[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.