Geek asks and answers

Very Slow MYSQL Sub Query

Guys, I'm more of an MSSQL guy, but right now I'm working on some MYSQL.

Iv wrote a simple query, with a subquery, and I cannot understand all my life why this is so slow.

This request:

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

Works in 0.0034 seconds

However, this relatively similar query, but nested, takes more than 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 anybody help? I am stuck.

There are about 900 rows in the checkin table. only the room_id column is indexed.

Greetings

EDIT Thank you guys .. heres the result of EXPLAIN

DEPENDENT SUBCURING checkin ref room_id room_id 202 const 1104 Using where; The use of temporary; Using filesort

+3
source share
3 answers

HAVING . MySQL, , , :

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
+4

, . , ( ), - MySQL. , - MAX, .

+2

Please run EXPLAIN for both queries. You may not have matching indexes in your columns.

Try the following:

EXPLAIN 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);

and

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

More articles:


All Articles