why mysql choose a bad query

[Changying Li]

 CREATE TABLE `abc` (
  `threadid` int(11) NOT NULL AUTO_INCREMENT COMMENT '主题id',
  `thread_type_id` int(11) DEFAULT NULL COMMENT '主题类别表id',
  `forumid` smallint(6) DEFAULT NULL COMMENT '主键id',
  `title` varchar(250) DEFAULT NULL COMMENT '标题',
  `lastpost` int(11) DEFAULT NULL COMMENT '最后回复时间',
  `open` smallint(6) DEFAULT NULL COMMENT '是否开放，0为不开放,1为开放,10为被移动的',
  `replycount` int(11) DEFAULT NULL COMMENT '回帖总数',
  `postusername` varchar(100) DEFAULT NULL COMMENT '发表人名称',
  `postuserid` int(11) DEFAULT NULL COMMENT '发表者id',
  `lastpostid` int(11) DEFAULT NULL,
  `lastposter` varchar(100) DEFAULT NULL COMMENT '最后回复人',
  `lastpostuserid` int(11) DEFAULT NULL,
  `dateline` int(11) DEFAULT NULL COMMENT '信息发表时间',
  `views` int(11) DEFAULT NULL COMMENT '查看次数',
  `visible` smallint(6) DEFAULT NULL COMMENT '是否有效,0为未审核,1为审核,2为软删除,3为草稿箱',
  `sticky` tinyint(1) DEFAULT NULL COMMENT '是否置顶',
  `goodnees` tinyint(1) DEFAULT NULL COMMENT '1为精华,0为非精华,默认为0',
  `votenum` smallint(6) DEFAULT NULL COMMENT '投票个数',
  `votetotal` int(11) DEFAULT NULL COMMENT '投票总分',
  `attach` smallint(6) DEFAULT NULL COMMENT '附件个数',
  `hiddencount` int(11) DEFAULT NULL COMMENT '未审核文章总数',
  `deletedcount` smallint(6) DEFAULT NULL COMMENT '被删除的回复总数',
  `pid` int(11) DEFAULT NULL COMMENT 
'当open不为10的时候它代表投票id,当open等于10时则为被移动的帖子的id',
  PRIMARY KEY (`threadid`),
  KEY `dateline` (`dateline`),
  KEY `forumid_2` (`forumid`,`thread_type_id`,`visible`,`sticky`,`dateline`),
  KEY `forumid` (`forumid`,`visible`,`sticky`,`dateline`)
) ENGINE=InnoDB AUTO_INCREMENT=660 DEFAULT CHARSET=utf8;


mysql> explain  SELECT *  FROM `abc` WHERE `forumid` = 25 AND `visible` = 1 AND 
`sticky` = 0 order by dateline \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: abc
         type: ref
possible_keys: forumid_2,forumid
          key: forumid_2
      key_len: 3
          ref: const
         rows: 24
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

why it choose forumid_2, not forumid ? 


-- 

Thanks & Regards

Changying Li


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=arch...@jab.org