标签为 "extended" 的存档

求解,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)

Read more…