Latest change: ORDER BY timestamp DESC LIMIT 1 - Efficient?

I have开发者_如何学Go a simple save-log:

XiD (INT) what was saved  
UiD (INT) who done it  
TiD (TIMESTAMP) CURRENT_TIMESTAMP  

With all three fields in the primary key - to make it unique.

I want to inform the user of only the latest log-line (until she asks for more):

SELECT UiD,TiD FROM log WHERE XiD=? ORDER BY TiD DESC LIMIT 1  

But what happens if there are a billion records with the same XiD ?

Is the above efficient or will the MySql-server examine all the records (for ORDER BY)

- And if so: How do I extract the single latest record of a log like that, using the least server-calories ?

PS: I havent implemented anything yet, so any alternative scheme is possible : )

EDIT:

Perhaps it would be more efficient simply to store (overwrite) who and when (UiD+TiD) directly in fields on the XiD-record (besides in the log), so I can get them directly, only looking in the log when the user asks for more log-lines explicitly..

---

1. No, the engine will not examine all the records for ORDER BY. It will examine all those records returned by the WHERE clause. Sorry, missed this But what happens if there are a billion records with the same XiD ?. Yes, the engine will examine all the records for the ORDER BY clause. Here is the order of operations, as explained by Ben Nadel:

   1. FROM clause
   2. WHERE clause
   3. GROUP BY clause
   4. HAVING clause
   5. SELECT clause
   6. ORDER BY clause

2. IMHO, I don't think there can be any better way to extract the single latest record.

---

You need index on XiD ASC, TiD DESC to do that. Since UiD is not in the where clause of the query, and TiD is after UiD in the index, it cannot be used for sorting (which is indicated by filesort in the explain query results)

---

if your 'XID' columns has '*AUTO_INCREMENT*' U can use:

SELECT UiD,TiD FROM log WHERE XiD=LAST_INSERT_ID() ORDER BY TiD DESC LIMIT 1

ref: MySQL Manual