Retrieve last row in mysql with shared primary key

Question

I have a table with a primary key of 2 values - id and timestamp. The table contains multiple rows for each item_id, and the timestamp is what should make it unique. Can I execute an efficient query considering this setup? I want to find the latest appearance of an item_id. Any ideas?

CREATE TABLE IF NOT EXISTS table (
  item_id varchar(30) NOT NULL,
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ...
  PRIMARY KEY  (item_id, ts)
);

Answer 1

Do you mean MAX()?

SELECT item_id, MAX(ts) as TS
FROM `table`
GROUP BY item_id

However, if you wanted to get all columns based from the latest item_id, you can put this query inside subquery and join it to the table itself:

SELECT  a.*
FROM    `table` a
        INNER JOIN
        (
            SELECT item_id, MAX(ts) as TS
            FROM `table`
            GROUP BY item_id
        ) b ON a.item_id = b.item_id
                AND a.ts = b.TS

Answer 2

You can either pass the item_id into a variable or set it directly into the query and as long as you have an index for the table containing both fields, the query optimizer will utilise it and give you the latest record for the item_id:

SET @ID := 10;
SELECT item_id, ts 
FROM table 
WHERE item_id = @ID
ORDER BY item_id,ts DESC
LIMIT 1