optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY key

Hello,

My problem was reported as fixed in MySQL version 3.23.38,
but its not fixed in the current versions.
I have already reported this on the general list, last month.
But because I got no final answer I resend it to bugs@mysql.


Problem:
A query of this type,
SELECT * FROM tbl_name,tbl_name2 ... ORDER BY key_part1 LIMIT #
doesn't use the index but uses filesort instead.


Tested Versions:
MySQL 3.23.49, MySQL 4.0.2


How-To-Repeat:
Example:

My query does a selfjoin with no WHERE part and could
use a key for the ORDER LIMIT but does filesort instead.

EXPLAIN SELECT dog.dog_id, dog.dog_name, sire.dog_name as dog_sirename
FROM dogs dog LEFT JOIN dogs as sire ON (dog.dog_sireid = sire.dog_id)
ORDER BY dog.dog_timestamp_cr LIMIT 100;
+-----+------+---------+-------+-------+--------------+----- -+--------------
+
|table|type |possiblek|key |key_len|ref |rows |Extra
|
+-----+------+---------+-------+-------+--------------+----- -+--------------
+
|dog |ALL | NULL |NULL | NULL|NULL |416827|Using
filesort|
|sire |eq_ref| PRIMARY |PRIMARY| 3|dog.dog_sireid| 1|
|
+-----+------+---------+-------+-------+--------------+----- -+--------------
+


For comparison here the query without the join.
Without the join the optimizer uses the key.

EXPLAIN SELECT dog.dog_id, dog.dog_name FROM dogs dog
ORDER BY dog.dog_timestamp_cr LIMIT 100;
+-----+-----+---------+-----------------+-------+----+------ +-----+
|table|type |possiblek|key |key_len|ref |rows |Extra|
+-----+-----+---------+-----------------+-------+----+------ +-----+
|dog |index| NULL |idog_timestamp_cr| 4|NULL|416827| |
+-----+-----+---------+-----------------+-------+----+------ +-----+


Here the output of show index:

show index from dogs;
+-----+-----------------+----+--- ------------+---+------+---+---+---+-----+
|Non_u|Key_name |Seq_i|Column_name |Col|Cardin|Sub|Pac|Nul|Itype|
+-----+-----------------+----+--- ------------+---+------+---+---+---+-----+
| 0|PRIMARY | 1|dog_id | A |416827|NUL|NUL| |BTREE|
| 1|idog_sireid | 1|dog_sireid | A | 18946|NUL|NUL| |BTREE|
| 1|idog_damid | 1|dog_damid | A | 83365|NUL|NUL| |BTREE|
| 1|idog_alias | 1|dog_alias | A |416827|NUL|NUL| |BTREE|
| 1|idog_timestamp_cr| 1|dog_timestamp_cr| A | 59546|NUL|NUL| |BTREE|
+-----+-----------------+----+--- ------------+---+------+---+---+---+-----+
(This is the output for table dogs. I shortened the output
a little bit, to improve readabilaty of the email. Hope it helps)



Please answer my email and clarify whether
this is _again_ a general problem of mySQL
or just a misconfiguation of my setup.


best regards

Gunnar von Boehn

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12297@lists.mysql.com
To unsubscribe, e-mail

Re: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY key LIMIT # uses filesort!

Dear Boehl.

We would deffinetely want to fix this problem but we need ful repeatable=20
case for it as it might be data dependend.

Please provide full set of SQL statememnts which we could use

mysql test < file.sql

to see the wrong behaviour.

In case you can't repeat the problem with small data size (which will=20
result in file.sql to be no more than 100Kb) please compress and=20
upload tables required for test to ftp://support.mysql.com/pub/mysql/sec=
ret


I've just checked the simple query and it seems to work:

CREATE TABLE `a` (
`s` tinyint(3) unsigned NOT NULL default '0',
`l` int(11) default NULL,
KEY `s` (`s`,`l`)
) TYPE=3DMyISAM

mysql> explain select * from a order by s limit 2;
+-------+-------+---------------+------+---------+------+--- ---+---------=
----+
| table | type | possible_keys | key | key_len | ref | rows | Extra =
|
+-------+-------+---------------+------+---------+------+--- ---+---------=
----+
| a | index | NULL | s | 6 | NULL | 20 | Using in=
dex |
+-------+-------+---------------+------+---------+------+--- ---+---------=
----+
1 row in set (0.00 sec)



--=20
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com M: +7 095 725 4955


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12299@lists.mysql.com
To unsubscribe, e-mail

Re: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY key

