MySQL Forums
Forum List  »  Optimizer & Parser

table join optimize help!
Posted by: kelvin chou
Date: November 14, 2011 01:10AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
table join optimize help!
1951
November 14, 2011 01:10AM
935
November 14, 2011 09:24PM
1162
November 16, 2011 01:20AM
964
November 16, 2011 09:41PM
1027
November 18, 2011 01:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.