execute select took 8 min

[h ch]

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.