表结构如下DROP TABLE IF EXISTS `t_dbxx_201203`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `t_db_201203` (
`Fcompany_id` varchar(60) NOT NULL DEFAULT '',
`Fmsgid` bigint(20) unsigned NOT NULL DEFAULT '0',
`Freal_msgid` bigint(20) unsigned NOT NULL DEFAULT '0',
`Ftime_recv_uint` int(10) unsigned NOT NULL DEFAULT '0',
`Fservertime_recv` datetime NOT NULL,
`Fcode` varchar(60) NOT NULL DEFAULT '',
`Fmomsg` text NOT NULL,
.....
.....
..... `Fid` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`Fid`),
UNIQUE KEY `Freal_msgid` (`Freal_msgid`),
UNIQUE KEY `Flinkid` (`Flinkid`)
) ENGINE=MyISAM AUTO_INCREMENT=22430 DEFAULT CHARSET=gbk;
SET character_set_client = @saved_cs_client;查询语句如下select Fcode,Fmomsg,count(1) from t_db_201203 where Freport_stat in ('DELIVRD','0') and Fservertime_recv>='2012-03-01 00:00:00' and Fservertime_recv<='2012-03-30 00:00:00' group by Fcode,Fmomsg order by Fcode,Fmomsg;
表数据很大,查询很慢,怎么建立index,优化查询啊?
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `t_db_201203` (
`Fcompany_id` varchar(60) NOT NULL DEFAULT '',
`Fmsgid` bigint(20) unsigned NOT NULL DEFAULT '0',
`Freal_msgid` bigint(20) unsigned NOT NULL DEFAULT '0',
`Ftime_recv_uint` int(10) unsigned NOT NULL DEFAULT '0',
`Fservertime_recv` datetime NOT NULL,
`Fcode` varchar(60) NOT NULL DEFAULT '',
`Fmomsg` text NOT NULL,
.....
.....
..... `Fid` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`Fid`),
UNIQUE KEY `Freal_msgid` (`Freal_msgid`),
UNIQUE KEY `Flinkid` (`Flinkid`)
) ENGINE=MyISAM AUTO_INCREMENT=22430 DEFAULT CHARSET=gbk;
SET character_set_client = @saved_cs_client;查询语句如下select Fcode,Fmomsg,count(1) from t_db_201203 where Freport_stat in ('DELIVRD','0') and Fservertime_recv>='2012-03-01 00:00:00' and Fservertime_recv<='2012-03-30 00:00:00' group by Fcode,Fmomsg order by Fcode,Fmomsg;
表数据很大,查询很慢,怎么建立index,优化查询啊?
解决方案 »
- xpe操作系统 安装 mysql front 乱码
- 时间长后连接池清空,并且不能重新获取问题.
- 怎么能够在My.ini里面设置SET GLOBAL event_scheduler = 1;
- mysql自动递增id怎么样快速回复到无间隔递增?
- 这个sql语句查出来的数据怎么绑定到repeater
- 小问题
- select * from om_policy order by 10.11 desc limit 10
- mysql存储过程 到底是什么呢?
- MYSQL中mysql_fetch_field()函数获取的MYSQL_FIELD指针需要释放么?
- 关于聚类 的问题
- 高手给看看这是什么原因啊
- 求一条查询语句
没看到东东
to:二楼
Using where; Using temporary; Using filesort 没用到
| t_db_201203 | 0 | PRIMARY | 1 | Fid | A | 22429 | NULL | NULL | | BTREE | | |
| t_db_201203 | 0 | Freal_msgid | 1 | Freal_msgid | A | 22429 | NULL | NULL | | BTREE | | |
| t_db_201203 | 0 | Flinkid | 1 | Flinkid | A | 22429 | NULL | NULL | | BTREE | | |
| t_db_201203 | 1 | idx_db_201203_01 | 1 | Freport_stat | A | 1 | NULL | NULL | | BTREE | | |
| t_db_201203 | 1 | idx_db_201203_01 | 2 | Fservertime_recv | A | 22429 | NULL | NULL | | BTREE | | |
| t_db_201203 | 1 | idx_db_201203_01 | 3 | Fcode | A | 22429 | NULL | NULL | | BTREE | | |
| t_db_201203 | 1 | idx_db_201203_01 | 4 | Fmomsg | A | 22429 | 200 | NULL | | BTREE | | |
| t_db_201203 | 1 | Fservertime_recv | 1 | Fservertime_recv | A | 22429 | NULL | NULL | | BTREE | | |
| t_db_201203 | 1 | Fservertime_recv | 2 | Freport_stat | A | 22429 | NULL | NULL | | BTREE | | |
Using where; Using temporary; Using filesort 没用
看看具体执行需要多长时间 不要看explain
create index x2 on t_db_201203 (Freport_stat,Fservertime_recv);
create index x3 on t_db_201203 ( Fcode,Fmomsg,Freport_stat,Fservertime_recv);
三列 但是是两个字段
这样
alter table t_db_201203 add index ix_test(Freport_stat,Fservertime_recv);
结果+----+-------------+---------------+------+---------------+------+---------+------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+------+---------+------+-------+----------------------------------------------+
| 1 | SIMPLE | t_db_201203 | ALL | NULL | NULL | NULL | NULL | 22429 | Using where; Using temporary; Using filesort |
+----+-------------+---------------+------+---------------+------+---------+------+-------+----------------------------------------------+
1 row in set (0.00 sec)