mysql - 根据最新时间戳选择记录

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

我需要根据最新时间戳和state_id = 2选择itemid。

我有这个疑问 http://sqlfiddle.com/#!2/a3d41/1/0 但我认为它没有针对大表进行优化，有什么想法吗？谢谢!

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 引擎在表上查找的次数。或表扫描，无论你怎么调用它。