MySQL Forums
Forum List  »  Optimizer & Parser

execute select took 8 min
Posted by: h ch
Date: March 26, 2010 12:31AM

i find very interesting thing today,a trigger block all query in my DB,finally,when i check the SQL in trigger,i find the SQL execute time is 8 min!!
here is the SQL

mysql> explain select SubscribeQ_Id from server.TB_MStatus where SubscribeQ_Id in (select asid from `netsun_Q`.`my_Q` where poster = (select login from `hub_netsun`.`minfo` where id = 100001 limit 1));
+----+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | PRIMARY | TB_MStatus | ALL | NULL | NULL | NULL | NULL | 1000 | Using where |
| 2 | DEPENDENT SUBQUERY | my_Q | range | poster1,asid | poster1 | 153 | NULL | 54 | Using where; Using index |
| 3 | SUBQUERY | minfo | const | PRIMARY | PRIMARY | 4 | | 1 | |
+----+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
ok this no problem ,it's return empty set,next run this


mysql> select * from server.TB_MStatus where SubscribeQ_Id in (select SubscribeQ_Id from server.TB_MStatus where SubscribeQ_Id in (select asid from `netsun_Q`.`my_Q` where poster = (select login from `hub_netsun`.`minfo` where id = 100001 limit 1)));
Empty set (8 min 14.88 sec)

this took very long time

mysql> explain select M_Id,S_Id,M_Id as AdvM_Id,0 as CM_Status,SubscribeQ_Id from server.TB_MStatus where M_Id in (select SubscribeQ_Id from server.TB_MStatus where SubscribeQ_Id in (select asid from `netsun_Q`.`my_Q` where poster = (select login from `hub_netsun`.`minfo` where id = 100001 limit 1)));
+----+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | PRIMARY | TB_MStatus | ALL | NULL | NULL | NULL | NULL | 1000 | Using where |
| 2 | DEPENDENT SUBQUERY | TB_MStatus | ALL | NULL | NULL | NULL | NULL | 1000 | Using where |
| 3 | DEPENDENT SUBQUERY | my_Q | range | poster1,asid | poster1 | 153 | NULL | 54 | Using where; Using index |
| 4 | SUBQUERY | minfo | const | PRIMARY | PRIMARY | 4 | | 1 | |
+----+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+



Edited 1 time(s). Last edit at 03/26/2010 12:59AM by h ch.

Options: ReplyQuote


Subject
Views
Written By
Posted
execute select took 8 min
2561
March 26, 2010 12:31AM
1409
March 26, 2010 01:51AM
1396
March 26, 2010 02: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.