12 messages in com.mysql.lists.bugsRE: optimizer bug: SELECT * FROM tbl_...| From | Sent On | Attachments |
|---|---|---|
| von Boehn, Gunnar | 29 Aug 2002 08:33 | |
| von Boehn, Gunnar | 29 Aug 2002 08:36 | |
| von Boehn, Gunnar | 29 Aug 2002 08:37 | |
| von Boehn, Gunnar | 29 Aug 2002 09:11 | |
| Sinisa Milivojevic | 29 Aug 2002 09:58 | |
| Michael Widenius | 02 Sep 2002 04:24 | |
| Michael Widenius | 02 Sep 2002 04:58 | |
| von Boehn, Gunnar | 02 Sep 2002 07:59 | |
| Peter Zaitsev | 03 Sep 2002 03:29 | |
| Michael Widenius | 03 Sep 2002 05:51 | |
| Michael Widenius | 03 Sep 2002 06:02 | |
| von Boehn, Gunnar | 03 Sep 2002 08:17 |
| Subject: | RE: optimizer bug: SELECT * FROM tbl_name,tbl_name2 ... ORDER BY key LIMIT # uses filesort! |
|---|---|
| From: | Michael Widenius (mon...@mysql.com) |
| Date: | 09/02/2002 04:24:14 AM |
| List: | com.mysql.lists.bugs |
Hi!
"von" == von Boehn <von> writes:
<cut>
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.
<cut>
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 bu...@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.