表连接中，mysql只能用一个表的连接字段的索引！！！userid是连接字段，在每个表中都包含于索引中。
请问这是为什么？有什么解决办法吗？
mysql> explain
-> select a.userid,a.nickname,a.sex,a.username from linfo_user a,linfo_userDynamic b where a.userid=b.userid and b.longitude is not null and a.regDate > '2010-11-10 16:02:00'and a.regDateexplain
-> select a.userid,a.nickname,a.sex,a.username from linfo_user a,linfo_userDynamic b where a.userid=b.userid and b.longitude is not null and a.regDate > '2010-11-10 16:02:00'and a.regDateshow create table linfo_user\G
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:213
Current database: mmc
*************************** 1. row ***************************
Table: linfo_user
Create Table: CREATE TABLE `linfo_user` (
`userid` bigint(11) NOT NULL auto_increment,
`username` varchar(20) default NULL,
`password` varchar(20) default NULL,
`nickname` varchar(20) default NULL,
`email` varchar(50) default NULL,
`mobilephone` varchar(20) default NULL,
`imsi` varchar(50) default NULL,
`sex` int(1) default '1',
`age` int(3) default '0',
`marry` int(1) default '0',
`description` varchar(200) character set utf8 collate utf8_bin default NULL,
`isopenloc` int(1) default NULL,
`avatar` varchar(50) default NULL,
`regdate` datetime default NULL,
`clientkey` varchar(40) character set utf8 collate utf8_bin default NULL,
`language` varchar(2) character set utf8 collate utf8_bin default NULL,
`weathercity` varchar(30) default NULL,
`constellation` int(2) default '0',
`clientversion` varchar(50) default NULL,
`sign` varchar(150) default NULL,
`livecity` varchar(30) default NULL,
`ua` varchar(20) default NULL,
`usertype` int(1) default '0',
`ranking` bigint(11) default '0',
`rankinglevel` int(2) default '0',
PRIMARY KEY(`userid`),
UNIQUE KEY `index_imsi` (`imsi`),
KEY `index_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=20200481 DEFAULT CHARSET=utf8
1 row in set (0.03 sec)
mysql> show create table linfo_userDynamic\G
*************************** 1. row ***************************
Table: linfo_userDynamic
Create Table: CREATE TABLE `linfo_userdynamic` (
`userdynamicid` bigint(11) NOT NULL auto_increment,
`userid` bigint(11) NOT NULL,
`isopenloc` int(1) default '0',
`longitude` decimal(15,9) default NULL,
`latitude` decimal(15,9) default NULL,
`locdate` datetime default NULL,
`location` varchar(150) default NULL,
`ranking` bigint(11) default '0',
`onlinedate` datetime default NULL,
`logincount` bigint(11) default '0',
`lastestMsgTimeLong` bigint(15) default NULL,
PRIMARY KEY(`userdynamicid`),
UNIQUE KEY `index_userid` (`userid`),
KEY `longi` (`longitude`),
KEY `onlinedatedesc` (`onlinedate`),
CONSTRAINT `userdynamicuser_fk` FOREIGN KEY (`userid`) REFERENCES `linfo_user` (`userid`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2329055 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
|
