MySql - Reordering the rows of a table

Question

I have a table (with no PK) that is being inserted into by an regular event. It is currently unordered, and I would like to order it in the fashion of

select * from t1 order by col1 ASC,col2 DESC

Constraints are as follows:

• For performance reasons I can't create a new temporary table to store the ordered data in

• For cleanness of the user-interface I must assume that all select statements will be without an order by clause

• Because of the insert into mechanism I can't order the table while it's being built up

Is there some way of doing this that I don't know about/have overlooked?

Answer 1

Is there some way of doing this that I don't know about/have overlooked?

No. And here is a very good explanation. Quoting the most revelant part:

In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause.

That being said, if you remove your performance requirements, you could create an ordered view and select from it. For example (not sure about the syntax):

CREATE VIEW foobar AS
select * from t1 order by col1 ASC,col2 DESC;

But it's going to be slow(ish).