table join optimize help!

[kelvin chou]

hi everyone:
I try several days to improve the speed of the following queries.Once the query is executed ,the other sessions selecting the same table will be locked !!
I doubt the table lock is not only effect insert but also select..
following is my statement:

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'

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
| 1 | SIMPLE | L | range | UserID,LoginIP,LoginTime,| LoginIP | 4 | NULL | 14924 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | U | eq_ref | PRIMARY,LevelID,RoomID | PRIMARY | 4 | MainDB.L.UserID | 1 | Using where |

any one can help optimize? Thanks a million


CREATE TABLE `User` (
`UserID` int(10) unsigned NOT NULL DEFAULT '0',
`LoginName` varchar(20) NOT NULL DEFAULT '',
`Password` varchar(30) NOT NULL DEFAULT '',
`LevelID` tinyint(3) unsigned NOT NULL DEFAULT '0',
`RoomID` int(3) unsigned NOT NULL DEFAULT '0',
`CreateDate` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
`Currency` varchar(3) NOT NULL DEFAULT 'RMB',
PRIMARY KEY (`UserID`),
UNIQUE KEY `LoginName` (`LoginName`,`LevelID`,`RoomID`),
KEY `LevelID` (`LevelID`),
KEY `RoomID` (`RoomID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `LRecord` (
`UserID` int(10) NOT NULL,
`LoginTime` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
`LoginName` varchar(20) NOT NULL DEFAULT '',
`Password` varchar(30) DEFAULT NULL,
`LoginIP` int(10) unsigned NOT NULL DEFAULT '0',
`Message` text NOT NULL,
KEY `UserID` (`UserID`),
KEY `LoginIP` (`LoginIP`),
KEY `LoginTime` (`LoginTime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8



Edited 1 time(s). Last edit at 11/14/2011 03:16AM by kelvin chou.