von Boehn, Gunnar writes:
>
> Hello,
>
> My problem was reported as fixed in MySQL version 3.23.38,
> but its not fixed in the current versions.
> I have already reported this on the general list, last month.
> But because I got no final answer I resend it to bugs@mysql.
>
>
> Problem:
> A query of this type,
> SELECT * FROM tbl_name,tbl_name2 ... ORDER BY key_part1 LIMIT #
> doesn't use the index but uses filesort instead.
>
>
> Tested Versions:
> MySQL 3.23.49, MySQL 4.0.2
>
>
> How-To-Repeat:
> Example:
>
> My query does a selfjoin with no WHERE part and could
> use a key for the ORDER LIMIT but does filesort instead.
>
> EXPLAIN SELECT dog.dog_id, dog.dog_name, sire.dog_name as dog_sirename
> FROM dogs dog LEFT JOIN dogs as sire ON (dog.dog_sireid = sire.dog_id)
> ORDER BY dog.dog_timestamp_cr LIMIT 100;
>
> Please answer my email and clarify whether
> this is _again_ a general problem of mySQL
> or just a misconfiguation of my setup.
>
>
> best regards
>
> Gunnar von Boehn

Hi!

Problems is that dog is LEFT JOIN'ed so it has to be read entirely.

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12304@lists.mysql.com
To unsubscribe, e-mail

RE: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY

Hi,


>gunnar wrote:
>> How-To-Repeat:
>> My query does a selfjoin with no WHERE part and could
>> use a key for the ORDER LIMIT but does filesort instead.
>>
>> EXPLAIN SELECT dog.dog_id, dog.dog_name, sire.dog_name as
>> dog_sirename FROM dogs dog LEFT JOIN dogs as sire ON
>> (dog.dog_sireid = sire.dog_id)
>> ORDER BY dog.dog_timestamp_cr LIMIT 100;

sinisa wrote:
>
> Problems is that dog is LEFT JOIN'ed so it has to be read entirely.


Sorry, but I can't follow you.
Could you please explain this to me?

Why is there a difference between these two querys?
Why does mysql read the full table for a query like this:
SELECT dog.dog_id FROM dogs dog
LEFT JOIN dogs as sire ON (dog.dog_sireid = sire.dog_id)
ORDER BY dog.dog_timestamp_cr LIMIT 1;

But only need to read one row for this query:
SELECT dog.dog_id FROM dogs dog
LEFT JOIN dogs as sire ON (dog.dog_sireid = sire.dog_id)
WHERE dog.dog_id=1;



thank you for your help
best regards

Gunnar


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12305@lists.mysql.com
To unsubscribe, e-mail

RE: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY

von Boehn, Gunnar writes:
> Hi,
>
>
> Why is there a differnece between these two querys?
> Why does mysql read the full table for a query like this:
> SELECT dog.dog_id FROM dogs dog
> LEFT JOIN dogs as sire ON (dog.dog_sireid = sire.dog_id) ORDER BY
> dog.dog_timestamp_cr LIMIT 1;
>
> But only need to read one row for this query:
> SELECT dog.dog_id FROM dogs dog
> LEFT JOIN dogs as sire ON (dog.dog_sireid = sire.dog_id) WHERE
> dog.dog_id=1;
>
>
>
> thank you for your help
> best regards
>
> Gunnar
>

Second query adds one important filter which can optimise a query to
use an index on dog.

Also, MySQL can use only one index per table.

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12306@lists.mysql.com
To unsubscribe, e-mail

RE: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY

Hi,

Sorry but I still didn't got it.


> How-To-Repeat:
> von Boehn, Gunnar writes:
> >
> > Why is there a differnece between these two querys?
> > Why does mysql read the full table for a query like this:
> > SELECT dog.dog_id FROM dogs dog
> > LEFT JOIN dogs as sire ON (dog.dog_sireid = sire.dog_id) ORDER BY
> > dog.dog_timestamp_cr LIMIT 1;
> >
> > But only need to read one row for this query:
> > SELECT dog.dog_id FROM dogs dog
> > LEFT JOIN dogs as sire ON (dog.dog_sireid = sire.dog_id) WHERE
> > dog.dog_id=1;
> >
> >

sinisa wrote:
> Second query adds one important filter which can optimise a query to
> use an index on dog.


Shouldn't the optimizer use the index on the first query as well?
The optimizer could use the index on dog.dog_timestamp_cr because
of the ORDER BY LIMIT #. I think its a bug if the optimizer doesn't.


Please have a look at the change log for mySQL version 4.3.38.
http://www.mysql.com/doc/en/News-3.23.38.html

D.2.15 Changes in release 3.23.38 (09 May 2001)
- Changed optimiser so that queries like
SELECT * FROM tbl_name,tbl_name2 ... ORDER BY key_part1 LIMIT #
will use index on key_part1 instead of filesort.


