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 这是为什么呢？