非常慢的MYSQL子查询 [英] Very Slow MYSQL Sub Query

问题描述

伙计们，我更像是一个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秒

但是这个相对类似的查询却嵌套了，需要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使用位置；使用临时的；使用文件排序

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

这篇关于非常慢的MYSQL子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！