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

Personally I'd never trust an autoincrement in that way. I'd sort by the date.

I'm pretty sure that the ID is guaranteed to be unique, but not necessarily sequential and increasing.

Answer 2

If there is a chance you'll get two added with the same date, you'll probably need:

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

(note the 'descending' clause on both fields).

However, you will need to take into account what you want to happen when someone adds an adjusting entry of the 2^nd of February which is given the date 31^st January to ensure the month of January is complete. It will have an ID greater than those made on the 1^st of February.

Generally, accounting systems just work on the date. Perhaps if you could tell us why the order is important, we could make other suggestions.

---

In response to your comment:

I would love to hear any other ideas or advice you might have, even if they're off-topic since I have zero knowledge of accounting-type database models.

I would provide a few pieces of advice - this is all I could think of immediately, I usually spew forth much more "advice" with even less encouragement :-) The first two, more database-related than accounting-relared, are:

First, do everything in third normal form and only revert if and when you have performance problems. This will save you a lot of angst with duplicate data which may get out of step. Even if you do revert, use triggers and other DBMS capabilities to ensure that data doesn't get out of step.

An example, if you want to speed up your searches on a last_name column, you can create an upper_last_name column (indexed) then use that to locate records matching your already upper-cased search term. This will almost always be faster than the per-row function upper(last_name). You can use an insert/update trigger to ensure the upper_last_name is always set correctly and this incurs the cost only when the name changes, not every time you search.

Secondly, don't duplicate data even across tables (like your current schema) unless you can use those same trigger-type tricks to guarantee the data won't get out of step. What will your customer do when you send them an invoice where the final balance doesn't match the starting balance plus purchases? That's not going to make your company look very professional :-)

Thirdly (and this is more accounting-related), you generally don't need to worry about the number of transactions when calculating balances on the fly. That's because accounting systems usually have a roll-over function at year end which resets the opening balances.

So you're usually never having to process more than a year's worth of data at once which, unless you're the US government or Microsoft, is not that onerous.

Answer 3

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