Isn't that exactly the case we are talking about?



> Also, MySQL can use only one index per table.

But MySQL isn't even using one index for the first table,
MySQL was doing filesort instead.




thanks for your help

Gunnar

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12307@lists.mysql.com
To unsubscribe, e-mail

RE: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY

von Boehn, Gunnar writes:
> Hi,
>
>
>
> Shouldn't the optimizer use the index on the first query as well?
> The optimizer could use the index on dog.dog_timestamp_cr because
> of the ORDER BY LIMIT #. I think its a bug if the optimizer doesn't.
>
>

In t5e first case, MySQL has to scan entire table for a left
join. This is because you are using left inner join.

Plus MySQL can use only one index per table and you have only one
table here.

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12308@lists.mysql.com
To unsubscribe, e-mail

RE: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY

Ok, one more try :-)


> -----Original Message-----
> From: Sinisa Milivojevic [mailto:sinisa@mysql.com]
>
> In t5e first case, MySQL has to scan entire table for a left
> join. This is because you are using left inner join.
>
> Plus MySQL can use only one index per table and you have only one
> table here.

How-To-Repeat:
As a reminder here the first case again)
"SELECT dog.dog_id FROM dogs dog
LEFT JOIN dogs as sire ON (dog.dog_sireid = sire.dog_id) ORDER BY
dog.dog_timestamp_cr LIMIT 1;"
=>does filesort and instead using index


Sinisa:
> In t5e first case, MySQL has to scan entire table for a left
> join. This is because you are using left inner join.

- Using LEFT JOIN is no reason for doing a table scan.
This query uses LEFT JOIN and doesn't need a table scan:
"SELECT dog.dog_id FROM dogs dog
LEFT JOIN dogs as sire ON (dog.dog_sireid = sire.dog_id) WHERE
dog.dog_id=1;"


Sinisa:
> Plus MySQL can use only one index per table
> and you have only one table here.

I call one table two times, that gives me two tables for this query.
Using two different tables or two times the same table makes no difference
to SQL.
Which means that MySQL could use two keys in this case.
Please have a look at this query, it uses exactly the same tables and uses
keys:
"SELECT dog.dog_id FROM dogs dog
LEFT JOIN dogs as sire ON (dog.dog_sireid = sire.dog_id) WHERE
dog.dog_id=1;"



Please have a look at the change log for mySQL version 3.23.38
http://www.mysql.com/doc/en/News-3.23.38.html
<--->
D.2.15 Changes in release 3.23.38 (09 May 2001)
- Changed optimiser so that queries like
SELECT * FROM tbl_name,tbl_name2 ... ORDER BY key_part1 LIMIT #
will use index on key_part1 instead of filesort.
<--->


Isn't this exactly the case we are talking about?



best regards
Gunnar

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12309@lists.mysql.com
To unsubscribe, e-mail

RE: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY

von Boehn, Gunnar writes:
> Ok, one more try :-)
>
> Sinisa:
> > Plus MySQL can use only one index per table
> > and you have only one table here.
>
> I call one table two times, that gives me two tables for this query.
> Using two different tables or two times the same table makes no difference
> to SQL.
>
>
> best regards
> Gunnar
>

Let me correct myself:

One index per one physical table.

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12311@lists.mysql.com
To unsubscribe, e-mail

RE: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY

von Boehn, Gunnar writes:
> Hi,
>
>
> Shouldn't the optimizer use the index on the first query as well?
> The optimizer could use the index on dog.dog_timestamp_cr because
> of the ORDER BY LIMIT #. I think its a bug if the optimizer doesn't.
>
>
> Please have a look at the change log for mySQL version 4.3.38.
> http://www.mysql.com/doc/en/News-3.23.38.html
>
> D.2.15 Changes in release 3.23.38 (09 May 2001)
> - Changed optimiser so that queries like
> SELECT * FROM tbl_name,tbl_name2 ... ORDER BY key_part1 LIMIT #
> will use index on key_part1 instead of filesort.
>
>
> Isn't that exactly the case we are talking about?
>
>
>
> > Also, MySQL can use only one index per table.
>
> But MySQL isn't even using one index for the first table,
> MySQL was doing filesort instead.
>
>
>
>
> thanks for your help
>
> Gunnar
>

First of all, yes MySQL can use indices on both logical tables.

Change a qeury to ordinary join and see if index is used for sorting.

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12314@lists.mysql.com
To unsubscribe, e-mail

RE: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY

> From: Sinisa Milivojevic [mailto:sinisa@mysql.com]
>
> First of all, yes MySQL can use indices on both logical tables.
>
> Change a qeury to ordinary join and see if index is used for sorting.

