Are the order of rows in a MYSQL table fixed, so that queries always return them in that order?

Question

I have an application which collects data into a mysql table. The table has no unique id column, so I can't reference a specific row by id.

I want to write a dump application which every day dumps the new rows added to the table to upload them elsewhere. I could do it by adding a unique id field and storing the last id dumped, but I don't want to add an id column just for that to the table.

So I thought I store the number of rows in the table at every dump and use that number as an offset next time the table is dumped (select * from table limit verylargenumber offset x). Of course, it works only if there is a guarantee new rows always inserted at the end of the table, so all new rows will be after the offset.

I assume I can rely on that. Am I right?

Answer 1

No this isn't the case. The database will move stuff around to optimize and make queries faster. You would have to add an order by clause to your query to ensure any sort of order. You should definitely consider adding a unique id to your table.

Answer 2

No, you aren't. There is no surety as to the order in which the engine will return the rows. A table without a unique ID is generally not such a godo idea anyways. In this case, you definitely have reason enough to use one.

Answer 3

Similar to a file system, unless the table is optimized or defragmented, deleted data will free up a "slot" where new data will be inserted. It isn't always appended to the end of the table.

So say you have 3 rows: A, B, C

If you delete B, then your table will essentially look like A, [free space], C

So if you insert D into your table, it will now look like: A, D, C

Your best bet is to use a unique auto incrementing key. This will also speed up queries.