Bug #2538 Filesort on merge table with DESC

• Submitted: 27 Jan 2004 18:49
• Modified: 29 Jan 2004 13:22
• Reporter: Nis Peder Bonde
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.17
• OS: Linux (Linux)
• Assigned to: Sergei Golubchik
• CPU Architecture: Any

[27 Jan 2004 18:49] Nis Peder Bonde

Description:
It seems that a merge table can not optimize a statement like the following:

mysql> explain select * from log_gw where num<59168849 ORDER BY num DESC LIMIT 1;
+--------+-------+---------------+---------+---------+------+----------+-----------------------------+
| table  | type  | possible_keys | key     | key_len | ref  | rows     | Extra                       |
+--------+-------+---------------+---------+---------+------+----------+-----------------------------+
| log_gw | range | PRIMARY       | PRIMARY |       4 | NULL | 59167081 | Using where; Using filesort |
+--------+-------+---------------+---------+---------+------+----------+-----------------------------+
1 row in set (0.00 sec)

If I do the same on one of the underlying tables, the query is optimized:

mysql> explain select * from log_gw_11 where num<59168849 ORDER BY num DESC LIMIT 1;
+-----------+-------+---------------+---------+---------+------+---------+-------------+
| table     | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+-----------+-------+---------------+---------+---------+------+---------+-------------+
| log_gw_11 | range | PRIMARY       | PRIMARY |       4 | NULL | 4167082 | Using where |
+-----------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)

The num column is defined as a primary key both in the merge table and in the underlying tables:

| num              | int(11)      |      | PRI | 0       |       |

How to repeat:
I can repeat it in live tables, but I am not sure if a small example would show anything, since I suspect the optimizer to work differently on small datasets.

[29 Jan 2004 12:43] MySQL Verification Team

Nothing to do with MERGE tables.

It is behaving the same with MyISAM tables:
+ explain select * from t1 where num < 50000 order by num limit 1;
+ id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+ 1     SIMPLE  t1      range   PRIMARY PRIMARY 4       NULL    52077   Using where

We have on our TODO to fix this in 4.1. or 5.0, but meanhilw you have to help 
MySQL with USING / FORCE INDEX

[29 Jan 2004 13:22] Sergei Golubchik

Not a bug.
This is an optimization that was added in 4.1 only.