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]
