mysqlsqldatabasequery-optimization

Can I order by id rather than creation time?


I have a very simple table with a unique auto incrementing id and a created_at timestamp.

+-----------+--------------------+
| id        |created_at          |
+-----------+--------------------+
| 1         |2012-12-11 20:35:19 |
| 2         |2012-12-12 20:35:19 |
| 3         |2012-12-13 20:35:19 |
| 4         |2012-12-14 20:35:19 |
+-----------+--------------------+

Both of these columns are added dynamically so it can be said that a new 'insert' will ALWAYS have a greater id and ALWAYS have a greater date.

OBJECTIVE - very simply grab the results ordered by created_at in descending order

SOLUTION ONE - A query that orders by date in descending order

SELECT * FROM tablename
ORDER BY created_at DESC

SOLUTION TWO - A query that orders by ID in descending order

SELECT * FROM tablename
ORDER BY id DESC

Is solution two considered bad practice? Or is solution two the proper way of doing things?


Solution

  • In typical practice you can almost always assume that an autoincrement id can be sorted to give you the records in creation order (either direction). However, you should note that this is not considered portable in terms of your data. You might move your data to another system where the keys are recreated, but the created_at data is the same.

    There is actually a pretty good StackOverflow discussion of this issue.

    The basic summary is the first solution, ordering by created_at, is considered best practice. Be sure, however, to properly index the created_at field to give the best performance.