CODEWITHSUNDEEP
mysqlsqldatabasequery-optimization
Alex Naspo
Alex Naspo

Reputation: 2092

Questionable SQL practice - Order By id rather than creation time

So I have an interesting question that I am not sure is considered a 'hack' or not. I looked through some questions but did not find a duplicate so here it is. Basically, I need to know if this is unreliable or considered bad practice.

I have a very simple table with a unique auto incrementing id and a created_at timestamp. (simplified version of my problem to clarify the concept in question)

+-----------+--------------------+
| 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. Any explanation of your reasonings would be very helpful as I am trying to understand the concept, not just simply get an answer. Thanks in advance.

Upvotes: 19

Views: 4317

Answers (5)

davidethell
davidethell

Reputation: 12018

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.

Upvotes: 13

Andy Lester
Andy Lester

Reputation: 93636

You shouldn't rely on ID for anything other than that it uniquely identifies a row. It's an arbitrary number that only happens to correspond to the order in which the records were created.

Say you have this table

ID  creation_date
1   2010-10-25
2   2010-10-26
3   2012-03-05

In this case, sorting on ID instead of creation_date works.

Now in the future you realize, oh, whoops, you have to change the creation date of of record ID #2 to 2010-09-17. Your sorts using ID now report the records in the same order:

1   2010-10-25
2   2010-09-17
3   2012-03-05

even though with the new date they should be:

2   2010-09-17
1   2010-10-25
3   2012-03-05

Short version: Use data columns for the purpose that they were created. Don't rely on side effects of the data.

Upvotes: 8

Mark Byers
Mark Byers

Reputation: 838156

There are a couple of differences between the two options.

The first is that they can give different results.

The value of created_at might be affected by the time being adjusted on the server but the id column will be unaffected. If the time is adjusted backwards (either manually or automatically by time synchronization software) you could get records that were inserted later but with timestamps that are before records that were inserted earlier. In this case you will get a different order depending on which column you order by. Which order you consider to be "correct" is up to you.

The second is performance. It is likely to be faster to ORDER BY your clustered index.

How the Clustered Index Speeds Up Queries

Accessing a row through the clustered index is fast because the row data is on the same page where the index search leads.

By default the clustered key is the primary key, which in your case is presumably the id column. You will probably find that ORDER BY id is slightly faster than ORDER BY created_at.

Upvotes: 6

Bohemian
Bohemian

Reputation: 425003

Ordering by id orders by insertion order.

If you have use cases where insertion can be delayed, for example a batch process, then you must order by created_at to order by time.

Both are acceptable if they meet you needs.

Upvotes: 4

user188654
user188654

Reputation:

Primary keys, especially of surrogate type, do not usually represent any kind of meaningful data aside from the fact that their mere function is to allow for uniquely identifiable records. Since dates in this case do represent meaningful data that has meaning outside of its primary function I'd say sorting according to dates is a more logical approach here.

Upvotes: 4

Related Questions