Selecting multiple "most recent by timestamp" in mysql

Question

I have a table containing log entries for various servers. I need to create a view with the most recent (by time) log entry for each idServer.

mysql> describe serverLog;
+----------+-----------+------+-----+-------------------+----------------+
| Field    | Type      | Null | Key | Default           | Extra          |
+----------+-----------+------+-----+-------------------+----------------+
| idLog    | int(11)   | NO   | PRI | NULL              | auto_increment | 
| idServer | int(11)   | NO   | MUL | NULL              |                | 
| time     | timestamp | NO   |     | CURRENT_TIMESTAMP |                | 
| text     | text      | NO   |     | NULL              |                | 
+----------+-----------+------+-----+-------------------+----------------+

mysql> select * from serverLog;
+-------+----------+---------------------+------------+
| idLog | idServer | time                | text       |
+-------+----------+---------------------+------------+
|     1 |        1 | 2009-12-01 15:50:27 | log line 2 | 
|     2 |        1 | 2009-12-01 15:50:32 | log line 1 | 
|     3 |        3 | 2009-12-01 15:51:43 | log line 3 | 
|     4 |        1 | 2009-12-01 10:20:30 | log line 0 | 
+-------+----------+---------------------+------------+

What makes this difficult (for me) is:

• Entries for earlier dates/times may be inserted later, so I can't just rely on idLog.
• timestamps are not unique, so I need to use idLog as a tiebreaker for "latest".

I can get the result I want using a subquery, but I can't put a subquery into a view. Also, I hear that subquery performance sucks in MySQL.

mysql> SELECT * FROM (
    SELECT * FROM serverLog ORDER BY time DESC, idLog DESC
    ) q GROUP BY idServer;
+-------+----------+---------------------+------------+
| idLog | idServer | time                | text       |
+-------+----------+---------------------+------------+
|     2 |        1 | 2009-12-01 15:50:32 | log line 1 | 
|     3 |        3 | 2009-12-01 15:51:43 | log line 3 | 
+-------+----------+---------------------+------------+

What is the correct way to write my view?

Answer 1

I recommend using:

CREATE OR REPLACE VIEW vw_your_view AS
  SELECT t.*
    FROM SERVERLOG t
    JOIN (SELECT sl.idserver,
                 MAX(sl.time) 'max_time'
            FROM SERVERLOG sl
        GROUP BY sl.idserver) x ON x.idserver = t.idserver
                               AND x.max_time = t.time

Never define an ORDER BY in a VIEW, because there's no guarantee that the order you specify is needed for every time you use the view.