select User_list.`UserID`, UserEmail,NickName,Scores,Credit,Level,User_list.JoinDate from `user_info`
left join (select id,`UserID`,JoinDate from `user_basic_info` ) as User_list
on User_list.`UserID` = `user_info`.`UserID`;这个语句有什么问题.查询整整查了5分钟还在查.mysql> EXPLAIN select User_list.`UserID`, UserEmail,NickName,Scores,Credit,Level,User_list.JoinDate from `user_info`
-> left join (select id,`UserID`,JoinDate from `user_basic_info` ) as User_list
-> on User_list.`UserID` = `user_info`.`UserID`;
+----+-------------+-----------------+-------+---------------+--------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+---------------+--------+---------+------+--------+-------------+
| 1 | PRIMARY | user_info | index | NULL | UserID | 619 | NULL | 210441 | Using index |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 210338 | |
| 2 | DERIVED | user_basic_info | index | NULL | ID | 165 | NULL | 210715 | Using index |
+----+-------------+-----------------+-------+---------------+--------+---------+------+--------+-------------+
3 rows in set (0.48 sec)
show index 一下看看
left join `user_basic_info` as User_list
on User_list.`UserID` = `user_info`.`UserID`;
`UserID`上有无索引
ERROR 1146 (42S02): Table 'bluesoleil_en.order_basic_info' doesn't exist
mysql> show index from user_basic_info;
+-----------------+------------+----------+--------------+-------------+-----------+----------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | rdinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+----------+--------------+-------------+-----------+----------
| user_basic_info | 0 | PRIMARY | 1 | ID | A | 210715 | NULL | NULL | | BTREE | |
| user_basic_info | 0 | ID | 1 | ID | A | 210715 | NULL | NULL | | BTREE | |
| user_basic_info | 0 | ID | 2 | JoinDate | A | 210715 | NULL | NULL | YES | BTREE | |
| user_basic_info | 0 | ID | 3 | UserID | A | 210715 | NULL | NULL | | BTREE | |
+-----------------+------------+----------+--------------+-------------+-----------+----------
4 rows in set (0.11 sec)
创建单独的UserID 索引。
left join `user_basic_info` as User_list
FORCE INDEX (index_for_column)
on User_list.`UserID` = `user_info`.`UserID`;
mysql> EXPLAIN select User_list.`UserID`, UserEmail,NickName,Scores,Credit,Level,User_list.JoinDate from `user_info` FORCE INDEX(UserID)
-> left join (select id,`UserID`,JoinDate from `user_basic_info` ) as User_list
-> on User_list.`UserID` = `user_info`.`UserID`;
+----+-------------+-----------------+-------+---------------+--------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+---------------+--------+---------+------+--------+-------------+
| 1 | PRIMARY | user_info | ALL | NULL | NULL | NULL | NULL | 210441 | |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 210338 | |
| 2 | DERIVED | user_basic_info | index | NULL | UserID | 161 | NULL | 210715 | ing index |
+----+-------------+-----------------+-------+---------------+--------+---------+------+--------+-------------+
3 rows in set (0.50 sec)
left join `user_basic_info` as User_list
FORCE INDEX (index_for_column)
on User_list.`UserID` = `user_info`.`UserID
本应该是这样的.
select User_list.`UserID`, UserEmail,NickName,Scores,Credit,Level,User_list.JoinDate from `user_info` FORCE INDEX(UserID)
left join (select id,`UserID`,JoinDate from `user_basic_info` where joinDate = '2011-1-10' ) as User_list
on User_list.`UserID` = `user_info`.`UserID`;
left join (select id,`UserID`,JoinDate from `user_basic_info` where joinDate = '2011-1-10' ) as User_list
on User_list.`UserID` = `user_info`.`UserID`;
from `user_info` left join user_basic_info on user_basic_info.`UserID` = `user_info`.`UserID`;直接改成这个。 楼上 WWWWA(/WWWWB不是已经贴给你了吗?
left join `user_basic_info` as User_list
FORCE INDEX (index_for_column)
on User_list.`UserID` = `user_info`.`UserID
WHERE User_list.joinDate = '2011-1-10'
mysql> EXPLAIN select User_list.`UserID`, UserEmail,NickName,Scores,Credit,Level,User_list.JoinDate from `user_info`
-> left join (select id,`UserID`,JoinDate from `user_basic_info` where joinDate >='0001-01-01' and JoinDate <= '2011-2-20') as User_list
-> on User_list.`UserID` = `user_info`.`UserID`;
+----+-------------+-----------------+-------+---------------+----------+---------+------+--------+--
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+---------------+----------+---------+------+--------+--| 1 | PRIMARY | user_info | ALL | NULL | NULL | NULL | NULL | 210441 |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 210338 |
| 2 | DERIVED | user_basic_info | range | JoinDate | JoinDate | 9 | NULL | 105357 |Using where; Using index |
+----+-------------+-----------------+-------+---------------+----------+---------+------+--------+--
3 rows in set (0.89 sec)
从15:44:18 的顶楼,#14楼, #15楼,#24楼 (16:23:42)能贴一个不会再变化的吗?
select user_info.UserEmail,user_info.NickName,user_info.Scores,user_info.Credit,
user_info.Level,`user_basic_info`.`JoinDate` from `user_info` left join `user_basic_info`
on `user_info`.`UserID` = `user_basic_info`.`UserID`
where joinDate >='0001-01-01' and JoinDate <= '2011-2-20'
mysql> EXPLAIN select user_info.UserEmail,user_info.NickName,user_info.Scores,user_info.Credit,
-> user_info.Level,`user_basic_info`.`JoinDate` from `user_info` left join `user_basic_info`
-> on `user_info`.`UserID` = `user_basic_info`.`UserID`
-> where joinDate >='0001-01-01' and JoinDate <= '2011-2-20';
+----+-------------+-----------------+-------+-----------------+----------+---------+--------------------------------------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-----------------+-------+-----------------+----------+---------+--------------------------------------+--------+--------------------------+
| 1 | SIMPLE | user_basic_info | range | UserID,JoinDate | JoinDate | 9 | NULL | 105357 | Using where; Using index |
| 1 | SIMPLE | user_info | ref | UserID | UserID | 152 | bluesoleil_en.user_basic_info.UserID | 1 | |
+----+-------------+-----------------+-------+-----------------+----------+---------+--------------------------------------+--------+--------------------------+
from `user_info` left join `user_basic_info`
from `user_info` left join `user_basic_info` on `user_info`.`UserID` = `user_basic_info`.`UserID` where joinDate >='0001-01-01' and JoinDate <= '2011-2-20与
left join (select id,`UserID`,JoinDate from `user_basic_info` where joinDate >='0001-01-01' and JoinDate <= '2011-2-20' ) as User_list
on User_list.`UserID` = `user_info`.`UserID`在语义上完全不同,两个查询并不是相等的。第一个SQL语句并不能替代第二个。 你最好想一下,你的需要到底是什么?
from `user_info` left join `user_basic_info` on `user_info`.`UserID` = `user_basic_info`.`UserID` where joinDate >='0001-01-01' and JoinDate <= '2011-2-20创建索引 (UserID,joinDate)
还有你的 show index from tableName
mysql> EXPLAIN select user_info.UserEmail,user_info.NickName,user_info.Scores,user_info.Credit,
-> user_info.Level,`user_basic_info`.`JoinDate` from `user_info` left join `user_basic_info`
-> on `user_info`.`UserID` = `user_basic_info`.`UserID`
-> where joinDate >='0001-01-01' and JoinDate <= '2011-2-20';
+----+-------------+-----------------+-------+---------------+--------+---------+--------------------------------------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+---------------+--------+---------+--------------------------------------+--------+--------------------------+
| 1 | SIMPLE | user_basic_info | index | UserID | UserID | 161 | NULL | 210715 | Using where; Using index |
| 1 | SIMPLE | user_info | ref | UserID | UserID | 152 | bluesoleil_en.user_basic_info.UserID | 1 | |
+----+-------------+-----------------+-------+---------------+--------+---------+--------------------------------------+--------+--------------------------+
2 rows in set (0.00 sec)
mysql> show index from user_info;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | CALL | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| user_info | 0 | PRIMARY | 1 | ID | A | 210441 | NULL | NULL | | BTREE | |
| user_info | 1 | UserID | 1 | UserID | A | 210441 | NULL | NULL | | BTREE | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.08 sec)
mysql> show index from user_basic_info;
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| user_basic_info | 0 | PRIMARY | 1 | ID | A | 210715 | NULL | NULL | | BTREE | |
| user_basic_info | 0 | UserID | 1 | UserID | A | 210715 | NULL | NULL | | BTREE | |
| user_basic_info | 0 | UserID | 2 | JoinDate | A | 210715 | NULL | NULL | YES | BTREE | |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.09 sec)
mysql> EXPLAIN select user_info.UserEmail,user_info.NickName,user_info.Scores,user_info.Credit,
-> user_info.Level,`user_basic_info`.`JoinDate` from `user_info` left join `user_basic_info`
-> on `user_info`.`UserID` = `user_basic_info`.`UserID`
-> where joinDate >='0001-01-01' and JoinDate <= '2011-2-20';
+----+-------------+-----------------+-------+-----------------+--------+---------+--------------------------------------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+-----------------+--------+---------+--------------------------------------+--------+--------------------------+
| 1 | SIMPLE | user_basic_info | index | UserID,JoinDate | UserID | 161 | NULL | 210715 | Using where; Using index |
| 1 | SIMPLE | user_info | ref | UserID | UserID | 152 | bluesoleil_en.user_basic_info.UserID | 1 | |
+----+-------------+-----------------+-------+-----------------+--------+---------+--------------------------------------+--------+--------------------------+
2 rows in set (0.00 sec)
mysql> show index from user_basic_info;
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| user_basic_info | 0 | PRIMARY | 1 | ID | A | 210715 | NULL | NULL | | BTREE | |
| user_basic_info | 0 | UserID | 1 | UserID | A | 210715 | NULL | NULL | | BTREE | |
| user_basic_info | 0 | UserID | 2 | JoinDate | A | 210715 | NULL | NULL | YES | BTREE | |
| user_basic_info | 1 | JoinDate | 1 | JoinDate | A | 6 | NULL | NULL | YES | BTREE | |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.17 sec)
你的问题在这儿!
你表中所有用户的JoinDate几乎都是相同的,210715个用户,一共才有6个不同的JoinDate,这样导致索引失效!