That didn't change anything.
The type of the join has no influence to the problem.

If I do a JOIN then the possible key for the ORDER BY LIMIT is _NOT_ used.
But a query without a JOIN uses the key.


How-To-Repeat:


JOIN => not using index

EXPLAIN SELECT dog.dog_id, dog.dog_name FROM dogs dog, dogs as sire
WHERE (dog.dog_sireid = sire.dog_id) ORDER BY dog.dog_timestamp DESC
LIMIT 2;
+-------+--------+----------------+---------+---------+----- -----------+----
----+----------------+
| table | type | possible_keys | key | key_len | ref |
rows | Extra |
+-------+--------+----------------+---------+---------+----- -----------+----
----+----------------+
| dog | ALL | ind_dog_sireid | NULL | NULL | NULL |
416827 | Using filesort |
| sire | eq_ref | PRIMARY | PRIMARY | 3 | dog.dog_sireid |
1 | Using index |
+-------+--------+----------------+---------+---------+----- -----------+----
----+----------------+


NO JOIN => use of index

EXPLAIN SELECT dog.dog_id, dog.dog_name FROM dogs dog ORDER BY
dog.dog_timestamp DESC LIMIT 2;
+-------+-------+---------------+---------------+---------+- -----+--------+-
------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+-------+---------------+---------------+---------+- -----+--------+-
------+
| dog | index | NULL | dog_timestamp | 4 | NULL | 416827 |
|
+-------+-------+---------------+---------------+---------+- -----+--------+-
------+




2nd example to show that this problem is the same when we use another index.

JOIN => not using index

EXPLAIN SELECT dog.dog_id, dog.dog_name FROM dogs dog, dogs as sire
WHERE (dog.dog_sireid = sire.dog_id) ORDER BY dog.dog_id DESC LIMIT 2;
+-------+--------+----------------+---------+---------+----- -----------+----
----+----------------+
| table | type | possible_keys | key | key_len | ref |
rows | Extra |
+-------+--------+----------------+---------+---------+----- -----------+----
----+----------------+
| dog | ALL | ind_dog_sireid | NULL | NULL | NULL |
416827 | Using filesort |
| sire | eq_ref | PRIMARY | PRIMARY | 3 | dog.dog_sireid |
1 | Using index |
+-------+--------+----------------+---------+---------+----- -----------+----
----+----------------+
In this example the 'optimizer' should use both times the Primary Key,
instead of using filesort.



NO JOIN => using index

EXPLAIN SELECT dog.dog_id, dog.dog_name FROM dogs dog ORDER BY dog.dog_id
DESC LIMIT 2;
+-------+-------+---------------+---------+---------+------+ --------+-------
+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+-------+---------------+---------+---------+------+ --------+-------
+
| dog | index | NULL | PRIMARY | 3 | NULL | 416827 |
|
+-------+-------+---------------+---------+---------+------+ --------+-------
+




I hope this info helps you.

looking forward to your answer.
best regards

Gunnar

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12316@lists.mysql.com
To unsubscribe, e-mail

RE: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY

von Boehn, Gunnar writes:
>
>
> > From: Sinisa Milivojevic [mailto:sinisa@mysql.com]
> >
> > First of all, yes MySQL can use indices on both logical tables.
> >
> > Change a qeury to ordinary join and see if index is used for sorting.
>

[skip]

>
> I hope this info helps you.
>
> looking forward to your answer.
> best regards
>
> Gunnar
>

I already have a test case and I will take a deeper look at it as soon
as I find some time ...

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12325@lists.mysql.com
To unsubscribe, e-mail

RE: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY

How-To-Repeat:
-----Original Message-----
From: von Boehn, Gunnar
Sent: Donnerstag, 29. August 2002 15:54
To: 'sinisa@mysql.com'
Subject: RE: optimizer bug: SELECT * FROM tbl1 , tbl2 ... ORDER BY key
-[ID:961 RF:1]


Hello,


it looks like Version 4.0.3 doesn't fix
the "optimizer bug: SELECT * FROM tbl1,tbl2 ORDER BY key LIMIT #".

So please reopen the ticket.



mysql> show variables;
[...]
| version | 4.0.3-beta



Here is an example selfjoin to show the bug.
It should use two times the primary key for the field dog_id.
The bug is, that it uses one times a filesort instead.


