子查询，distinct及not exist

原句如下：

select DISTINCT acc_id from bored a where "2008-05-15"<=DATE_FORMAT(a.done_time,"%Y-%m-%d") and DATE_FORMAT(a.done_time,"%Y-%m-%d")<"2008-05-23" and not EXISTS(SELECT DISTINCT acc_id from bored where bored.acc_id=a.acc_id and DATE_FORMAT( bored.done_time,"%Y-%m-%d")<"2008-05-15" );

 

mysql> explain select DISTINCT acc_id from bored a where "2008-05-15"<=DATE_FORMAT(a.done_time,"%Y-%m-%d") and DATE_FORMAT(a.done_time,"%Y-%m-%d")<"2008-05-23" and not EXISTS(SELECT DISTINCT acc_id from bored where bored.acc_id=a.acc_id and DATE_FORMAT( bored.done_time,"%Y-%m-%d")<"2008-05-15" );
+----+--------------------+-------+------+---------------+-----------+---------+-------------------+--------+------------------------------+
| id | select_type        | table | type | possible_keys | key       | key_len | ref               | rows   | Extra                        |
+----+--------------------+-------+------+---------------+-----------+---------+-------------------+--------+------------------------------+
|  1 | PRIMARY            | a     | ALL  | NULL          | NULL      | NULL    | NULL              | 188953 | Using where; Using temporary |
|  2 | DEPENDENT SUBQUERY | bored | ref  | idx_accid     | idx_accid | 4       | InGameAD.a.acc_id |    266 | Using where; Using temporary |

测试返回结果用时47秒。

 

改进1) 简化时间比较

mysql> explain select DISTINCT acc_id from bored a where a.done_time between '2008-05-15' and '2008-05-23' and not EXISTS(SELECT DISTINCT acc_id from bored where bored.acc_id=a.acc_id and  bored.done_time<'2008-05-15' );
+----+--------------------+-------+------+------------------------+-----------+---------+-------------------+--------+------------------------------+
| id | select_type        | table | type | possible_keys          | key       | key_len | ref               | rows   | Extra                        |
+----+--------------------+-------+------+------------------------+-----------+---------+-------------------+--------+------------------------------+
|  1 | PRIMARY            | a     | ALL  | idx_donetime           | NULL      | NULL    | NULL              | 189048 | Using where; Using temporary |
|  2 | DEPENDENT SUBQUERY | bored | ref  | idx_accid,idx_donetime | idx_accid | 4       | InGameAD.a.acc_id |    266 | Using where; Using temporary |
+----+--------------------+-------+------+------------------------+-----------+---------+-------------------+--------+------------------------------+
测试返回结果用时47秒。

 

改进2)，使用多一个临时表

mysql> explain select * from (select DISTINCT acc_id from bored where done_time between '2008-05-15' and '2008-05-23') a  where not EXISTS(SELECT DISTINCT acc_id from bored where bored.acc_id=a.acc_id and  bored.done_time<'2008-05-15' );               +----+--------------------+------------+------+------------------------+-----------+---------+----------+--------+------------------------------+
| id | select_type        | table      | type | possible_keys          | key       | key_len | ref      | rows   | Extra                        |
+----+--------------------+------------+------+------------------------+-----------+---------+----------+--------+------------------------------+
|  1 | PRIMARY            | | ALL  | NULL                   | NULL      | NULL    | NULL     |    476 | Using where                  |
|  3 | DEPENDENT SUBQUERY | bored      | ref  | idx_accid,idx_donetime | idx_accid | 4       | a.acc_id |    266 | Using where; Using temporary |
|  2 | DERIVED            | bored      | ALL  | idx_donetime           | NULL      | NULL    | NULL     | 189048 | Using where; Using temporary |
+----+--------------------+------------+------+------------------------+-----------+---------+----------+--------+------------------------------+
3 rows in set (0.18 sec)

测试返回结果用时0.4秒。

 

小结：要注意DISTINCT在查询中的位置和次序，尽可能先执行。