Bug #16862 Views appear to have no indices
Submitted: 27 Jan 2006 23:20 Modified: 28 Jan 2006 0:56
Reporter: Alan Bruce Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Views Severity:S4 (Feature request)
Version:5.0.18 OS:Linux (linux)
Assigned to: CPU Architecture:Any

[27 Jan 2006 23:20] Alan Bruce
Perhaps this is a feature request, although it seems like a bug report.

In one of the MySQL forums, Oleksandr Byelkin (on April 21, 2005) wrote:

"If merge algorithm is used, then indexes of underlying tables will be used".

When I create a view from a single table, and then I query the view with DESCRIBE, none of the indices from the underlying table can be seen, even if the ORDER BY clause is used in the CREATE VIEW statement. 

The view appears to be an unindexed table, which means that any application checking to see if the table is indexed before ordering the table will generate an error like, "Table is not indexed."

We were hoping to use views of MySQL 5.x so that a legacy application could access several new, normalized tables as if they were a single old table. The application refuses to use the views since they seem not to be indexed.

How to repeat:
This can be reproduced by (1) creating a simple table with two columns, making column 1 the primary key and creating a second index on the second column, (2) creating a view using a simple "select * from simple_table order by column_one" (it doesn't seem to matter if you use MERGE or not) and (3) running a DESCRIBE simple_view. The view has no keys, even though the table has two.

Suggested fix:
Perhaps a KEY or INDEX clause as in CREATE TABLE could be added to CREATE VIEW? 
Perhaps the ORDER BY clause would create a virtual index on the column(s) referenced by the clause?