mysql子查询慢,非常慢的MYSQL子查询

最新推荐文章于 2024-09-20 22:28:47 发布

Aurora曙光

最新推荐文章于 2024-09-20 22:28:47 发布

阅读量188

点赞数

文章标签： mysql子查询慢

博主在工作中遇到了MySQL查询效率低下的问题，一个包含子查询的SQL语句执行时间过长。经过分析，发现子查询优化是关键。解决方案包括使用HAVING子句替代子查询，确保timestamp字段有索引，或者使用NOT EXISTS结合子查询来提高查询效率。通过这些调整，查询性能得到了显著提升。

摘要由CSDN通过智能技术生成

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.

This query:

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

Runs in 0.0034 seconds

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.

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

Cheers

EDIT

Thanks guys .. heres the result of the EXPLAIN

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

解决方案

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

also make sure that there is an index on timestamp

Alternatively:

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

关注