Re: insert in an ordered set, then reorder

[Peter Brawley]

> Ho[w] to insert/update (orange, 2) before red or after blue? and reorder the set?

Do not try.

A fundamental attribute of relational tables is that physical row order in tables is undefined, that is, the engine is free to order them for its own convenience. For InnoDB, that convenience is defined in something you can't see, the btrees of its keys (indexes).

So, use keys to provide orderings that queries can efficiently execute, eg if a table with PK id will often be queried with an Order By this_column, add index(this_column) to the table.