MySQL Forums
Forum List  »  Knowledge Base

How about this problem
Posted by: Chen Bo
Date: December 03, 2007 12:24AM

MYSQL version:4.1.2

INSERT INTO T_M_CATEGORY_SEN
(RBID, CATE6_DIV, LOCALCATEID, CONSOLICATEID, EXP_DATE_FROM, EXP_DATE_TO,
CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, GENEID)
SELECT e.RBID, 1, d.LOCALCATEID, d.CONSOLICATEID, d.EXP_DATE_FROM, d.EXP_DATE_TO,
d.CREATE_USER, d.CREATE_DATE, d.UPDATE_USER, d.UPDATE_DATE, d.GENEID
FROM (SELECT * FROM T_M_CONSOLICATE a, T_M_LOCALCATE b, T_M_6DIGIT2 c
WHERE a.CON_CATE_CODE = b.LOC_CATE_CODE and c.CATE6_CODE = a.CON_CATE_CODE) d,
(SELECT RBID FROM T_M_REGIONB) e
WHERE NOT EXISTS
(SELECT * FROM T_M_CATEGORY_SEN f WHERE
d.LOCALCATEID = f.LOCALCATEID and d.CONSOLICATEID = f.CONSOLICATEID
AND e.RBID = f.RBID
group by f.LOCALCATEID, f.CONSOLICATEID);

When the SQL running:sometimes running 2 hours,but sometimes running only 1 minute.
And user command "show processlist":
When running 2 hours:State is "Sending data"
When running 1 minute:State first is "Sending data"(about 5 seconds),then change to "Copying to tmp table"

explain the SQL,there are some messages bellow:

id,"select_type","table","type",possible_keys,key,key_len,ref,rows,"Extra"
1,"PRIMARY","<derived2>","ALL",NULL,NULL,NULL,NULL,377,""
1,"PRIMARY","<derived3>","ALL",NULL,NULL,NULL,NULL,91,"Using where"
4,"DEPENDENT SUBQUERY","f","ref","T_M_CATEGORY_SEN_FK1,T_M_CATEGORY_SEN_FK2","T_M_CATEGORY_SEN_FK1",5,"d.LOCALCATEID",69,"Using where; Using temporary; Using filesort"
3,"DERIVED","T_M_REGIONB","index",NULL,"PRIMARY",4,NULL,91,"Using index"
2,"DERIVED","c","ALL",NULL,NULL,NULL,NULL,321,""
2,"DERIVED","a","ALL",NULL,NULL,NULL,NULL,3649,"Using where"
2,"DERIVED","b","ALL",NULL,NULL,NULL,NULL,3653,"Using where"


Why the SQL sometines running slow or fast?

Who can help me?Thank you very much!

Options: ReplyQuote


Subject
Views
Written By
Posted
How about this problem
3394
December 03, 2007 12:24AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.