Hi,

What's a faster method for retrieving the last timestamp in a table that has thousands of records? Using the MAX function or LIMIT 1? Or does it not matter?

SELECT * from tblname where id = 1234 ORDER BY datetime desc LIMIT 1
OR
SELECT MAX(datetime) from tblname where id = 1234

the two queries are not comparable, because of the columns they return

and LIMIT only works in MySQL

Hi,

What's a faster method for retrieving the last timestamp in a table that has thousands of records? Using the MAX function or LIMIT 1? Or does it not matter?

SELECT * from tblname where id = 1234 ORDER BY datetime desc LIMIT 1
OR
SELECT MAX(datetime) from tblname where id = 1234

The first is faster because it will already throw a syntax error during parsing/query compilation. The second may actually do some real work and, therefore, needs more time.

Assuming that you would fix the 1st query and use FETCH FIRST 1 ROW ONLY, you should have a look at the access plans to see what happens. Maybe DB2 uses the very same access plan and, thus, there will be no difference.

Well, the question is not trivial.

Generally speaking I'd prefere to use the MAX function to avoid a sort extra step caused by the ORDER BY clause.

How do you come to the conclusion that an ORDER BY requires a sort? ORDER BY only means that the DBMS has to return the rows in the specified order. Whether actually a sort is happening internally to produce the order or whether the DBMS does something else (like using an index, which already has the right order) is a completely different question.

So what you have to do is to look at the access plans to figure what is really happening internally. I can very well imagine that DB2 transforms an ORDER BY ... FETCH FIRST 1 ROW ONLY into a simple descend along the right-most edges in a B-Tree to find the largest value - exactly like it would do it for MAX().

Of course, this is dependent on the DBMS. Since you have shown NOT us valid DB2 syntax, we cannot be sure which system you are using.

I dared when I examined the plan_table.

First of all, for a fair comparison, you should either replace "*" by "datetime" in the first query, or alternatively move the second query into a subquery:
or else
The elements of the two pairs may even return different results, e.g. when several rows have the same timestamp.

When no index exists on the datetime column, the two queries of the first interpretation will have exactly the same access path (viz. a table scan followed by a sorting).
For the two last queries the second one will only have to sort the datetime column (like in the first two queries) but then re-access the table to get all matching rows. I.e.: two table scans.
So it would need twice the I/O for the tablespace as compare to the third query, but that one will need more I/O (and CPU) during the sorting since it will have to sort the full table.

Hence, the answer to the question "which is better" also depends on the sizes of the other columns in the table!

When an index exists with datetime as its first column, access paths for the two variants for each interpretation will be identical (as Knut Stolze already pointed out).

thanks much for the replies! yes, you're correct.. I was thinking in terms of postgresql syntax.. what I really meant was 'FETCH FIRST 1 ROW ONLY' and yes, SELECT * would probably return a different result than SELECT MAX(date)