SELECT ai.`code`, c.mobilePhone
,co.`name`,cc.createdTime
FROM
ClientCoupon AS cc
INNER JOIN ActivityInfo AS ai ON ai.sn = cc.activitySn
INNER JOIN Client AS c ON cc.clientSn = c.sn
INNER JOIN Coupon AS co ON co.sn = cc.couponSn ,
ClientCoupon
WHERE 1 = 1
AND co.sn not in (3, 5, 6)
AND ai.code in ('10000006', '10000007', '10000008', '10000001', '10000002')
AND ai.partStartTime <= '2015-12-18 16:42:00'
AND ai.partEndTime >= '2015-12-18 16:42:00'
ORDER BY co.amount DESC, cc.createdTime DESC
limit 0, 10000;
Vsftp:/tmp# ls -ltrh
total 409M
drwx------. 2 root root 4.0K Mar 8 2016 keyring-NKnAWe
lrwxrwxrwx 1 root root 24 Oct 28 09:43 mysql.sock -> /data01/mysql/mysql.sock
drwx------ 2 tomcat tomcat 4.0K Nov 8 10:09 pulse-n4FjBj0h36vp
drwx------ 2 gdm gdm 4.0K Nov 29 13:43 orbit-gdm
drwx------ 2 root root 4.0K Nov 29 13:43 keyring-0Z0NaP
drwx------. 2 root root 4.0K Nov 29 13:43 pulse-bwFjVHZn5SDK
drwx------ 2 root root 4.0K Nov 29 13:43 virtual-root.H3BWKr
drwx------ 2 gdm gdm 4.0K Nov 29 13:43 pulse-CM0yro2khZRx
drwx------ 2 root root 4.0K Nov 29 13:43 orbit-root
drwxr-xr-x 2 elk elk 4.0K Dec 9 10:01 hsperfdata_elk
drwxr-xr-x 2 root root 4.0K Dec 15 17:36 hsperfdata_root
-rw-rw-r-- 1 zabbix zabbix 1.1M Dec 16 23:59 zabbix_agentd.log.old
-rw-rw-r-- 1 zabbix zabbix 566K Dec 22 10:45 zabbix_agentd.log
-rw-rw---- 1 mysql mysql 1.0K Dec 22 10:51 #sql_6e7f_0.MYI
-rw-rw---- 1 mysql mysql 407M Dec 22 10:51 #sql_6e7f_0.MYD
mysql> explain SELECT ai.`code`, c.mobilePhone ,co.`name`,cc.createdTime FROM ClientCoupon AS cc INNER JOIN ActivityInfo AS ai ON ai.sn = cc.activitySn INNER JOIN Client AS c ON cc.clientSn = c.sn INNER JOIN Coupon AS co ON co.sn = cc.couponSn , ClientCoupon WHERE 1 = 1 AND co.sn not in (3, 5, 6) AND ai.code in ('10000006', '10000007', '10000008', '10000001', '10000002') AND ai.partStartTime <= '2015-12-18 16:42:00' AND ai.partEndTime >= '2015-12-18 16:42:00' ORDER BY co.amount DESC, cc.createdTime DESC limit 0, 10000;
+----+-------------+--------------+--------+-------------------+---------+---------+--------------------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+--------+-------------------+---------+---------+--------------------+------+----------------------------------------------------+
| 1 | SIMPLE | cc | ALL | ClientCoupon_idx1 | NULL | NULL | NULL | 8316 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | ai | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.cc.activitySn | 1 | Using where |
| 1 | SIMPLE | co | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.cc.couponSn | 1 | NULL |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.cc.clientSn | 1 | NULL |
| 1 | SIMPLE | ClientCoupon | index | NULL | sn | 4 | NULL | 8316 | Using index; Using join buffer (Block Nested Loop) |
+----+-------------+--------------+--------+-------------------+---------+---------+--------------------+------+----------------------------------------------------+
5 rows in set (0.00 sec)