Выберите запись на основе последней отметки времени

Question

Я видел несколько подобных типов вопросов, однако я не смог найти лучшего решения. Мне нужно оптимизировать этот запрос.

itemid           state_id                timestamp
======================================================
  1              1              2012-08-11 00:00:00
  1              2              2012-08-12 00:00:00
  1              3              2012-08-13 00:00:00
  2              1              2012-08-10 00:00:00
  2              2              2012-08-13 00:00:00   <=== lastest  state_id =2
  3              1              2012-08-10 00:00:00
  3              2              2012-08-13 00:00:00
  3              4              2012-08-15 00:00:00
  4              1              2012-08-10 00:00:00
  4              3              2012-08-13 00:00:00
  4              2              2012-08-16 00:00:00   <=== lastest state_id =2
  5              1              2012-08-16 00:00:00

Мне нужно выбрать itemid на основе последней отметки времени и state_id = 2.

У меня есть этот запрос http://sqlfiddle.com/, но я думаю, что он не оптимизирован для большой таблицы, Есть идеи? Спасибо!

SELECT *
FROM hops h
WHERE h.timestamp = (
SELECT MAX( h2.`timestamp` )
FROM hops h2
WHERE h.`itemid` = h2.`itemid` )
AND h.state_id = 2

Answer 1

SELECT * FROM hops a
inner join (SELECT itemid, MAX(timestamp) FROM hops group by itemid) b
ON a.itemid  = b.itemid
 where a.STATE_ID = 2

Это выполнит внутреннее объединение для подзапроса, который фактически должен выбирать только записи, связанные со значением метки времени MAX в соответствующей таблице.

Таким образом, вы будете выбирать меньше строк, которые при внутреннем объединении будут возвращать только те строки, которые вы выбрали в подзапросе, но с выводом результата, указанным с помощью SELECT *

Теперь вы получили объединение вместо использования предложения WHERE, что позволит свести к минимуму количество поисков вашей таблицы SQL вашим движком. или таблица сканирует, как вы это называете.

Answer 2

Я думаю, что вам нужно сделать тест, чтобы увидеть, какое решение лучше всего оптимизировать.

SELECT itemid, timestamp 
FROM hops t1 
WHERE state_id = 2 
AND timestamp > (
   SELECT MAX(timestamp)
   FROM hops t2 
   WHERE t1.itemid = t2.itemid 
   AND t2.state_id != 2
)
GROUP BY itemid

Я проверил код, и он работает нормально, вы можете увидеть, что он работает на http://sqlfiddle.com/

Answer 3

Предполагая, что вы ищете только одну возвращаемую запись, вы можете использовать что-то вроде:

SELECT *
FROM hops h
WHERE
h.state_id = 2
ORDER BY timestamp DESC limit 1