SELECT `Cmbook`.`id` AS `id`, `Cmbook`.`bid` AS `bid`, `Cmbook`.`name` AS `name`, `Cmbook`.`author` AS `author`, `Cmbook`.`introduction` AS `introduction`, `Cmbook`.`cover_url` AS `cover_url`, `NewBook`.`come` FROM `new_books` AS `NewBook` LEFT JOIN cmbooks AS `Cmbook` ON (`Cmbook`.`bid` = `NewBook`.`bid`) WHERE `NewBook`.`class_id` = 3007 AND `NewBook`.`come` = 0 ORDER BY `NewBook`.`position` desc, `NewBook`.`id` ASC LIMIT 20 我只是取20条数据 可是竟然用了1分钟
EXPLAIN SELECT `Cmbook`.`id` AS `id` , `Cmbook`.`bid` AS `bid` , `Cmbook`.`name` AS `name` , `Cmbook`.`author` AS `author` , `Cmbook`.`introduction` AS `introduction` , `Cmbook`.`cover_url` AS `cover_url` , `NewBook`.`come`
FROM `new_books` AS `NewBook`
LEFT JOIN cmbooks AS `Cmbook` ON ( `Cmbook`.`bid` = `NewBook`.`bid` )
WHERE `NewBook`.`class_id` =3007
AND `NewBook`.`come` =0
ORDER BY `NewBook`.`position` DESC , `NewBook`.`id` ASC
LIMIT 20 的结果如下id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NewBook ref class_id class_id 4 const 1026 Using where; Using temporary; Using filesort
1 SIMPLE Cmbook ALL bid NULL NULL NULL 24497 CREATE TABLE IF NOT EXISTS `cmbooks` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bid` varchar(16) DEFAULT NULL,
`cid` varchar(30) DEFAULT NULL COMMENT '第一章的cid',
`name` varchar(128) DEFAULT NULL,
`author` varchar(64) DEFAULT NULL,
`introduction` varchar(512) DEFAULT NULL,
`cover_url` varchar(128) DEFAULT NULL,
`status` tinyint(4) DEFAULT '0' COMMENT '0:unknown 1:updateing 2:finished',
`click_num` varchar(40) NOT NULL DEFAULT '' COMMENT '点击',
`word_num` varchar(40) NOT NULL DEFAULT '' COMMENT '字数',
`class` int(10) NOT NULL DEFAULT '0' COMMENT '分类',
`word_scope` int(2) NOT NULL DEFAULT '0' COMMENT '字数范围 1(0-20)2(20-50)3(50-100)4(100-200)5(200-300)6 (300以上)',
`like` varchar(500) NOT NULL DEFAULT '' COMMENT '猜你喜欢',
PRIMARY KEY (`id`),
UNIQUE KEY `bid` (`bid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=24863 ;CREATE TABLE IF NOT EXISTS `new_books` (
`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`class_id` int(5) NOT NULL DEFAULT '0' COMMENT '三极分类id',
`bid` int(15) NOT NULL DEFAULT '0' COMMENT '书的bid',
`come` int(1) NOT NULL DEFAULT '0' COMMENT '0代表采集的1代表自定义',
`position` int(5) NOT NULL DEFAULT '0' COMMENT '位置',
PRIMARY KEY (`id`),
KEY `class_id` (`class_id`),
KEY `bid` (`bid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='新书库表' AUTO_INCREMENT=32837 ;
索引我也加过了 可是为什么这么慢怎么优化 cmbooks 表有25000条记录 new_books 表有30000条记录
EXPLAIN SELECT `Cmbook`.`id` AS `id` , `Cmbook`.`bid` AS `bid` , `Cmbook`.`name` AS `name` , `Cmbook`.`author` AS `author` , `Cmbook`.`introduction` AS `introduction` , `Cmbook`.`cover_url` AS `cover_url` , `NewBook`.`come`
FROM `new_books` AS `NewBook`
LEFT JOIN cmbooks AS `Cmbook` ON ( `Cmbook`.`bid` = `NewBook`.`bid` )
WHERE `NewBook`.`class_id` =3007
AND `NewBook`.`come` =0
ORDER BY `NewBook`.`position` DESC , `NewBook`.`id` ASC
LIMIT 20 的结果如下id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NewBook ref class_id class_id 4 const 1026 Using where; Using temporary; Using filesort
1 SIMPLE Cmbook ALL bid NULL NULL NULL 24497 CREATE TABLE IF NOT EXISTS `cmbooks` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bid` varchar(16) DEFAULT NULL,
`cid` varchar(30) DEFAULT NULL COMMENT '第一章的cid',
`name` varchar(128) DEFAULT NULL,
`author` varchar(64) DEFAULT NULL,
`introduction` varchar(512) DEFAULT NULL,
`cover_url` varchar(128) DEFAULT NULL,
`status` tinyint(4) DEFAULT '0' COMMENT '0:unknown 1:updateing 2:finished',
`click_num` varchar(40) NOT NULL DEFAULT '' COMMENT '点击',
`word_num` varchar(40) NOT NULL DEFAULT '' COMMENT '字数',
`class` int(10) NOT NULL DEFAULT '0' COMMENT '分类',
`word_scope` int(2) NOT NULL DEFAULT '0' COMMENT '字数范围 1(0-20)2(20-50)3(50-100)4(100-200)5(200-300)6 (300以上)',
`like` varchar(500) NOT NULL DEFAULT '' COMMENT '猜你喜欢',
PRIMARY KEY (`id`),
UNIQUE KEY `bid` (`bid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=24863 ;CREATE TABLE IF NOT EXISTS `new_books` (
`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`class_id` int(5) NOT NULL DEFAULT '0' COMMENT '三极分类id',
`bid` int(15) NOT NULL DEFAULT '0' COMMENT '书的bid',
`come` int(1) NOT NULL DEFAULT '0' COMMENT '0代表采集的1代表自定义',
`position` int(5) NOT NULL DEFAULT '0' COMMENT '位置',
PRIMARY KEY (`id`),
KEY `class_id` (`class_id`),
KEY `bid` (`bid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='新书库表' AUTO_INCREMENT=32837 ;
索引我也加过了 可是为什么这么慢怎么优化 cmbooks 表有25000条记录 new_books 表有30000条记录
解决方案 »
- 新人提问:这个问题也太诡异了吧。。关于RSS的基础应用
- 请xuzuning来接收,感谢你的回答!顺便再请教一个小问题。
- 保持身体健康,,散分了
- 如何模拟卓越亚马逊购买的页眉tab导航?
- MD5的加密后的密码修改问题
- php中开启iconv的方法,及怎么utf8->gb2312
- windows下与linux下$_get有什么不同?
- [PHP]php5.1.0b3运行php时只能有一个<? ?>
- 高分求载取繁体中文和简体中文的函数。
- 适合于初学者的部分问题及常用代码下载,希望大家能把自己收藏的一些源代码打包发给我,我一起打包提供下载
- 着急!快快快!!!!
- 请教有用过 Sphinx 吗?应该怎么跟php结合使用?
倒过来写
ON ( `NewBook`.`bid` = `Cmbook`.`bid` )
需要用left join吗?
另外, 为何一个是innodb,一个是MyISAM ?
order by后的字段也加上索引看看吧