(For reference)
SELECT L.LoginName, U.LoginName AS MainLoginName,
U.LevelID,L.Message, L.LoginTime ,
L.LoginIP
FROM LRecord AS L
JOIN User AS U
ON L.UserID = U.UserID
WHERE L.LoginTime >= '2011-10-11 00:00:00'
AND L.LoginTime <= '2011-11-11 23:59:59'
AND U.RoomID = '163';
SELECT L.LoginName,
U.LoginName AS MainLoginName,
U.LevelID,
L.Message,
max(L.LoginTime) AS LoginTime ,
L.LoginIP
FROM LRecord AS L
JOIN User AS U ON( L.UserID = U.UserID )
WHERE L.LoginTime >= '2011-08-04 00:00:00'
AND L.LoginTime <= '2011-11-12 23:59:59'
AND U.RoomID = '163'
AND U.LevelID>4
AND L.LoginIP IN (2018872567,2018872567,
1948007961,3549143976,3549143976,1948007961,
977185930,977185930,977185930,977185930,
977185930)
GROUP BY L.LoginName, L.LoginIP
ORDER BY LoginIP ASC, LoginTime ASC;
> I select the ip and messages columns so that I could know the Visitors who and where during the time.
You will get some ip and some message. You won't get all of them.
> if you could provide any suggestion I will be appreciated.
I don't understand the goal of the query.
How many rows are returned by the queries? The first one may be hitting a million rows (between the two tables) to get the resultset. And it will have to generate a temp table of perhaps a million rows.