| 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: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.0.15, 5.0.16-bk | OS: | Linux (Linux, Windows) |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[10 Nov 2005 10:08]
Valeriy 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.
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.