mysql> EXPLAIN SELECT * FROM dogsO as table1, dogsO as table2 WHERE
(table1.dog_id = table2.dog_id) ORDER BY table1.dog_id LIMIT 2;
+--------+--------+---------------+---------+---------+----- ----------+-----
---+----------------+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
+--------+--------+---------------+---------+---------+----- ----------+-----
---+----------------+
| table1 | ALL | PRIMARY | NULL | NULL | NULL |
416827 | Using filesort |
| table2 | eq_ref | PRIMARY | PRIMARY | 3 | table1.dog_id |
1 | |
+--------+--------+---------------+---------+---------+----- ----------+-----
---+----------------+
2 rows in set (0.00 sec)


I assume that I don't have to send a new,additional testcase
as it's still the 'same' bug as reported before.
Wasn't this bug first reported before Version 3.23.38?
How can it be in again?


regards

Gunnar von Boehn
Sony International Europe(GmbH)




> -----Original Message-----
> From: supportwizard@mysql.com [mailto:supportwizard@mysql.com]
> Sent: Donnerstag, 29. August 2002 15:40
> To: von Boehn, Gunnar
> Subject: optimizer bug: SELECT * FROM tbl1 , tbl2 ... ORDER BY key
> -[ID:961 RF:1]
>
>
> Dear Gunnar von Boehn,
>
> Ticket is to be closed.
>
> *** READ THIS !!! READ THIS !!! READ THIS !!! READ THIS !!! ****
>
> MAKE SURE YOU ANSWER INSIDE THE DESCRIPTION FIELD BELOW
> (under line 'Description:') AND DO NOT REMOVE ANY LINES
> ABOVE THE DESCRIPTION FIELD OR YOUR MAIL WILL BE IGNORED
>
> ************************************************************ ****
>
> State : ->
> Assigned to : sinisa
> Description :
> >
> >
> > Hi!
> >
> > This problem is fixed in 4.0.3 which should be out in
> several days.
> >
> > --
> > Regards,
> > __ ___ ___ ____ __
> > / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
>
> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
> > /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
> > <___/ www.mysql.com
>
>
> I did not receive anything from the Support Wizard, so please send
> this time (and only this time) your questions directly to me.
>
> If a problem persists, please upload your test case to:
>
> ftp://support.mysql.com:/pub/mysql/secret
>
> --
> Regards,
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
>
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
> /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
> <___/ www.mysql.com
>

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12428@lists.mysql.com
To unsubscribe, e-mail

RE: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY

How-To-Repeat:
-----Original Message-----
From: Sinisa Milivojevic [mailto:sinisa@mysql.com]
Sent: Donnerstag, 29. August 2002 16:04
To: von Boehn, Gunnar
Subject: RE: optimizer bug: SELECT * FROM tbl1 , tbl2 ... ORDER BY key
-[ID:961 RF:1]


von Boehn, Gunnar writes:
> Hello,
>
>
> it looks like Version 4.0.3 doesn't fix
> the "optimizer bug: SELECT * FROM tbl1,tbl2 ORDER BY key LIMIT #".
>
> So please reopen the ticket.
>
>
>
> mysql> show variables;
> [...]
> | version | 4.0.3-beta
>
>
>
> Here is an example selfjoin to show the bug.
> It should use two times the primary key for the field dog_id.
> The bug is, that it uses one times a filesort instead.
>
>
> mysql> EXPLAIN SELECT * FROM dogsO as table1, dogsO as table2 WHERE
> (table1.dog_id = table2.dog_id) ORDER BY table1.dog_id LIMIT 2;
>
+--------+--------+---------------+---------+---------+----- ----------+-----
> ---+----------------+
> | table | type | possible_keys | key | key_len | ref |
rows
> | Extra |
>
+--------+--------+---------------+---------+---------+----- ----------+-----
> ---+----------------+
> | table1 | ALL | PRIMARY | NULL | NULL | NULL |
> 416827 | Using filesort |
> | table2 | eq_ref | PRIMARY | PRIMARY | 3 | table1.dog_id |
> 1 | |
>
+--------+--------+---------------+---------+---------+----- ----------+-----
> ---+----------------+
> 2 rows in set (0.00 sec)
>
>
> I assume that I don't have to send a new,additional testcase
> as it's still the 'same' bug as reported before.
> Wasn't this bug first reported before Version 3.23.38?
> How can it be in again?
>
>
> regards
>
> Gunnar von Boehn
> Sony International Europe(GmbH)
>

I will investigate it carefully once I get my hands on your tables.

I will re-open a ticket.

Under which filename did you upload your tables ??


--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12429@lists.mysql.com
To unsubscribe, e-mail

RE: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY

How-To-Repeat:
-----Original Message-----
From: von Boehn, Gunnar
Sent: Donnerstag, 29. August 2002 17:10
To: 'sinisa@mysql.com'
Subject: RE: optimizer bug: SELECT * FROM tbl1 , tbl2 ... ORDER BY key


