Best practice question for MySQL: order by id or date?

Question

This is kind of a noobish question, but it's one that I've never been given a straight answer on.

Suppose I have a DB table with the following fields and values:

| id | date_added          | balance |
+------------------------------------+
|  1 | 2009-12-01 19:43:22 | 1237.50 |
|  2 | 2010-01-12 03:19:54 |  473.00 |
|  3 | 2010-01-12 03:19:54 | 2131.20 |
|  4 | 2010-01-20 11:27:31 | 3238.10 |
|  5 | 2010-01-25 22:52:07 |  569.40 |
+------------------------------------+    

This is for a very basic 'accounting' sub-system. I want to get the most recent balance. The id field is set to auto_increment. Typically, I would use:

SELECT balance FROM my_table ORDER BY date_added DESC LIMIT 1;

But I need to make absolutely sure that the value returned is the most recent... (see id# 2 & 3 above)

1) Would I be better off using:

SELECT balance FROM my_table ORDER BY id DESC LIMIT 1;

2) Or would this be a better solution?:

SELECT balance FROM my_table ORDER BY date_added,id DESC LIMIT 1;

AFAIK, auto_increment works pretty well, but is it reliable enough to sort something this crucial by? That's why I'm thinking sorting by both fields is a better idea, but I've seen some really quirky behavior in MySQL when I've done that in the past. Or if there's an even better solution, I'd appreciate your input.

Thanks in advance!

Brian

Answer 1

Maybe is faster by id, but safer by datetime; use the latter if have performance issues add an index.