数据库有39万条数据我在需要查询的3字段上面加了索引 mysql> SELECT * FROM `partner`.`vas_cpmt_log` where adate between '2010-07-27' and '2010-07-28'
and serviceid = 'CPKayIVR0100T ' and cptelco = 'dtac' order by adate desc limit 0, 100;上面的语句执行起来起来就1秒不到但是查询总记录数的时候就不行了mysql> SELECT count(*) FROM `partner`.`vas_cpmt_log` where adate between '2010-07-27' and '2010-07-28'
and serviceid = 'CPKayIVR0100T ' and cptelco = 'dtac';下面这个语句要执行8秒有谁知道怎么样优化,在线急等
and serviceid = 'CPKayIVR0100T ' and cptelco = 'dtac' order by adate desc limit 0, 100;上面的语句执行起来起来就1秒不到但是查询总记录数的时候就不行了mysql> SELECT count(*) FROM `partner`.`vas_cpmt_log` where adate between '2010-07-27' and '2010-07-28'
and serviceid = 'CPKayIVR0100T ' and cptelco = 'dtac';下面这个语句要执行8秒有谁知道怎么样优化,在线急等
+--------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| mtid | varchar(30) | NO | PRI | NULL | |
| adate | datetime | YES | MUL | NULL | |
| linkid | varchar(50) | YES | | NULL | |
| telco | char(4) | YES | | NULL | |
| cptelco | varchar(10) | YES | MUL | NULL | |
| channeltype | varchar(10) | YES | | NULL | |
| serviceid | varchar(20) | YES | MUL | NULL | |
| srcnumber | varchar(30) | YES | | NULL | |
| destnumber | varchar(30) | YES | | NULL | |
| chargenumber | varchar(30) | YES | | NULL | |
| extendcode | varchar(20) | YES | | NULL | |
| msgtype | varchar(10) | YES | | NULL | |
| content | varchar(8000) | YES | | NULL | |
| rpt_status | int(11) | YES | | NULL | |
| rpt_re | varchar(200) | YES | | NULL | |
+--------------+---------------+------+-----+---------+-------+表结构
不知道对你有没有帮助
建议楼主优化数据库设置
谢谢上面朋友的看法,知道的麻烦说下优化的思路