Bug #14816 VIEW performs filesort when using ORDER BY but underlying table does not
Submitted: 10 Nov 2005 9:40 Modified: 24 Nov 2005 0:29
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.15, 5.0.16-bk OS:Linux (Linux, Windows)
Assigned to: Evgeny Potemkin

[10 Nov 2005 9:40] Shane Bester
Description:
Performance when selecting from a view is suffering because of the filesort.  No filesort occurs when selecting from the table.

mysql> use test;
Database changedmysql> DROP VIEW IF EXISTS v1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id))ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),();
Query OK, 15 rows affected (0.00 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> CREATE VIEW v1 AS SELECT id FROM t1;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT id FROM t1 ORDER BY id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 15
        Extra: Using index
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id FROM v1 ORDER BY id\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 15
        Extra: Using index; Using filesort
1 row in set (0.00 sec)

mysql>

How to repeat:
DROP VIEW IF EXISTS v1;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id))ENGINE=MyISAM;
INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),();
CREATE VIEW v1 AS SELECT id FROM t1;
EXPLAIN SELECT id FROM t1 ORDER BY id\G
EXPLAIN SELECT id FROM v1 ORDER BY id\G

Suggested fix:
not sure.
[10 Nov 2005 10:08] Valerii Kravchuk
Thank you for a bug report. Verified just as described on 5.0.16-BK (ChangeSet@1.1972, 2005-11-05 22:45:54-08:00, igor@rurik.mysql.com).
[14 Nov 2005 21:57] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32252
[16 Nov 2005 0:18] Evgeny Potemkin
test_if_order_by_key() expected only Item_fields to be in order->item, thus
failing to find available index on view's field, which results in reported
error.

Fixed in 5.0.17, cset 1.1952.18.1
[24 Nov 2005 0:29] Paul Dubois
Noted in 5.0.17 changelog.