thoughts on performance of max vs order by limit 1

Today in a interview i was asked to write a SQL that query for the very hour of most sent msg per user. When i write down the sql with “group by … order by … desc limit 1”, I was asked if there were a large amount of data, would it be right to do that?

In the way back home, I thought of there is another function “max()” in SQL which do the same trick. Intuitively, I would believe that in SQL engine, if I build an index for the certain column, the sql parser would do logical improvement for querying.

So I google for it, see how sql execution really do:

There is an actual difference between max() and ORDER BY DESC + LIMIT 1. Many people seem to miss that. NULL values sort first in descending sort order. So ORDER BY timestamp DESC LIMIT 1 returns a row with timestamp IS NULL if it exists, while the aggregate function max() ignores NULL values and returns the latest non-null timestamp.

see more detail: https://stackoverflow.com/questions/34246403/performance-of-max-vs-order-by-desc-limit-1