MySQL Bugs: #11842: Server hangs when using ANY, IN, NOT IN, EXISTS, NOT EXISTS

Bug #11842	Server hangs when using ANY, IN, NOT IN, EXISTS, NOT EXISTS
Submitted:	10 Jul 2005 6:56	Modified:	27 Jul 2005 6:17
Reporter:	Christopher Martinez	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.12a	OS:	Windows (WindowsNT,XP,2000)
Assigned to:		CPU Architecture:	Any

Description:
I have these queries that i used to look for some id's from master table whether their id's are recorded in table_att, or not recorded in table_att with the specified period like in the example below. Using  IN, NOT IN or EXISTS, NOT EXISTS. 

* when i used these queries the server always hang or is not responding.  Until I used kill thread in server connections in MySQL Manager. After that in my query browser shows some results but its not complete. i had also used exists, and not exists.  But it didn't give the right results. Im using 4.12a.  I have read that in older versions you have to used inner join but i have tried it also and still its not working.

Query 1
Select tbl_master.id from tbl_master where del_cod='na' and
id NOT IN (select tbl_att.id from tbl_att where srv_dat between 
'2005-07-02 00:00:00' and '2005-07-08 00:00:00')

Query 2
Select tbl_master.id from tbl_master where del_cod='na' and
id <> ANY (select tbl_att.id from tbl_att where srv_dat between 
'2005-07-02 00:00:00' and '2005-07-08 00:00:00')

The scenario is this. I have two tables tbl_master and tbl_att.
I want to get all the ID from tbl_master that are not in tbl_att during a certain period.  

Thank you very much.
God Bless You All
Christopher Martinez

How to repeat:
The scenario is this. I have two tables tbl_master and tbl_att.
I want to get all the ID from tbl_master that are not in tbl_att during a certain period.

Hi! could you pls, put here output of 'explain' of that queries? And what is table size? Could you provide the table definitions and some exaple data?

Hi, Aleksey Kishkin

Well I finally figured it out.  But ofcourse I will show you the explanation.  Maybe i was not that good in SQL.  But then here it is

tbl_master has 28,941 rows, 44 fields, size/length is 7.4 MB
tbl_att_his has 492,148 rows, 5 fields, size/length is 18.5 MB

I finally noticed that these tables has no index at all. So I edit the tables and indexed it.  And wow its amazing. its super fast.  below is the query, results and explanation.

SELECT tbl_master.id
FROM tbl_master
WHERE tbl_master.vdt_cod = 'na' AND 
    tbl_master.id NOT IN
        (SELECT tbl_att_his.id
      FROM tbl_att_his
      WHERE tbl_att_his.srv_dat BETWEEN 
           '2005-07-02 00:00:00' AND '2005-07-08 00:00:00')

Explaned results not indexed
id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'PRIMARY', 'tbl_master', 'ALL', '', '', , '', 28941, 'Using where'
2, 'DEPENDENT SUBQUERY', 'tbl_att_his', 'ALL', '', '', , '', 492148, 'Using where'

Explain Results indexed:
id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'PRIMARY', 'tbl_master', 'ALL', '', '', '', '', 28941, 'Using where'
2, 'DEPENDENT SUBQUERY', 'tbl_att_his', 'index_subquery', 'Index_1', 'Index_1', '12', 'func', 24, 'Using index; Using where'

Query Results not indexed:
The servers hangs...no results 

Query Results not indexed:
'100005'
'100008'
'100054'
'100059'
'100060'
'114300'
......
6164 rows fetched in 3.2627s (0.9654s)

By the way I am using 4.12 in NT/2000 and 5.0.7beta in XP. And they all work fine. Next week we will be using XServe from Apple.  And I will try this also in that OS Tiger server.  I'll give you a feedback.

Thanks...

Hi, Aleksey Kishkin

i have a correction

Query Results not indexed:
The servers hangs...no results 

Query Results indexed:
'100005'
'100008'
'100054'
'100059'
'100060'
'114300'
......
6164 rows fetched in 3.2627s (0.9654s)

Thanks again. And God bless.

Could you please write here definition of tables ? Could you wait for result of non-indexed tables more ? I guess You had a long time delay because the tables didn't have indexes.

Well i can send you the table scripts, but i think the results of non indexed table is not yet because i remember i have tried that overnight from 9pm to 9am in the morning and there is still no results. But i will try to send it to you next time.
Any here is the table scripts.

CREATE TABLE tbl_master
(
   ID VARCHAR(9),
   LNAM VARCHAR(50),
   FNAM VARCHAR(50),
   MNAM VARCHAR(50),
   NNAM VARCHAR(50),
   RESNO VARCHAR(50),
   STREET VARCHAR(50),
   BRGY VARCHAR(50),
   CITY VARCHAR(50),
   TELNO VARCHAR(30),
   MOBILE VARCHAR(25),
   EMAIL VARCHAR(50),
   AREA_CO VARCHAR(7),
   PSTR_CO VARCHAR(6),
   RGHD_CO VARCHAR(6),
   DIV_NUM FLOAT,
   SRC_COD VARCHAR(6),
   DIV_COD VARCHAR(1),
   GENDER VARCHAR(1),
   CIV_STA VARCHAR(1),
   MEM_COD VARCHAR(2),
   CELL_CO VARCHAR(10),
   CNRT_CO VARCHAR(2),
   BDAT DATETIME,
   BADAT DATETIME,
   WBDAT DATETIME,
   HSBDAT DATETIME,
   LSDAT DATETIME,
   PIF_DAT DATETIME,
   CRT_DAT DATETIME,
   VDT_DAT DATETIME,
   VDT_COD VARCHAR(2),
   DEL_ADA DATETIME,
   REAC_DA DATETIME,
   UPDT_DA DATETIME,
   UPDT_TI DATETIME,
   UPDT_OP VARCHAR(8),
   AGE FLOAT,
   MBR_DAT DATETIME,
   IS_UPDT CHAR(1),
   FAM_ID VARCHAR(13),
   TELNO1 VARCHAR(30),
   NID VARCHAR(8),
   CONID VARCHAR(8)
);

CREATE TABLE tbl_att_his
(
   ID VARCHAR(9),
   SRV_DAT DATETIME,
   UPDT_DA DATETIME,
   UPDT_OP VARCHAR(8),
   SRV_CO VARCHAR(2)  NOT NULL
);

Hope i can send you the results of non index table as soon as possible if the server will not hang.  Cause it uses 50% of the CPU. Hmmm, I am wondering why.  And by the way, I forgot to tell you IN and NOT IN is functioning but i hav tried <> ANY in my query on my indexed tables but it is still not functioning. it's confusing now.  I think will send you the test data and tried it using 4.12a and 5.x in NT or XP.  What you think?

Thanks....