Unnamed

Slow Query:

mysql> explain select * from BlogPost bp where bp.postStatusId=0 and bp.publishedDate > '2007-04-16' order by bp.publishedDate desc limit 0,50;
+----+-------------+-------+------+----------------------------+--------------+---------+-------+--------+-----------------------------+
| id | select_type | table | type | possible_keys              | key          | key_len | ref   | rows   | Extra                       |
+----+-------------+-------+------+----------------------------+--------------+---------+-------+--------+-----------------------------+
|  1 | SIMPLE      | bp    | ref  | postStatusId,publishedDate | postStatusId | 4       | const | 688398 | Using where; Using filesort | 
+----+-------------+-------+------+----------------------------+--------------+---------+-------+--------+-----------------------------+

Fast Query:

mysql> explain select * from BlogPost bp where bp.publishedDate > '2007-04-16' order by bp.publishedDate desc limit 0,50;
+----+-------------+-------+-------+---------------+---------------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+---------------+---------+------+---------+-------------+
|  1 | SIMPLE      | bp    | range | publishedDate | publishedDate | 9       | NULL | 2052000 | Using where | 
+----+-------------+-------+-------+---------------+---------------+---------+------+---------+-------------+
1 row in set (0.00 sec)




Table:

CREATE TABLE `BlogPost` (
  `blogPostId` bigint(20) NOT NULL auto_increment,
  `blogId` bigint(20) NOT NULL,
  `title` varchar(256) default NULL,
  `link` varchar(512) NOT NULL,
  `publishedDate` datetime default NULL,
  `technotags` varchar(512) default NULL,
  `metawords` varchar(512) default NULL,
  `author` varchar(256) default NULL,
  `wordcount` int(11) default NULL,
  `mediaProviderId` int(11) default '1',
  `content` text,
  `rawContent` text,
  `postStatusId` int(11) NOT NULL default '0',
  PRIMARY KEY  (`blogPostId`),
  UNIQUE KEY `UQ_BlogPost_1` (`link`),
  UNIQUE KEY `UQ_BlogPost_2` (`title`,`blogId`),
  KEY `blogId` (`blogId`),
  KEY `IX_BlogPost_1` (`link`),
  KEY `IX_BlogPost_2` (`blogPostId`),
  KEY `FKDC69FF8259EE4D86` (`blogId`),
  KEY `blogId_2` (`blogId`),
  KEY `FKDC69FF82E23E9DDE` (`mediaProviderId`),
  KEY `postStatusId` (`postStatusId`),
  KEY `publishedDate` (`publishedDate`),
  CONSTRAINT `FKDC69FF8259EE4D86` FOREIGN KEY (`blogId`) REFERENCES `Blog` (`blogId`),
  CONSTRAINT `FKDC69FF82E23E9DDE` FOREIGN KEY (`mediaProviderId`) REFERENCES `MediaProvider` (`mediaProviderId`),
  CONSTRAINT `FK_BlogPost_1` FOREIGN KEY (`blogId`) REFERENCES `Blog` (`blogId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1