I have 2 tables, a users table and a log table which literally logs each time a user signs in.
What I'm trying to do is grab a list of all users with a certain rank who have not logged in for over a year. That said, I have a working query, but it's taking ~5 minutes to execute.
Here's what I'm currently working with:
SELECT u.usrid, u.username, u.rank, u.extras, l.dtime FROM users AS u JOIN ( SELECT userid, MAX(datetime) dtime FROM log GROUP BY userid ) AS l ON u.usrid = l.userid WHERE (u.rank = 'P' OR u.extras LIKE '%W%') AND l.dtime < DATE_SUB(NOW(), INTERVAL 1 YEAR) ORDER BY l.dtime DESC
It's a part of a very old and soon to be updated system... But until then, I'm trying to make the best of what I've been given to work with. That being said, I don't have access to actually alter the database schema so I'm hoping there is a way to optimize this query to work better.
Furthermore, the users table has ~500k rows while the log table has ~4m rows. With that many rows, I'm also wondering if it's a size issue. If so, what suggestions could I pass on to the DBA in order to improve it's ability to scale? I know I could simply store the last log in date somewhere separate for this particular use-case; but we use that log data for a lot of things so we need a way to be able to search it efficiently.