First, let's see if it is doing what you want.
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;
Note that it is GROUPing BY two fields, but there are 5 non-aggregate values in the SELECT. Which IP and Message do you expect to get? It may not be the one corresponding to the last LoginTime.
> `LoginIP` int(10) unsigned NOT NULL DEFAULT '0',
If that is an IP address, it cannot handle IPv6.
To assist in analyzing slow SELECTs, please provide
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]
How much RAM do you have?
It could be as simple as having too small a key_buffer. Read
http://mysql.rjweb.org/doc.php/memory
Switching to InnoDB may help, but read
http://mysql.rjweb.org/doc.php/myisam2innodb
Perhaps you pasted things wrong? There is no way this would use the index on LoginIP:
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'