How can I get the biggest value?

Question

Here is my table:

-- log
+----+---------+------------+
| id | user_id |    seen    |
+----+---------+------------+
| 1  | 2342    | 1442664886 |
| 2  | 3244    | 1442665851 |
| 3  | 2342    | 1442711823 |
| 4  | 7654    | 1442864219 |
| 5  | 3244    | 1442954080 |
| 6  | 9984    | 1442984716 |
+----+---------+------------+

I want to get the biggest seen time for a specific user as last seen. I can do that by these two queries:

First query:

SELECT seen AS last_seen
FROM log
WHERE user_id = :id
ORDER BY seen DESC
LIMIT 1

Second query:

SELECT MAX(seen) AS last_seen
FROM log
WHERE user_id = :id

Well which one is the standard way? Should I go with which one? Is there any different in the performance?

score 2 · Accepted Answer · answered Aug 12 '17 at 18:28

2

They are both fine. Both will take advantage of an index on log(user_id, seen).

The first is often preferable because you can pull in the whole row and get information from other columns.

answered Aug 12 '17 at 18:28

Gordon Linoff

1,242,037
58
646
786

not OP , but if you let me ask , `ORDER BY seen DESC` would the ordering operation take more time than finding the max value ? – Ali Faris Aug 12 '17 at 18:32
@Ali see the duplicate topic for a detailed answer. – Shadow Aug 12 '17 at 18:38

How can I get the biggest value?

1 Answers1