如何使用按DESC排序的主键创建MySQL表并在插入新行后保持这种方式？

Question

I need to create an InnoDB table that I will use to add data to and constantly fetch the most recent 10 rows added to it. To avoid having to do an ORDER BY with every SELECT query to get those last 10 rows, I would like to have the table itself ordered by the Primary Key in DESC order so that I can skip the ORDER BY entirely and just do a SELECT ... LIMIT 10, which should automatically pull the most recent 10 rows added to the table.

How can I do that? Is it as simple as adding ORDER BY [PRIMARYKEY] DESC to the CREATE TABLE query? Will the table continue to be sorted in DESC order even after INSERTing new rows?

Answer 1

A RDBMS never provides any guarantees on the order of the rows in any of the tables it manages. The only way to get a specific order is to ask for one. For the case of MySQL, the rows happens to be sorted by the primary key in ascending order often time when that key is in auto increment mode, but it's not a guaranteed property.

Use ORDER BY on your queries to get the desired result.

On the other hand, the ordering will be faster if the primary key type is BTREE (which is the default on most engine).

The sorting direction isn't yet used on MySQL 5.5.