客户的表中大概有几W条记录,我通过模糊查询后,电脑直接卡了~~~SELECT u.id,username FROM mailuser u, policy_assignment pa WHERE u.status=" + ArchivingConst.ENABLED + " AND pa.type=? AND u.id=pa.referenceid AND id >= 0 AND username LIKE ? ORDER BY username这个是我的sql语句..CREATE TABLE `mailuser` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(128) default NULL,
`aliasname` varchar(128) default NULL,
`domain` varchar(128) default NULL,
`passwd` varchar(32) default NULL,
`firstname` varchar(255) default NULL,
`lastname` varchar(255) default NULL,
`middlename` varchar(255) default NULL,
`fullname` varchar(255) default NULL,
`authenticName` varchar(128) default NULL,
`mailserver` varchar(255) default NULL,
`mailpath` varchar(255) default NULL,
`gid` int(11) default NULL,
`roleId` int(11) default NULL,
`supervisorId` int(11) default NULL,
`mailRule` blob,
`preference` blob,
`quota` int(11) default NULL,
`lastArchiveTime` datetime default NULL,
`lastStubTime` datetime default NULL,
`lastRemoveFromServerTime` datetime default NULL,
`lastRemoveFromArchiveTime` datetime default NULL,
`createTime` datetime default NULL,
`lastModifyTime` datetime default NULL,
`lastAccessTime` datetime default NULL,
`status` int(11) default '1',
`description` varchar(100) default NULL,
`canaccessarchive` char(1) default 'T',
`usesystempolicy` char(1) default 'T',
`canrestore` char(1) default 'T',
`hostname` varchar(128) default NULL,
`displayname` varchar(128) default NULL,
`legacyExchangeDN` varchar(128) default NULL,
`ext1` varchar(255) default NULL,
`ext2` varchar(255) default NULL,
`ext3` varchar(255) default NULL,
`ext4` varchar(255) default NULL,
`ext5` varchar(255) default NULL,
`ext6` int(11) default NULL,
`ext7` date default NULL,
`distinguishedName` varchar(255) default NULL,
`bEnable_archive_email` char(1) default NULL,
`bEnable_archive_im` char(1) default NULL,
`bEnable_archive_file` char(1) default NULL,
`bEnable_archive_misc` int(11) default NULL,
`bEnable_stub_email` char(1) default NULL,
`mDBOverHardQuotaLimit` varchar(255) default NULL,
`userType` varchar(50) default NULL,
`uniqueID` varchar(150) default NULL,
`physicalDeliveryOfficeName` varchar(255) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `mailuser_i0` (`uniqueID`,`userType`),
KEY `mailuser_i1` (`lastAccessTime`),
KEY `mailuser_i2` (`supervisorId`),
KEY `mailuser_i3` (`gid`),
KEY `mailuser_i4` (`authenticName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8CREATE TABLE `policy_assignment` (
`type` char(16) NOT NULL default '',
`policyid` int(12) default NULL,
`referenceid` int(12) NOT NULL default '0',
PRIMARY KEY (`type`,`referenceid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8这个是那两张表,
请问如何优化~~~
`id` int(11) NOT NULL auto_increment,
`username` varchar(128) default NULL,
`aliasname` varchar(128) default NULL,
`domain` varchar(128) default NULL,
`passwd` varchar(32) default NULL,
`firstname` varchar(255) default NULL,
`lastname` varchar(255) default NULL,
`middlename` varchar(255) default NULL,
`fullname` varchar(255) default NULL,
`authenticName` varchar(128) default NULL,
`mailserver` varchar(255) default NULL,
`mailpath` varchar(255) default NULL,
`gid` int(11) default NULL,
`roleId` int(11) default NULL,
`supervisorId` int(11) default NULL,
`mailRule` blob,
`preference` blob,
`quota` int(11) default NULL,
`lastArchiveTime` datetime default NULL,
`lastStubTime` datetime default NULL,
`lastRemoveFromServerTime` datetime default NULL,
`lastRemoveFromArchiveTime` datetime default NULL,
`createTime` datetime default NULL,
`lastModifyTime` datetime default NULL,
`lastAccessTime` datetime default NULL,
`status` int(11) default '1',
`description` varchar(100) default NULL,
`canaccessarchive` char(1) default 'T',
`usesystempolicy` char(1) default 'T',
`canrestore` char(1) default 'T',
`hostname` varchar(128) default NULL,
`displayname` varchar(128) default NULL,
`legacyExchangeDN` varchar(128) default NULL,
`ext1` varchar(255) default NULL,
`ext2` varchar(255) default NULL,
`ext3` varchar(255) default NULL,
`ext4` varchar(255) default NULL,
`ext5` varchar(255) default NULL,
`ext6` int(11) default NULL,
`ext7` date default NULL,
`distinguishedName` varchar(255) default NULL,
`bEnable_archive_email` char(1) default NULL,
`bEnable_archive_im` char(1) default NULL,
`bEnable_archive_file` char(1) default NULL,
`bEnable_archive_misc` int(11) default NULL,
`bEnable_stub_email` char(1) default NULL,
`mDBOverHardQuotaLimit` varchar(255) default NULL,
`userType` varchar(50) default NULL,
`uniqueID` varchar(150) default NULL,
`physicalDeliveryOfficeName` varchar(255) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `mailuser_i0` (`uniqueID`,`userType`),
KEY `mailuser_i1` (`lastAccessTime`),
KEY `mailuser_i2` (`supervisorId`),
KEY `mailuser_i3` (`gid`),
KEY `mailuser_i4` (`authenticName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8CREATE TABLE `policy_assignment` (
`type` char(16) NOT NULL default '',
`policyid` int(12) default NULL,
`referenceid` int(12) NOT NULL default '0',
PRIMARY KEY (`type`,`referenceid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8这个是那两张表,
请问如何优化~~~
explain SELECT u.id,username FROM mailuser u, policy_assignment pa WHERE u.
show index on mailuser ;
show index on policy_assignment ;贴出以供分析。