MySQL query help

[SPOOKMAN]

First off Hi All,

I need help with a query.

 

I have 2 tables users and sessions

in users I have (user, status, ip)

in sessions I have (sessionid, user, stoptime)

 

In users there user is unique and in sessions the sessionid is unique.

 

I what to select the max sessionid for all users in users table

I worte this query but it takes forever to run and never seems to finish, the sessions table has about 50000 rows.

 

SELECT MAX(sessionid) FROM sessions s1 
WHERE s1.users IN 
(SELECT u1.user from users u1) group by s1.username

this returns all the correct sessions for each users

 

SELECT * FROM sessions s2 WHERE s2.sessionid IN (SELECT MAX(sessionid) FROM sessions s1 
WHERE s1.users IN 
(SELECT u1.user from users u1) group by s1.username)

This runs forever

 

I am looking for a query that returns the results

user,status,ip,sessionid,stoptime

 

I have not included the join in the code as I an batteling with getting the correct info back from the above codes

 

 

 

 

 

 

[bh]

Hi,

 

First use Indexes to your searched columns (sl.users) and second use JOIN, it will be faster...

[fenway]

Show us the EXPLAIN output.