CyberJunkie
CyberJunkie

Reputation: 22674

MYSQL order by date or primary key?

I have a table with an auto-increment primary key column and a datetime column. I want to return rows ordered by the date they were created.

Does it matter if I order by the date or the primary key? I imagine that it's a faster by primary key opposed to a date format of 0000-00-00 00:00:00 or a timestamp. New records will have greater primary keys. Yet everyone orders by date fields.

Upvotes: 4

Views: 1998

Answers (1)

Ariel
Ariel

Reputation: 26753

Order by the date field, but make sure to put an index on the date field.

A date field is a binary field, it's just as fast as the primary key. (A date is 3 bytes vs an integer primary key which is 4.) It does not use a string to order it as you appear to assume.

And more importantly it indicates your intent, (plus it's possible for the primary key to wrap around).

Also, if you use innodb it's completely possible for the primary key not to be in order depending on how transactions interleave.

Also, if you are skilled at reading query plans add the date column to the end of the same index that is used for the query. However this only applies in specific situations, so only do it when you know it will help. i.e. don't just do it blindly.

Upvotes: 8

Related Questions