非常慢的 MYSQL 子查询

2022-01-23 00:00:00 subquery mysql

伙计们，我更像是一个 MSSQL 人，但我现在正在研究一些 MYSQL.

Guys, im more of a MSSQL guy but im working on some MYSQL right now.

我写了一个简单的查询，带有一个子查询，我一辈子都无法理解为什么它这么慢.

Iv written a simple query, with a subquery and I cant understand for the life of me why its so slow.

这个查询:

   SELECT MAX(timestamp), user, status FROM checkin WHERE room_id = 'Room Name' AND timestamp        > DATE_SUB(Now() ,INTERVAL 4005 SECOND) GROUP BY user

在 0.0034 秒内运行

Runs in 0.0034 seconds

然而这个相对相似但嵌套的查询需要超过 6 秒..

Yet this relatively similiar query but nested, takes over 6 seconds ..

SELECT user, status FROM checkin
WHERE timestamp IN
(SELECT MAX(timestamp) FROM checkin WHERE room_id = 'Room Name' AND timestamp > DATE_SUB(Now() ,INTERVAL 4005 SECOND) GROUP BY user)

有人可以帮忙吗?我卡住了.

Can anyone please help? Im stuck.

checkin"表中只有大约 900 行.只有 room_id 列被索引.

The table "checkin" only has about 900 rows in it. only the room_id column is indexed.

干杯

编辑谢谢大家..这是解释的结果

EDIT Thanks guys .. heres the result of the EXPLAIN

DEPENDENT SUBQUERY checkin ref room_id room_id 202 const 1104 使用 where；使用临时的；使用文件排序

DEPENDENT SUBQUERY checkin ref room_id room_id 202 const 1104 Using where; Using temporary; Using filesort

推荐答案

考虑使用 HAVING 子句来实现相同的结果.MySQL 在子查询优化方面出了名的差，试试这个:

Look into using a HAVING clause to achieve the same results. MySQL is notoriously bad at sub-query optimization, try this:

SELECT MAX(timestamp) as ts, user, status 
FROM checkin
WHERE room_id = 'Room Name' 
AND   timestamp > DATE_SUB(Now() ,INTERVAL 4005 SECOND)
GROUP BY user
HAVING timestamp = ts

还要确保 timestamp

或者:

SELECT user, status 
FROM checkin
WHERE room_id = 'Room Name' 
AND   timestamp > DATE_SUB(Now() ,INTERVAL 4005 SECOND)
AND NOT EXISTS (SELECT * FROM checkin as newer 
                WHERE newer.timestamp>checkin.timestamp
                AND newer.room_id = 'Room Name'
                AND newer.user = checkin.user)
GROUP BY user

相关文章