MySQL SQL getting most recent records in a table?

Question

I have an sql statement as below attempting to retrieve the most recent entries in a table. So I have two questions:

1. is it better to order by id or by date?

2. how do I rewrite this sql statement to re-order by date?

SELECT id, comment, DATE_FORMAT(entry_date, '%W %H:%i') FROM comments ORDER BY id DESC LIMIT 10

Answer 1

It depends on what you mean by most recent:

If you mean the most recently created record, then (in most cases) by primary key id will work.

If you mean the most recently updated record, then definitely by date.

To sort by date just change the field name: ORDER BY entry_date DESC

Answer 2

If you have to be 100% precise to sort for the newest rows, you must use the date field. Sorting for the time field when you want to sort for the newest makes pretty much sense and if indexed, sorting on dates will take the same time than to sort for the id-s.

The values for the id field are only guaranteed to be unique I assume, there is possibly no way to ensure that they are ordered by insertion time. For example the id field can be a cyclic field (6,7,8,-7,-6...), or a later modification on the table is allowed to change the id fields but will not know to preserve their ordering by insertion time, or parallel transactions might decide to insert new rows in a different order on the id and date fields.