Ann Smith Ann Smith - 5 days ago 5
MySQL Question

Auto order by column (non-primary key)

Is it possible to alter a table so that any generic query will return data ordered by a column that is not indexed or a primary key? I would like

SELECT * from <table>


to have the same ordering as

SELECT * from <table> ORDER BY <misc_col>


without harming the integrity of the table or any of the current keys.

Answer

One way to do this is to use a view:

create view v_table as
    select t.*
    from t
    order by col;

Many databases either ignore such an order by or don't allow the syntax. However, MySQL allows the syntax and selecting from the view should return the results in the order that you want.

Note: An index on t(col) should improve performance.

Comments