解决方案 »
- 关于mysql中删除指定时间的记录
- 多行合并为一行
- mysql可以实现,同一个数据库中,不同的表应用不存的存贮引擎吗?
- 关于存储过程返回日期型的问题。
- mysql5.1.7乱码问题!!!
- 关于MySQL的两个驱动的问题
- mysql里有没有bit类型啊?
- 请教本人想用java+mysql在linux上作点东西,各位给推荐推荐mysql的书,谢谢!!!
- mySQ单表2000W 记录,查询效率低,是否有优化办法?
- 怎么用mysql把mdf和ldf文件打开啊啊啊啊,好苦恼啊
- 出错了,mysql 在两台计算机上实现数据同步。(主从模式)
- MySQL中,UPDATE SET 中类似if-else的条件怎么办???????????????????????
show index from 帖子表;
explain select 帖子id, 帖子标题,帖子公开性, 创建日期,创建人,回复数 from 帖子表 where 是否已结帖=0或1 and 板块id=数字 and 创建时间>'2010-**-**' and 创建时间<'2010-**-**' order by 创建时间 desc limit startrow,30;
建立复合索引试试
explain 你的SQL语句
下面是表结构:
CREATE TABLE `oritexts` (
`OriTextID` int(11) NOT NULL auto_increment,
`questionTypeID` int(11) default NULL,
`title` varchar(500) NOT NULL,
`author` varchar(200) NOT NULL,
`magazine` varchar(100) NOT NULL,
`pubyear` varchar(200) NOT NULL,
`UserID` int(11) NOT NULL,
`LibID` int(11) default NULL,
`ExpertID` int(11) default NULL,
`IsOpen` tinyint(1) NOT NULL,
`Email` varchar(30) default NULL,
`IsResolved` tinyint(1) NOT NULL,
`BonusPoint` int(11) default NULL,
`CreateDate` datetime NOT NULL,
`Supplementary` longblob,
`isEnglish` tinyint(1) NOT NULL,
`UpdateDate` datetime NOT NULL,
`quserip` varchar(32) default NULL,
`Urlink` varchar(500) default NULL,
`inVolume` varchar(200) default NULL,
`timeIndex` decimal(25,0) NOT NULL,
`reason` varchar(200) default NULL,
`qikan` varchar(200) default NULL,
`beizhu` varchar(200) default NULL,
`page` varchar(200) default NULL,
`IsUsp` tinyint(1) NOT NULL,
`illStatus` tinyint(1) default NULL,
`applyIllUserID` int(11) default NULL,
`applyIllDate` datetime default NULL,
`processIllUserID` int(11) default NULL,
`processIllDate` datetime default NULL,
`illNo` varchar(50) default NULL,
`username` varchar(20) NOT NULL COMMENT 'user nick name from users table',
`answercount` int(11) default '0',
`ifupload` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`OriTextID`),
KEY `Expert_oritexts_FK1` (`ExpertID`),
KEY `Librarylist_oritexts_FK1` (`LibID`),
KEY `Users_oritexts_FK1` (`UserID`),
KEY `question_type_FK1` (`questionTypeID`),
KEY `oritexts_index` (`OriTextID`,`UpdateDate`),
CONSTRAINT `Expert_oritexts_FK1` FOREIGN KEY (`ExpertID`) REFERENCES `expert` (`UserID`),
CONSTRAINT `Librarylist_oritexts_FK1` FOREIGN KEY (`LibID`) REFERENCES `librarylist` (`LibID`),
CONSTRAINT `question_type_FK1` FOREIGN KEY (`questionTypeID`) REFERENCES `questiontype` (`QuestionTypeID`),
CONSTRAINT `Users_oritexts_FK1` FOREIGN KEY (`UserID`) REFERENCES `users` (`UserID`)
) ENGINE=InnoDB AUTO_INCREMENT=273722 DEFAULT CHARSET=utf8;show index from oritexts结果如下:
oritexts 0 PRIMARY 1 OriTextID A 289803 BTREE
oritexts 1 Expert_oritexts_FK1 1 ExpertID A 6 YES BTREE
oritexts 1 Librarylist_oritexts_FK1 1 LibID A 6 YES BTREE
oritexts 1 Users_oritexts_FK1 1 UserID A 20700 BTREE
oritexts 1 question_type_FK1 1 questionTypeID A 8 YES BTREE
oritexts 1 oritexts_index 1 OriTextID A 289803 BTREE
oritexts 1 oritexts_index 2 UpdateDate A 289803 BTREE explain select oritext.oriTextId as oritextid, oritext.title as title, oritext.isOpen as isopen, oritext.createDate as createdate,oritext.isEnglish as isEnglish, oritext.username as qusername, oritext.answercount as textanswerscount, oritext.updateDate as textanswerdate, oritext.magazine as magazine, oritext.pubyear as pubyear, oritext.urlink as urlink FROM Oritexts oritext WHERE 1=1 AND oritext.isResolved = 0 AND oritext.updateDate >= '2010-11-20 00:00:00' AND oritext.updateDate <= '2010-11-21 23:59:59' AND oritext.isOpen = 1 ORDER BY oritext.updateDate desc limit 20,30 结果如下:1 SIMPLE oritext ALL 289803 Using where; Using filesort辛苦各位了