解决方案 »
- 求大神出现指点一二 sql查询问题
- 想了一天了,这个SQL仍然不知道怎么写..有更高的高手来帮下!
- MySQL 5.0 有没有类似 SQL Server 2000 的事件查看器的工具呢?
- 一个数据多表查询的问题
- VB.net 如何备份Mysql数据库
- MySQL如何实现定时任务?
- 能不能这样备份和恢复MySQL数据库的一个表
- mysql里Connections达到29000多
- 请问在vc中如何实现与远端mysql数据库连接~~谢谢
- MySQL 双机备份,但是通过java创建表无法同步
- 出错了,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辛苦各位了