mysql 查询 Copying to tmp table 疑问

[sujin190]

select mobile, count(*) as cnt from trading_order where order_at>='2015-04-04 00:00:00' and order_at<'2015-04-17 00:00:00' and mobile>'' and status>3000 and mobile in (select o.mobile from trading_order_goods g left join trading_order o on g.order_id=o.order_id where g.trading_id='551e656c3f5bdd24568b4567' and o.order_at>='2015-04-04 00:00:00' and o.order_at<'2015-04-17 00:00:00' and o.mobile>'' and o.status>3000 group by o.mobile );

InnoDB引擎，2w数据，查询时间超过60s，explain
+----+--------------------+---------------+--------+--------------------------------------------+------------------+---------+-----------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------------+--------+--------------------------------------------+------------------+---------+-----------------+------+----------------------------------------------+
| 1 | PRIMARY | trading_order | range | index_mobile,index_order_at | index_order_at | 4 | NULL | 2959 | Using where; Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | g | ref | index_order_id,index_trading_id | index_trading_id | 74 | const | 1659 | Using where; Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | o | eq_ref | index_order_id,index_mobile,index_order_at | index_order_id | 74 | gege.g.order_id | 1 | Using where |
+----+--------------------+---------------+--------+--------------------------------------------+------------------+---------+-----------------+------+----------------------------------------------+
3 rows in set (0.00 sec)

show processlist查看发现卡在了
Copying to tmp table | select mobile, count(*) as cnt from trading_order where order_at>='2015-04-04 00:00:00' and order_at
这是为什么呢？

[hahasong]

搞联合查询带这么多条件还玩子句，不慢才怪。明显不合理。在代码里拆分一下吧，宁可拆成二次查询

[sujin190]

@hahasong 可是就算如此，join那部分就很快啊，万条数据而已，太不正常了吧

[ElmerZhang]

你这个SQL的扫描行数按explain的结果来看，大概会是 2959 * 1659 * 1 ＝ 4908981

[sujin190]

@ElmerZhang mysql这时候要扫描这么多数据么？这种情况和直接把手机号写在in里有什么区别呢？

[whiteblack]

DEPENDENT SUBQUERY 的问题，这里涉及到in的执行过程，具体看这篇博文

http://www.cnblogs.com/zhengyun_ustc/p/slowquery3.html

[sujin190]

@xiaobaigsy 好吧，了解了，感谢，好坑啊，为什么要设计成这样啊？

[zhanglp888]

有了group by后，必然会慢

[whiteblack]

@sujin190 用久了mysql 就知道了，这玩意全是坑。。。。已经不知道发现多少诡异的mysql问题，最后了解到是mysql的bug了。。。