We've recently enabled slow query logging on a server and it's proving interesting seeing which queries are bogging things down. This one is puzzling me a little:
SELECT articleid, type, authorid, authorname, text, posted FROM comments WHERE status = 'normal' ORDER BY posted DESC LIMIT 5; The purpose of this query is to list the five most recent (non-deleted) comments. Here is the table structure: CREATE TABLE `comments` ( `commentid` int(11) unsigned NOT NULL auto_increment, `type` enum('newsitem','article','column','artchapter') NOT NULL default 'article', `status` enum('normal','deleted') NOT NULL default 'normal', `authorid` mediumint(8) unsigned NOT NULL default '0', `authorname` varchar(20) default NULL, `articleid` mediumint(8) unsigned NOT NULL default '0', `text` text NOT NULL, `posted` int(11) unsigned NOT NULL default '0', `ip` varchar(60) NOT NULL default '', `hostname` varchar(80) default NULL, PRIMARY KEY (`commentid`), KEY `articleid` (`articleid`), KEY `posted` (`posted`), KEY `type` (`type`), KEY `status` (`status`) ) This table has approximately 100,000 rows. What's strange is that this query: SELECT articleid, type, authorid, authorname, text, posted FROM comments WHERE status != 'deleted' ORDER BY posted DESC LIMIT 5; which does exactly the same thing, flies, but the first one is very slow. An explain on the first (slow) query shows this: table type possible_keys key key_len ref rows Extra comments range status status 1 NULL 96295 where used; Using filesort An explain on the second (quick) query shows this: table type possible_keys key key_len ref rows Extra comments index NULL posted 4 NULL 96295 where used So in the slow query it's using status as the key and then filesorting, whereas in the second it's (correctly I think) using posted as the key and therefore narrowing the resultset first. Surely MySQL should always be optimising these in the same way? Can anyone shed any light on this (short of "just use the second query" ;)), so I know what to do (and not to do) in the future. -- Tim Fountain | Web developer | Incutio Limited | www.incutio.com email: [EMAIL PROTECTED] | Tel: +44 8708 700 333 | Fax: +44 7092 181 581 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]