求解，mysql查询建立索引后查询速度反而不如未建立索引以前
mysql 符合索引，未建立以前用时1.72s，建立一个符合索引后，反而用时2.44s，时间反而更长，是哪的问题呢，求解。。
以下是表结构和mysql explain 后的结果:
mysql> show create table new_bind_mp_weibo_user_info_0; +----+| Table | Create Table |+---+ | new_bind_mp_weibo_user_info_0 | CREATE TABLE `new_bind_mp_weibo_user_info_0` ( `id` int(11) NOT NULL AUTO_INCREMENT, `weiboid` varchar(20) NOT NULL, `weiboun` varchar(100) NOT NULL, `mpnumber` varchar(11) NOT NULL, `apmac` varchar(100) NOT NULL, `bindtime` datetime NOT NULL, `source` varchar(255) NOT NULL, PRIMARY KEY (`id`), KEY `search_key` (`bindtime`) ) ENGINE=MyISAM AUTO_INCREMENT=2768 DEFAULT CHARSET=utf8 | +-------------------------------+-----------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table weibo_user_visit_info_0; +-----------------+| Table | Create Table |+---------------------------------+ | weibo_user_visit_info_0 | CREATE TABLE `weibo_user_visit_info_0` ( `id` int(11) NOT NULL AUTO_INCREMENT, `weiboid` varchar(20) NOT NULL, `weiboun` varchar(50) NOT NULL, `apmac` varchar(100) DEFAULT NULL, `usermac` varchar(100) DEFAULT NULL, `mpnumber` varchar(11) DEFAULT NULL, `visittime` datetime NOT NULL, `source` varchar(255) NOT NULL, PRIMARY KEY (`id`), KEY `weiboid` (`weiboid`) ) ENGINE=MyISAM AUTO_INCREMENT=17952 DEFAULT CHARSET=utf8 | +-------------------------+--------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table weibo_user_visit_info_0 add index `search_index` (`apmac`(17),`visittime`); Query OK, 17944 rows affected (0.21 sec) Records: 17944 Duplicates: 0 Warnings: 0 mysql> explain select sql_no_cache count(distinct(a.mpnumber)) as mpnumber, date_format(a.bindtime,'%Y-%m-%d') as gDate from new_bind_mp_weibo_user_info_0 a left join weibo_user_visit_info_0 b on a.mpnumber=b.mpnumber and a.apmac=b.apmac where b.id is NULL and a.apmac in (select ap_mac from newwifi.business_installedAP_info where businessid=100064) and a.bindtime between '2012-03-01' and '2012-03-23 23:59:59' group by gDate order by gDate; +----+--------------------+---------------------------+-------+---------------+--------------+---------+----------------------------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+---------------------------+-------+---------------+--------------+---------+----------------------------+------+-----------------------------+ | 1 | PRIMARY | a | range | search_key | search_key | 8 | NULL | 394 | Using where; Using filesort | | 1 | PRIMARY | b | ref | search_index | search_index | 54 | newwifi_visit_info.a.apmac | 142 | Using where; Not exists | | 2 | DEPENDENT SUBQUERY | business_installedAP_info | ref | search_key | search_key | 306 | const,func | 1 | Using index | +----+--------------------+---------------------------+-------+---------------+--------------+---------+----------------------------+------+-----------------------------+ 3 rows in set (0.00 sec) mysql> select sql_no_cache count(distinct(a.mpnumber)) as mpnumber, date_format(a.bindtime,'%Y-%m-%d') as gDate from new_bind_mp_weibo_user_info_0 a left join weibo_user_visit_info_0 b on a.mpnumber=b.mpnumber and a.apmac=b.apmac where b.id is NULL and a.apmac in (select ap_mac from newwifi.business_installedAP_info where businessid=100064) and a.bindtime between '2012-03-01' and '2012-03-23 23:59:59' group by gDate order by gDate; +----------+------------+ | mpnumber | gDate | +----------+------------+ | 4 | 2012-03-01 | | 10 | 2012-03-02 | | 3 | 2012-03-03 | | 10 | 2012-03-04 | | 12 | 2012-03-05 | | 10 | 2012-03-08 | | 4 | 2012-03-09 | | 14 | 2012-03-12 | | 14 | 2012-03-13 | | 7 | 2012-03-14 | | 12 | 2012-03-15 | | 8 | 2012-03-16 | | 7 | 2012-03-17 | | 11 | 2012-03-18 | | 11 | 2012-03-19 | | 8 | 2012-03-20 | | 8 | 2012-03-21 | | 9 | 2012-03-22 | +----------+------------+ 18 rows in set (2.44 sec) mysql> alter table weibo_user_visit_info_0 drop index search_index; Query OK, 17944 rows affected (0.13 sec) Records: 17944 Duplicates: 0 Warnings: 0 mysql> explain select sql_no_cache count(distinct(a.mpnumber)) as mpnumber, date_format(a.bindtime,'%Y-%m-%d') as gDate from new_bind_mp_weibo_user_info_0 a left join weibo_user_visit_info_0 b on a.mpnumber=b.mpnumber and a.apmac=b.apmac where b.id is NULL and a.apmac in (select ap_mac from newwifi.business_installedAP_info where businessid=100064) and a.bindtime between '2012-03-01' and '2012-03-23 23:59:59' group by gDate order by gDate; +----+--------------------+---------------------------+-------+---------------+------------+---------+------------+-------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+---------------------------+-------+---------------+------------+---------+------------+-------+----------------------------------------------+ | 1 | PRIMARY | a | range | search_key | search_key | 8 | NULL | 394 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | b | ALL | NULL | NULL | NULL | NULL | 17944 | Using where; Not exists | | 2 | DEPENDENT SUBQUERY | business_installedAP_info | ref | search_key | search_key | 306 | const,func | 1 | Using index | +----+--------------------+---------------------------+-------+---------------+------------+---------+------------+-------+----------------------------------------------+ 3 rows in set (0.00 sec) mysql> select sql_no_cache count(distinct(a.mpnumber)) as mpnumber, date_format(a.bindtime,'%Y-%m-%d') as gDate from new_bind_mp_weibo_user_info_0 a left join weibo_user_visit_info_0 b on a.mpnumber=b.mpnumber and a.apmac=b.apmac where b.id is NULL and a.apmac in (select ap_mac from newwifi.business_installedAP_info where businessid=100064) and a.bindtime between '2012-03-01' and '2012-03-23 23:59:59' group by gDate order by gDate; +----------+------------+ | mpnumber | gDate | +----------+------------+ | 4 | 2012-03-01 | | 10 | 2012-03-02 | | 3 | 2012-03-03 | | 10 | 2012-03-04 | | 12 | 2012-03-05 | | 10 | 2012-03-08 | | 4 | 2012-03-09 | | 14 | 2012-03-12 | | 14 | 2012-03-13 | | 7 | 2012-03-14 | | 12 | 2012-03-15 | | 8 | 2012-03-16 | | 7 | 2012-03-17 | | 11 | 2012-03-18 | | 11 | 2012-03-19 | | 8 | 2012-03-20 | | 8 | 2012-03-21 | | 9 | 2012-03-22 | +----------+------------+ 18 rows in set (1.72 sec)
