Aloogy Aloogy - 1 month ago 5
MySQL Question

How to speed up MySQL table join

I have two tables, one containing some user details, another containing a running log of user requests including timestamp and location columns. They both share a common

user_id
column.

I'm basically trying to retrieve a list of all user information with their latest location and timestamp included.

This is how I'm currently achieving this, and it's definitely working - however this query in particular is taking over 2 seconds on average.

SELECT u.*, o.time, o.location
FROM users u
LEFT JOIN
(SELECT DISTINCT user_id,
time,
location
FROM onlineStatus o
WHERE time = (select MAX(time)
FROM onlineStatus
WHERE user_id = o.user_id)
) AS o
ON o.user_id = u.user_id
WHERE user_type = "1"
AND user_enabled = "0"
AND user_realm = ?
GROUP BY u.user_id


This is frustrating when I need to run multiple of these iterating over different
user_type
's.
I'm not entirely sure how to speed this up, any help would be appreciated.
Thanks.

Answer

You can rewrite that query and make it faster depending on the size of both tables, but you should introduce new table and update it when you insert into the onlineStatus table and then join that table with the user table:

CREATE TABLE onlineStatusLast ( user_id int unsigned not null primary key , last_time timestamp , location varchar(100) , KEY last_time(last_time) );

INSERT INTO onlineStatusLast (user_id, location, last_time) 
values (1, 'Canada', now()) 
on duplicate key update last_time=NOW(), locaton='Canada';

Populate table with

INSERT INTO onlineStatusLast (user_id, last_time) 
select user_id, MAX(time) FROM onlineStatus group by user_id

Your query would become this:

SELECT u.*, o.last_time, o.location 
FROM users u 
LEFT JOIN onlineStatusLast o
AS o ON o.user_id = u.user_id 
WHERE user_type = "1" 
AND user_enabled = "0" 
AND user_realm = ?