Hi,


> I will investigate it carefully once I get my hands on your tables.


You don't need my tables to test this.
It's a general problem that shows up with any and every table.


CREATE TABLE `test`(
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

[.. fill table with some hundret records..]

mysql> EXPLAIN SELECT * FROM test as table1, test as table2 WHERE
(table1.id = table2.id) ORDER BY table1.id LIMIT 2;

It will use filesort instead of using the key.



Please try it with any table you have
and tell me if you have got the same problem!



regards
Gunnar

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12430@lists.mysql.com
To unsubscribe, e-mail

RE: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY

How-To-Repeat:

Hi,


Sinisa wrote:
> I have only tables with up to 10 rows where indices are not used at
> all, so I will check your case , but when I find time to generate the
> tables.


I wonder who has checked and reproduced this problem the first time?
You told me that somebody has 'fixed' this problem in 4.0.3, who was it?


regards
Gunnar

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12432@lists.mysql.com
To unsubscribe, e-mail

RE: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY

von Boehn, Gunnar writes:
> Hi,
>
>
> You told me that somebody has 'fixed' this problem in 4.0.3, who was it?
>
>
> regards
> Gunnar
>

Monty did, but it was a case of SELECT DISTINCT .... ORDER BY ..

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12433@lists.mysql.com
To unsubscribe, e-mail

RE: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY

Hi!

>>>>> "von" == von Boehn writes:



>> I will investigate it carefully once I get my hands on your tables.

von> You don't need my tables to test this.
von> It's a general problem that shows up with any and every table.

Sorry but this is not true:

I did a test for this:

mysql> explain select * from t1, t2 where t1.a=t2.a order by t2.a limit 2;
+-------+--------+---------------+---------+---------+------ +-------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+--------+---------------+---------+---------+------ +-------+-------------+
| t2 | index | PRIMARY | PRIMARY | 4 | NULL | 42676 | Using index |
| t1 | eq_ref | PRIMARY | PRIMARY | 4 | t2.a | 1 | Using index |
+-------+--------+---------------+---------+---------+------ +-------+-------------+
2 rows in set (4.69 sec)

t1 and t2 are both tables with +40000 rows and this query doesn't have
this problem.



von> Please try it with any table you have
von> and tell me if you have got the same problem!

Sorry, but I can't repeat the problem with 4.0.3.

You have to understand that to resolve a query the optimizer takes a
lot of parameters into account, including key distribution, key length
etc. Because of this we it's normally very difficulty to repeat ta
problem without having a repeatable test case.

Just because of this problem we require that every email to
bugs@lists.mysql.com should have repeatable test case, either in the
email or uploaded to ftp://support.mysql.com.

By providing this to us we can on our hand usually provide a quick bug
fix for the problem in question.

Regards,
Monty
CTO of MySQL AB.

PS: It may be that MySQL has a bug in cases like this, but it's not as
trivial to repeat this as you think it was.

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12461@lists.mysql.com
To unsubscribe, e-mail

RE: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY

Hi!

A bit more information about this problem:

von> it looks like Version 4.0.3 doesn't fix
von> the "optimizer bug: SELECT * FROM tbl1,tbl2 ORDER BY key LIMIT #".

4.0.3 does solve this for some queries, but not for all.

I have now tuned things a bit for 4.0.4 to be able to recognize a
couple of more cases when this optimization can be done.

There is a couple of things that could be done even better
(with GROUP BY ... limit), but this has to wait for 4.1 as I don't
want to do any drastic changes to the 4.0 code as we hope to be able
to declare it stable in the near future.

Regards,
Monty

--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12462@lists.mysql.com
To unsubscribe, e-mail

RE: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY

Hi,


>> It's a general problem that shows up with any and every table.
>
> Sorry but this is not true:
> I did a test for this:
>
> mysql> explain select * from t1, t2 where t1.a=t2.a order by
> t2.a limit 2;
> +-------+--------+---------------+---------+---------+------ +-
> ------+-------------+
> | table | type | possible_keys | key | key_len | ref |
> rows | Extra |
> +-------+--------+---------------+---------+---------+------ +-
> ------+-------------+
> | t2 | index | PRIMARY | PRIMARY | 4 | NULL |
> 42676 | Using index |
> | t1 | eq_ref | PRIMARY | PRIMARY | 4 | t2.a |
> 1 | Using index |
> +-------+--------+---------------+---------+---------+------ +-
> ------+-------------+
> 2 rows in set (4.69 sec)
>
> t1 and t2 are both tables with +40000 rows and this query doesn't have
> this problem.
>
>
> von> Please try it with any table you have
> von> and tell me if you have got the same problem!
>
> Sorry, but I can't repeat the problem with 4.0.3.


Ok, here is a very simple testcase which should show the problem again.

how to repeat:
#
# Server version: 4.00.03
#
# Table structure for table `test`
CREATE TABLE test (
id int(11) NOT NULL auto_increment,
father int(11) NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;

# Dumping data for table `test`

INSERT INTO test VALUES (1, 0), (2, 1), (3, 2), (4, 3), (5, 4), (6, 5), (7,
6), (8, 7), (9, 8), (10, 9), (11, 10), (12, 11);

mysql> EXPLAIN SELECT * FROM test t1, test t2 WHERE t1.father = t2.id
ORDER BY t1.id LIMIT 2;
+-------+--------+---------------+---------+---------+------ -----+------+---
-------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+--------+---------------+---------+---------+------ -----+------+---
-------------+
| t1 | ALL | NULL | NULL | NULL | NULL | 12 |
Using filesort |
| t2 | eq_ref | PRIMARY | PRIMARY | 4 | t1.father | 1 |
|
+-------+--------+---------------+---------+---------+------ -----+------+---
-------------+




> You have to understand that to resolve a query the optimizer takes a
> lot of parameters into account, including key distribution, key length
> etc. Because of this we it's normally very difficulty to repeat ta
> problem without having a repeatable test case.
>
> Just because of this problem we require that every email to
> bugs@lists.mysql.com should have repeatable test case, either in the
> email or uploaded to ftp://support.mysql.com.


I understand.

The point simply was that a I did send a testcase a month ago,
and that exactly this testcase didn't worked with 4.0.3 again.

I tested 4.0.3 with half a douzend of other tables as well
which all failed to use the index.
That's why I thought that this a generally problem.

Sorry for the confusion.


best regards

Gunnar

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12465@lists.mysql.com
To unsubscribe, e-mail

Re: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY key LIMIT # uses filesort!

On Monday 02 September 2002 15:24, Michael Widenius wrote:
> Hi!
>
> >>>>> "von" == von Boehn writes:
>
>
>
> >> I will investigate it carefully once I get my hands on your tables.
>
> von> You don't need my tables to test this.
> von> It's a general problem that shows up with any and every table.
>
> Sorry but this is not true:
>
> I did a test for this:
>
> mysql> explain select * from t1, t2 where t1.a=3Dt2.a order by t2.a lim=
it 2;
> +-------+--------+---------------+---------+---------+------ +-------+--=
----
>-------+
>
> | table | type | possible_keys | key | key_len | ref | rows | E=
xtra
> | |
>
> +-------+--------+---------------+---------+---------+------ +-------+--=
----
>-------+
>
> | t2 | index | PRIMARY | PRIMARY | 4 | NULL | 42676 | U=
sing
> | index | t1 | eq_ref | PRIMARY | PRIMARY | 4 | t2.a | =
1
> | | Using index |
>
> +-------+--------+---------------+---------+---------+------ +-------+--=
----
>-------+ 2 rows in set (4.69 sec)
>
> t1 and t2 are both tables with +40000 rows and this query doesn't have
> this problem.
>

Monty. Please take a look at mail I've sent you on this subject. It expl=
ains the case in
which it works and it which it is not.

As you see in your case it uses "INDEX" to scan the first table - this me=
ans all columns from the table
are covered by the index so query can be resolved without data file consu=
lting

But please try to add one more column to t2 table so "index" type will b=
e no more possible and
it will turn to using filesort....


I've sent you the full example in the mail I refernce to.=20


--=20
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com M: +7 095 725 4955


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12474@lists.mysql.com
To unsubscribe, e-mail

Re: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY key LIMIT # uses filesort!

Hi!

>>>>> "Peter" == Peter Zaitsev writes:



Peter> Monty. Please take a look at mail I've sent you on this subject. It explains the case in
Peter> which it works and it which it is not.

Peter> As you see in your case it uses "INDEX" to scan the first table - this means all columns from the table
Peter> are covered by the index so query can be resolved without data file consulting

Peter> But please try to add one more column to t2 table so "index" type will be no more possible and
Peter> it will turn to using filesort....

Not in 4.0.4 :)

Already tested this..

Regards,
Monty

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12477@lists.mysql.com
To unsubscribe, e-mail

RE: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY

Hi!

>>>>> "von" == von Boehn writes:

>> Sorry, but I can't repeat the problem with 4.0.3.

von> Ok, here is a very simple testcase which should show the problem again.

von> how to repeat:



von> EXPLAIN SELECT * FROM test t1, test t2 WHERE t1.father = t2.id
von> ORDER BY t1.id LIMIT 2;
von> +-------+--------+---------------+---------+---------+------ -----+------+---
von> -------------+
von> | table | type | possible_keys | key | key_len | ref | rows |
von> Extra |
von> +-------+--------+---------------+---------+---------+------ -----+------+---
von> -------------+
von> | t1 | ALL | NULL | NULL | NULL | NULL | 12 |
von> Using filesort |
von> | t2 | eq_ref | PRIMARY | PRIMARY | 4 | t1.father | 1 |
von> |
von> +-------+--------+---------------+---------+---------+------ -----+------+---

On the current 4.0.4 code I get:

mysql> EXPLAIN SELECT * FROM test t1, test t2 WHERE t1.father = t2.id
-> ORDER BY t1.id LIMIT 2;
+-------+--------+---------------+---------+---------+------ -----+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+--------+---------------+---------+---------+------ -----+------+-------+
| t1 | index | NULL | PRIMARY | 4 | NULL | 12 | |
| t2 | eq_ref | PRIMARY | PRIMARY | 4 | t1.father | 1 | |
+-------+--------+---------------+---------+---------+------ -----+------+-------+
2 rows in set (0.00 sec)

So it looks like this case is now finally fixed.

>> You have to understand that to resolve a query the optimizer takes a
>> lot of parameters into account, including key distribution, key length
>> etc. Because of this we it's normally very difficulty to repeat ta
>> problem without having a repeatable test case.
>>
>> Just because of this problem we require that every email to
>> bugs@lists.mysql.com should have repeatable test case, either in the
>> email or uploaded to ftp://support.mysql.com.

von> I understand.

von> The point simply was that a I did send a testcase a month ago,
von> and that exactly this testcase didn't worked with 4.0.3 again.

Sorry, I must somehow have missed your original email :(
(Are you sure you posted it to bugs@; I couldn't find any email from
you with a test case during the last 3 months).

A not related issue (from an email you sent to mysql@lists.mysql.com):
We plan to in the near future also provide Debian packages of MySQL.

von> I tested 4.0.3 with half a douzend of other tables as well
von> which all failed to use the index.
von> That's why I thought that this a generally problem.

von> Sorry for the confusion.

No problem. Good that it's fixed now... (At least for this testcase :)

Regards,
Monty

--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12479@lists.mysql.com
To unsubscribe, e-mail

RE: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY

Hi,



> On the current 4.0.4 code I get:
> How-To-Repeat:
> mysql> EXPLAIN SELECT * FROM test t1, test t2 WHERE t1.father = t2.id
> -> ORDER BY t1.id LIMIT 2;
>
+-------+--------+---------------+---------+---------+------ -----+------+---
----+
> | table | type | possible_keys | key | key_len | ref | rows |
Extra |
>
+-------+--------+---------------+---------+---------+------ -----+------+---
----+
> | t1 | index | NULL | PRIMARY | 4 | NULL | 12 |
|
> | t2 | eq_ref | PRIMARY | PRIMARY | 4 | t1.father | 1 |
|
>
+-------+--------+---------------+---------+---------+------ -----+------+---
----+
> 2 rows in set (0.00 sec)
>
> So it looks like this case is now finally fixed.

[..]

> von> The point simply was that a I did send a testcase a month ago,
> von> and that exactly this testcase didn't worked with 4.0.3 again.
>
> Sorry, I must somehow have missed your original email :(
> (Are you sure you posted it to bugs@; I couldn't find any email from
> you with a test case during the last 3 months).

I did send it directly to one of the developers. The mail included an
attached mysql dump
with many rows, so I thought that it would make no sense to send it to the
mailinglist.
The mailing list isn't complete. There where a few more mails going from the
mysql people
directly to me and back, which where not send to the bugs list.



> von> Sorry for the confusion.
>
> No problem. Good that it's fixed now... (At least for this
> testcase :)

Yes, I'm really happy that this problem is fixed now.

In fact, I think that our problem is very commen and
that these queries which had problems are used quite often.
It's the typical case where you want to see the xx last created/changed
records in a table
and where you need some additional fields out of other tables as well.
For example, we have three different web project here,
(created by different people) which all suffer from this problem.



Thanks Peter for narrowing the problem pointing Monty into the right
direction.
Thanks Monty for fixing.



>A not related issue (from an email you sent to mysql@lists.mysql.com):
>We plan to in the near future also provide Debian packages of MySQL.

That's very exiting news.
Will you provide these packages at the mysql site
or will these packages be provided by debian?

I mean, do you work together with the Debian people
or will this be a service run by mysql alone?




kind regards
Gunnar

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12482@lists.mysql.com
To unsubscribe, e-mail