这是discuz的一个表结构.
CREATE TABLE `pre_forum_thread` (
`tid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`fid` mediumint(8) unsigned NOT NULL DEFAULT '0',
`posttableid` smallint(6) unsigned NOT NULL DEFAULT '0',
`typeid` smallint(6) unsigned NOT NULL DEFAULT '0',
`sortid` smallint(6) unsigned NOT NULL DEFAULT '0',
`readperm` tinyint(3) unsigned NOT NULL DEFAULT '0',
`price` smallint(6) NOT NULL DEFAULT '0',
`author` char(15) NOT NULL DEFAULT '',
`authorid` mediumint(8) unsigned NOT NULL DEFAULT '0',
`subject` char(80) NOT NULL DEFAULT '',
`dateline` int(10) unsigned NOT NULL DEFAULT '0',
`lastpost` int(10) unsigned NOT NULL DEFAULT '0',
`lastposter` char(15) NOT NULL DEFAULT '',
`views` int(10) unsigned NOT NULL DEFAULT '0',
`replies` mediumint(8) unsigned NOT NULL DEFAULT '0',
`displayorder` tinyint(1) NOT NULL DEFAULT '0',
`highlight` tinyint(1) NOT NULL DEFAULT '0',
`digest` tinyint(1) NOT NULL DEFAULT '0',
`rate` tinyint(1) NOT NULL DEFAULT '0',
`special` tinyint(1) NOT NULL DEFAULT '0',
`attachment` tinyint(1) NOT NULL DEFAULT '0',
`moderated` tinyint(1) NOT NULL DEFAULT '0',
`closed` mediumint(8) unsigned NOT NULL DEFAULT '0',
`stickreply` tinyint(1) unsigned NOT NULL DEFAULT '0',
`recommends` smallint(6) NOT NULL DEFAULT '0',
`recommend_add` smallint(6) NOT NULL DEFAULT '0',
`recommend_sub` smallint(6) NOT NULL DEFAULT '0',
`heats` int(10) unsigned NOT NULL DEFAULT '0',
`status` smallint(6) unsigned NOT NULL DEFAULT '0',
`isgroup` tinyint(1) NOT NULL DEFAULT '0',
`favtimes` mediumint(8) NOT NULL DEFAULT '0',
`sharetimes` mediumint(8) NOT NULL DEFAULT '0',
`stamp` tinyint(3) NOT NULL DEFAULT '-1',
`icon` tinyint(3) NOT NULL DEFAULT '-1',
`pushedaid` mediumint(8) NOT NULL DEFAULT '0',
`cover` smallint(6) NOT NULL DEFAULT '0',
`replycredit` smallint(6) NOT NULL DEFAULT '0',
`relatebytag` char(255) NOT NULL DEFAULT '0',
`maxposition` int(8) unsigned NOT NULL DEFAULT '0',
`bgcolor` char(8) NOT NULL DEFAULT '',
`comments` int(10) unsigned NOT NULL DEFAULT '0',
`hidden` smallint(6) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`tid`),
KEY `digest` (`digest`),
KEY `sortid` (`sortid`),
KEY `displayorder` (`fid`,`displayorder`,`lastpost`),
KEY `typeid` (`fid`,`typeid`,`displayorder`,`lastpost`),
KEY `recommends` (`recommends`),
KEY `heats` (`heats`),
KEY `authorid` (`authorid`),
KEY `isgroup` (`isgroup`,`lastpost`),
KEY `special` (`special`),
KEY `fid` (`fid`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=gbk既然建立了typeid 这个索引. 为什么还需要 displayorder这个索引 目的是什么?
KEY `displayorder` (`fid`,`displayorder`,`lastpost`),
KEY `typeid` (`fid`,`typeid`,`displayorder`,`lastpost`),
CREATE TABLE `pre_forum_thread` (
`tid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`fid` mediumint(8) unsigned NOT NULL DEFAULT '0',
`posttableid` smallint(6) unsigned NOT NULL DEFAULT '0',
`typeid` smallint(6) unsigned NOT NULL DEFAULT '0',
`sortid` smallint(6) unsigned NOT NULL DEFAULT '0',
`readperm` tinyint(3) unsigned NOT NULL DEFAULT '0',
`price` smallint(6) NOT NULL DEFAULT '0',
`author` char(15) NOT NULL DEFAULT '',
`authorid` mediumint(8) unsigned NOT NULL DEFAULT '0',
`subject` char(80) NOT NULL DEFAULT '',
`dateline` int(10) unsigned NOT NULL DEFAULT '0',
`lastpost` int(10) unsigned NOT NULL DEFAULT '0',
`lastposter` char(15) NOT NULL DEFAULT '',
`views` int(10) unsigned NOT NULL DEFAULT '0',
`replies` mediumint(8) unsigned NOT NULL DEFAULT '0',
`displayorder` tinyint(1) NOT NULL DEFAULT '0',
`highlight` tinyint(1) NOT NULL DEFAULT '0',
`digest` tinyint(1) NOT NULL DEFAULT '0',
`rate` tinyint(1) NOT NULL DEFAULT '0',
`special` tinyint(1) NOT NULL DEFAULT '0',
`attachment` tinyint(1) NOT NULL DEFAULT '0',
`moderated` tinyint(1) NOT NULL DEFAULT '0',
`closed` mediumint(8) unsigned NOT NULL DEFAULT '0',
`stickreply` tinyint(1) unsigned NOT NULL DEFAULT '0',
`recommends` smallint(6) NOT NULL DEFAULT '0',
`recommend_add` smallint(6) NOT NULL DEFAULT '0',
`recommend_sub` smallint(6) NOT NULL DEFAULT '0',
`heats` int(10) unsigned NOT NULL DEFAULT '0',
`status` smallint(6) unsigned NOT NULL DEFAULT '0',
`isgroup` tinyint(1) NOT NULL DEFAULT '0',
`favtimes` mediumint(8) NOT NULL DEFAULT '0',
`sharetimes` mediumint(8) NOT NULL DEFAULT '0',
`stamp` tinyint(3) NOT NULL DEFAULT '-1',
`icon` tinyint(3) NOT NULL DEFAULT '-1',
`pushedaid` mediumint(8) NOT NULL DEFAULT '0',
`cover` smallint(6) NOT NULL DEFAULT '0',
`replycredit` smallint(6) NOT NULL DEFAULT '0',
`relatebytag` char(255) NOT NULL DEFAULT '0',
`maxposition` int(8) unsigned NOT NULL DEFAULT '0',
`bgcolor` char(8) NOT NULL DEFAULT '',
`comments` int(10) unsigned NOT NULL DEFAULT '0',
`hidden` smallint(6) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`tid`),
KEY `digest` (`digest`),
KEY `sortid` (`sortid`),
KEY `displayorder` (`fid`,`displayorder`,`lastpost`),
KEY `typeid` (`fid`,`typeid`,`displayorder`,`lastpost`),
KEY `recommends` (`recommends`),
KEY `heats` (`heats`),
KEY `authorid` (`authorid`),
KEY `isgroup` (`isgroup`,`lastpost`),
KEY `special` (`special`),
KEY `fid` (`fid`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=gbk既然建立了typeid 这个索引. 为什么还需要 displayorder这个索引 目的是什么?
KEY `displayorder` (`fid`,`displayorder`,`lastpost`),
KEY `typeid` (`fid`,`typeid`,`displayorder`,`lastpost`),
KEY `typeid` (`fid`,`typeid`,`displayorder`,`lastpost`),
如果索引 typeid 这个联合索引中的 ‘typeid’是放在最后的
即KEY `typeid` (`fid`,`displayorder`,`lastpost`,`typeid`)
那么 索引 displayorder 就可以不需要了即如果索引 T (t1,t2,t3)可以把他理解为 (t1) (t1,t2) (t1,t2,t3) 这三种
where `fid`=1 and `typeid`=2
时会使用这个索引。