Order of MySQL results - same as in MySQL DB?

[Grant Palin]

I've got a MySQL database with some data in it. I can retrieve and display data with no problem. My question is, is the order in which results are returned (WITHOUT using the ORDER BY clause) necessarily the same order in which the information is entered into the database?

So without using ORDER BY, will results from the query be in the same order as they are in the actual DB?

[raf]

No.

It could be, but it's not guarantied. It depends on the columns you include in your recordset, and which column-type they are and if they have an index.

I don't know for sure, but i believe that if you would include the auto-number variable as the first variable in your recordset, that yu would then get the records in the same order as they were inserted. (select * from table will very most likely return them as entered, but it's not certain)
But if you wouldn' t include it, and have more then one indexed column in your recordset, then i don't think you can be certain how they are returned.

[Grant Palin]

I don't have any indexes or auto-incrementing columns. Although, that raises an idea: I could add a numeric column to each record, assigning it a unique integer, and thereby arrange the records that way. Then, when I retrieve records from the DB, I could just order the results by that numeric column, and get the results in the same order as they are in the DB. Or maybe there's an easier way to do this...?

[raf]

Well, i add an auto-number column to almost each table. It's almost the only way to have a relational db and to build a fully dynamic databasedriven webapplication.

And you can indeed use that column for that purpose.

Another option is to include a column of type DATETIME and set the value Now() to it when you insert a record. Like

INSERT INTO table (var1, var2, createdatetime) VALUES(value1, value2, Now())

You can then get your record sorted on that variable.