Index not being used on ORDER BY

I am using MySQL 3.23.51

I have created the following table:
CREATE TABLE tbl (
f1 int(11) NOT NULL default '0',
f2 int(11) NOT NULL default '0',
f3 int(11) NOT NULL default '0',
PRIMARY KEY (f1),
UNIQUE KEY f3 (f3),
KEY f2 (f2)
) TYPE=MyISAM;

and I have inserted random values by repeatedly running:
INSERT IGNORE INTO tbl VALUES (rand()*1000, rand()*100, rand()*10000);

When I run: EXPLAIN SELECT f1 FROM tbl ORDER BY f1
the primary index is used (as it should)

When I run: EXPLAIN SELECT f2 FROM tbl ORDER BY f2
the unique index is used (as it should)

BUT...

When I run any of the following:
EXPLAIN SELECT * FROM tbl ORDER BY f1
EXPLAIN SELECT f1,f2 FROM tbl ORDER BY f1
EXPLAIN SELECT * FROM tbl ORDER BY f2
EXPLAIN SELECT * FROM tbl USE INDEX (f2) ORDER BY f2

The indexes are not used and a filesort is used instead. But the only
thing that changed is the displayed fields. This shouldn't have any
effect on how the keys are used.

Does anyone know if this bug is being worked on, or if it's already
fixed and I'm just using an old version, or if it's stil present in
MySQL 4, or what?

Thanks,
Daniel DeLorme


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Index not being used on ORDER BY

On Sun, 2003-03-23 at 09:34, Daniel DeLorme wrote:

Dear Daniel,

> I am using MySQL 3.23.51
>
> I have created the following table:
> CREATE TABLE tbl (
> f1 int(11) NOT NULL default '0',
> f2 int(11) NOT NULL default '0',
> f3 int(11) NOT NULL default '0',
> PRIMARY KEY (f1),
> UNIQUE KEY f3 (f3),
> KEY f2 (f2)
> ) TYPE=MyISAM;
>
> and I have inserted random values by repeatedly running:
> INSERT IGNORE INTO tbl VALUES (rand()*1000, rand()*100, rand()*10000);

Please do not send such test cases for bugs. Issues may be data
dependent and random values you get can be quite different from what
we get trying to repeat your test case. Instead use particular data you
have seen the problem.

It is not important for this case but it might be important for others.



>
> When I run: EXPLAIN SELECT f1 FROM tbl ORDER BY f1
> the primary index is used (as it should)

mysql> EXPLAIN SELECT f1 FROM tbl ORDER BY f1;
+-------+-------+---------------+---------+---------+------+ ------+-------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+-------+---------------+---------+---------+------+ ------+-------------+
| tbl | index | NULL | PRIMARY | 4 | NULL | 17 |
Using index |
+-------+-------+---------------+---------+---------+------+ ------+-------------+
1 row in set (0.27 sec)


In this case MySQL resolves query by scanning table in sorted way using
index only.

>
> When I run: EXPLAIN SELECT f2 FROM tbl ORDER BY f2
> the unique index is used (as it should)

The same.

>
> BUT...
>
> When I run any of the following:
> EXPLAIN SELECT * FROM tbl ORDER BY f1
> EXPLAIN SELECT f1,f2 FROM tbl ORDER BY f1
> EXPLAIN SELECT * FROM tbl ORDER BY f2
> EXPLAIN SELECT * FROM tbl USE INDEX (f2) ORDER BY f2
>
> The indexes are not used and a filesort is used instead. But the only
> thing that changed is the displayed fields. This shouldn't have any
> effect on how the keys are used.

Actually it does. In the first case MySQL can use index only (without
querying rows themselves) to resolve the query and it is good plan.

On other hand if you specify fields which are not covered by index MySQL
has to consult data file for each row which generates a lot of IO for
large tables and so filesort is faster.

Note filesort is not really sort using temporary files for small tables
- for small enough sorts it is very fast in memory operation.

In case not all table is scanned, ie LIMIT is used index scan will be
preferred:

mysql> explain SELECT * FROM tbl ORDER BY f2 limit 5;
+-------+-------+---------------+------+---------+------+--- ---+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+------+---------+------+--- ---+-------+
| tbl | index | NULL | f2 | 4 | NULL | 17 | |
+-------+-------+---------------+------+---------+------+--- ---+-------+
1 row in set (0.00 sec)



>
> Does anyone know if this bug is being worked on, or if it's already
> fixed and I'm just using an old version, or if it's stil present in
> MySQL 4, or what?

This behavior is same in MySQL 4.0.

May I ask you if you have some particular query performance issue ?

--
MySQL 2003 Users Conf. -> http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Index not being used on ORDER BY

Peter Zaitsev wrote:
> On Sun, 2003-03-23 at 09:34, Daniel DeLorme wrote:
>>The indexes are not used and a filesort is used instead. But the only
>>thing that changed is the displayed fields. This shouldn't have any
>>effect on how the keys are used.
>
>
> Actually it does. In the first case MySQL can use index only (without
> querying rows themselves) to resolve the query and it is good plan.
>
> On other hand if you specify fields which are not covered by index MySQL
> has to consult data file for each row which generates a lot of IO for
> large tables and so filesort is faster.
>
> Note filesort is not really sort using temporary files for small tables
> - for small enough sorts it is very fast in memory operation.

Oh... well if the filesort is actually done in memory this changes
everything.

> May I ask you if you have some particular query performance issue ?

Well, I was having a performance problem and my first instinct was to run
EXPLAIN on the main query. Since it returned 'filesort' I figured that was
the performance problem, but after learning the filesort was most likely
being done in memory, I looked some more and found the real source of the
performance problem (which was not a MySQL bug).

Sorry for the erroneous bug report. To prevent this kind of misunderstanding
in the future, I suggest you should replace 'filesort' by 'memory sort' when
the sort is actually being done in memory. It would also help to better
understand the behavior of the MySQL optimizer when debugging.

MySQL-ly Yours,
Daniel DeLorme


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Index not being used on ORDER BY

On Mon, 2003-03-24 at 00:41, Daniel DeLorme wrote:

> > May I ask you if you have some particular query performance issue ?
>
> Well, I was having a performance problem and my first instinct was to run
> EXPLAIN on the main query. Since it returned 'filesort' I figured that was
> the performance problem, but after learning the filesort was most likely
> being done in memory, I looked some more and found the real source of the
> performance problem (which was not a MySQL bug).
>
> Sorry for the erroneous bug report. To prevent this kind of misunderstanding
> in the future, I suggest you should replace 'filesort' by 'memory sort' when
> the sort is actually being done in memory. It would also help to better
> understand the behavior of the MySQL optimizer when debugging.
>

Daniel,

Thank you for your feedback.
We shall think about renaming or even better writing if sort is likely
to be done in memory or not. But this will not happen in MySQL 4.0
which is stable now and so we can't add changes which can break
applications - some applications may analyse EXPLAIN output
automatically.


--
MySQL 2003 Users Conf. -> http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org