MySQL Forums
Forum List  »  Optimizer & Parser

Re: execute select took 8 min
Posted by: h ch
Date: March 26, 2010 01:51AM

i notice one subquery is unnecessary ,so now query is

mysql> explain (select M_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 = M_Id 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 | index_subquery | poster1,asid | asid | 4 | func | 155 | Using where |
| 3 | DEPENDENT SUBQUERY | minfo | eq_ref | PRIMARY | PRIMARY | 4 | server.TB_MStatus.M_Id | 1 | |
+----+--------------------+------------+----------------+---------------+---------+---------+------------------------+------+-------------+

but still very slow ,TB_MStatus have 1000 records

Options: ReplyQuote


Subject
Views
Written By
Posted
2669
March 26, 2010 12:31AM
Re: execute select took 8 min
1467
March 26, 2010 01:51AM
1452
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.