是一个关于mysql的问题.语句如下:
(SELECT n.nid,n.created,u.dst, NULL as tid FROM en_node n LEFT JOIN en_url_alias u ON n.nid = SUBSTRING(u.src, 6) ORDER BY n.created DESC LIMIT 20) en_node en_url_alias 数据量在900条左右,而查询此句sql,竟然花掉1.3秒之多.
去掉 ORDER BY n.created DESC ,时间是0.02左右.
总共查询出的结果就才20条,一个order by,为何花费这么多时间? 两个表的结构如下 :
CREATE TABLE IF NOT EXISTS `en_node` (
`nid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`vid` int(10) unsigned NOT NULL DEFAULT '0',
`type` varchar(32) NOT NULL DEFAULT '',
`language` varchar(12) NOT NULL DEFAULT '',
`title` varchar(255) NOT NULL DEFAULT '',
`uid` int(11) NOT NULL DEFAULT '0',
`status` int(11) NOT NULL DEFAULT '1',
`created` int(11) NOT NULL DEFAULT '0',
`changed` int(11) NOT NULL DEFAULT '0',
`comment` int(11) NOT NULL DEFAULT '0',
`promote` int(11) NOT NULL DEFAULT '0',
`moderate` int(11) NOT NULL DEFAULT '0',
`sticky` int(11) NOT NULL DEFAULT '0',
`tnid` int(10) unsigned NOT NULL DEFAULT '0',
`translate` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`nid`),
UNIQUE KEY `vid` (`vid`),
KEY `node_changed` (`changed`),
KEY `node_created` (`created`),
KEY `node_moderate` (`moderate`),
KEY `node_promote_status` (`promote`,`status`),
KEY `node_status_type` (`status`,`type`,`nid`),
KEY `node_title_type` (`title`,`type`(4)),
KEY `node_type` (`type`(4)),
KEY `uid` (`uid`),
KEY `tnid` (`tnid`),
KEY `translate` (`translate`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1133 ;CREATE TABLE IF NOT EXISTS `en_url_alias` (
`pid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`src` varchar(128) NOT NULL DEFAULT '',
`dst` varchar(128) NOT NULL DEFAULT '',
`language` varchar(12) NOT NULL DEFAULT '',
PRIMARY KEY (`pid`),
UNIQUE KEY `dst_language_pid` (`dst`,`language`,`pid`),
KEY `src_language_pid` (`src`,`language`,`pid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=886 ;请高人指点,本语句该如何优化?
(SELECT n.nid,n.created,u.dst, NULL as tid FROM en_node n LEFT JOIN en_url_alias u ON n.nid = SUBSTRING(u.src, 6) ORDER BY n.created DESC LIMIT 20) en_node en_url_alias 数据量在900条左右,而查询此句sql,竟然花掉1.3秒之多.
去掉 ORDER BY n.created DESC ,时间是0.02左右.
总共查询出的结果就才20条,一个order by,为何花费这么多时间? 两个表的结构如下 :
CREATE TABLE IF NOT EXISTS `en_node` (
`nid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`vid` int(10) unsigned NOT NULL DEFAULT '0',
`type` varchar(32) NOT NULL DEFAULT '',
`language` varchar(12) NOT NULL DEFAULT '',
`title` varchar(255) NOT NULL DEFAULT '',
`uid` int(11) NOT NULL DEFAULT '0',
`status` int(11) NOT NULL DEFAULT '1',
`created` int(11) NOT NULL DEFAULT '0',
`changed` int(11) NOT NULL DEFAULT '0',
`comment` int(11) NOT NULL DEFAULT '0',
`promote` int(11) NOT NULL DEFAULT '0',
`moderate` int(11) NOT NULL DEFAULT '0',
`sticky` int(11) NOT NULL DEFAULT '0',
`tnid` int(10) unsigned NOT NULL DEFAULT '0',
`translate` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`nid`),
UNIQUE KEY `vid` (`vid`),
KEY `node_changed` (`changed`),
KEY `node_created` (`created`),
KEY `node_moderate` (`moderate`),
KEY `node_promote_status` (`promote`,`status`),
KEY `node_status_type` (`status`,`type`,`nid`),
KEY `node_title_type` (`title`,`type`(4)),
KEY `node_type` (`type`(4)),
KEY `uid` (`uid`),
KEY `tnid` (`tnid`),
KEY `translate` (`translate`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1133 ;CREATE TABLE IF NOT EXISTS `en_url_alias` (
`pid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`src` varchar(128) NOT NULL DEFAULT '',
`dst` varchar(128) NOT NULL DEFAULT '',
`language` varchar(12) NOT NULL DEFAULT '',
PRIMARY KEY (`pid`),
UNIQUE KEY `dst_language_pid` (`dst`,`language`,`pid`),
KEY `src_language_pid` (`src`,`language`,`pid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=886 ;请高人指点,本语句该如何优化?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货