By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,356 Members | 2,752 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,356 IT Pros & Developers. It's quick & easy.

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

P: n/a

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: +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/my***********...ie.nctu.edu.tw

Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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: +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/my***********...ie.nctu.edu.tw

Jul 19 '05 #2

P: n/a
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: +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/my***********...ie.nctu.edu.tw

Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.