Query optimisation question - two queries, one slow, one fast

[Tim Fountain]

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: ti*@incutio.com | Tel: [phone] | Fax: [phone]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw

[J Davies]

Interesting... My guess is that the optimiser trys to optimise the "where"
first by using the"status" index, which it can do in the first (slow) query.
The not equal comparison in the second query forces it to to optimise the
sort using the "posted" index and simply select the first five rows not
equal to "deleted".

Have you tried the query with a modified status index which has posted as a
second key field.

Kind regards

Jerome Davies
"Tim Fountain" <ti*@incutio.com> wrote in message
news:be***********@FreeBSD.csie.NCTU.edu.tw...

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: ti*@incutio.com | Tel: [phone] | Fax: [phone]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw

[J Davies]

Interesting... My guess is that the optimiser trys to optimise the "where"
first by using the"status" index, which it can do in the first (slow) query.
The not equal comparison in the second query forces it to to optimise the
sort using the "posted" index and simply select the first five rows not
equal to "deleted".

Have you tried the query with a modified status index which has posted as a
second key field.

Kind regards

Jerome Davies
"Tim Fountain" <ti*@incutio.com> wrote in message
news:be***********@FreeBSD.csie.NCTU.edu.tw...

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: ti*@incutio.com | Tel: [phone] | Fax: [phone]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw