mysql - 为每个连接的行在每个子组中选择最新的

Question

我有两个表：session和session_states。第一个表有一些关于会话的一般信息。

第二个表包含每个会话的状态历史；多行可以连接到同一个会话（列session_id）；具有最新时间戳的行表示给定会话的当前/实际状态。

会话

id   search_criteria   some_other_info
1    aaa               something
2    bbb               something
3    aaa               something

session_states

pk   session_id   state   timestamp
1    1            new     01/01/1970 00:01
2    1            active  01/01/1970 00:02
3    2            new     01/01/1970 01:01
4    3            new     01/01/1970 02:01

我正在尝试创建一个查询，该查询允许按列search_criteria排序并表示与其当前状态匹配的会话列表。像这样的东西（WHERE search_criteria = 'aaa'）：

id   search_criteria   some_other_info   state   timestamp
1    aaa               something         active  01/01/1970 00:02
3    aaa               something         new     01/01/1970 02:01

我的查询：

SELECT s.id, s.search_criteria, s.some_other_info, st.state, st.timestamp
FROM sessions s
    JOIN
    (SELECT session_id, state, MAX(timestamp) FROM session_states GROUP BY session_id DESC) st
    ON s.id = st.session_id
WHERE search_criteria = 'aaa';

当每个表中有 400k+ 行时，查询需要 1+ 秒才能执行。查询分析器说，在 JOIN 的右侧，我们遍历每一行。

有什么方法可以更有效地做到这一点？

PS 我只能使用 MySQL 5.6 / 5.7。

标签： mysqljoingroup-by