为什么
lean_users
显示
NULL
在
ref
专栏?这将导致我的查询稍后使用临时表和文件排序(当我添加了更多联接时)…
14:45:21 (60) > EXPLAIN select * from users u inner join lean_users lu on u.id = lu.user_id;
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+-------------+
| 1 | SIMPLE | lu | index | PRIMARY | PRIMARY | 4 | NULL | 358 | Using index |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | nwa.lu.user_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+-------------+
用户表
14:45:24 (61) > show create table users;
+-------+-----------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`email` varchar(255) default NULL,
`first_name` varchar(50) NOT NULL,
`last_name` varchar(50) NOT NULL,
`address1` varchar(255) NOT NULL,
`address2` varchar(255) default NULL,
`city` varchar(25) NOT NULL,
`state` mediumint(9) default NULL,
`zip` varchar(10) NOT NULL,
`phone` varchar(20) default NULL,
`country` smallint(6) NOT NULL,
`username` varchar(10) NOT NULL,
`password` varchar(50) default NULL,
`cdate` datetime NOT NULL,
`last_used` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`level` varchar(25) default 'user',
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=38076 DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------+
精益用户表
14:45:40 (62) > show create table lean_users;
+-------------+-----------------------------------------------------------------------------+
| Table | Create Table |
+-------------+-----------------------------------------------------------------------------+
| lean_users | CREATE TABLE `lean_users` (
`user_id` int(11) NOT NULL,
PRIMARY KEY (`user_id`),
CONSTRAINT `lean_users_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+-----------------------------------------------------------------------------+
