'INSERT' phenomena

[swayguy]

Greets!

It is know that if you sequencially INSERT data into a table then the new record is added to the end:

---

record 0

record 1

newest

This is great! But my problem occurs when I DELETE the oldest record then, in the same code snippet, INSERT a new record. This happens:

newest 2 <- just inserted

record 1

newest

Why will php not add the data to the end thus keeping everything in chronological order. I'm starting to sulk =) It seems that php is resourcefully combining the DELETE with the INSERT and just using the old 'slot'.

Does anybody know how to get around this? It's ruining my life! Heh.
Thanks in advance.

[drew010]

to reduce overhead used by the tables, mysql will insert the data wherever it can, in this case it sees an empty spot from the previous delete so it puts the data there.
it has nothing to do with php, but is actually behavior of mysql.

if you really need the item to be added at the end, and you have no column that can identify when the record was inserted, the query
optimize table tablename
will remove the empty space and then your next insert will be at the end.

[swayguy]

Dude. That was perfect! Thanks heaps, I can now use the natural insertion pattern to keep records sorted.

=)

Pleased.