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)

解决方案 »

  1.   

    user_basic_info 表中创建 UserID 的索引了没有?
      

  2.   

    楼主的索引信息是怎么样的?
    show index 一下看看 
      

  3.   

    select User_list.`UserID`, a.UserEmail,a.NickName,a.Scores,a.Credit,a.Level,User_list.JoinDate from `user_info` a
    left join `user_basic_info` as User_list 
    on User_list.`UserID` = `user_info`.`UserID`;
    `UserID`上有无索引
      

  4.   

    mysql> show index from order_basic_info;
    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)
      

  5.   

    你的是组合索引 (ID,JoinDate    ,UserID      ) 这个索引对你的查询来说没有什么可以利用的。
    创建单独的UserID      索引。
      

  6.   

    顶,楼主要知道mysql的有个索引是最左索引,你UserID 是最右了,不会被用到
      

  7.   

    单独建立了UserID索引.但是默认不能使用.强制使用不知该放哪.!
      

  8.   

    不需要强制,直接贴你的EXPLAIN
      

  9.   

    select User_list.`UserID`, a.UserEmail,a.NickName,a.Scores,a.Credit,a.Level,User_list.JoinDate from `user_info` a
    left join `user_basic_info` as User_list  
    FORCE INDEX (index_for_column)
    on User_list.`UserID` = `user_info`.`UserID`;
      

  10.   


    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)
      

  11.   

    select User_list.`UserID`, a.UserEmail,a.NickName,a.Scores,a.Credit,a.Level,User_list.JoinDate from `user_info` a
    left join `user_basic_info` as User_list   
    FORCE INDEX (index_for_column)
    on User_list.`UserID` = `user_info`.`UserID
      

  12.   

    我这个语句本来是带了查询条件的.我是想想根据条件查出来然后再组合查询.
    本应该是这样的.
    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`;
      

  13.   

    错了.上面的语句贴错了.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 = '2011-1-10' ) as User_list 
    on User_list.`UserID` = `user_info`.`UserID`;
      

  14.   

    select *
    from `user_info` left join user_basic_info on user_basic_info.`UserID` = `user_info`.`UserID`;直接改成这个。 楼上 WWWWA(/WWWWB不是已经贴给你了吗?
      

  15.   

    select User_list.`UserID`, a.UserEmail,a.NickName,a.Scores,a.Credit,a.Level,User_list.JoinDate from `user_info` a
    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'
      

  16.   

    楼主还是先认真读一下这篇提问的智慧。创建索引( joinDate,UserID)
      

  17.   


    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)
      

  18.   

    每次,你的SQL语句都会变化!!!
    从15:44:18 的顶楼,#14楼, #15楼,#24楼 (16:23:42)能贴一个不会再变化的吗?
      

  19.   


    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 |                          |
    +----+-------------+-----------------+-------+-----------------+----------+---------+--------------------------------------+--------+--------------------------+
      

  20.   

    我先告诉你。 
     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语句并不能替代第二个。 你最好想一下,你的需要到底是什么?
      

  21.   

      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创建索引 (UserID,joinDate)
      

  22.   

    我在那表中建立了两个索引.一个是UserID(UserID,JoinDate),另一个是JoinDate(JoinDate,UserID).它默认使用的是JoinDate.
      

  23.   


    还有你的 show index from tableName
      

  24.   

    我把JoinDate(JoinDate,UserID)删除了.使用的是UserID(UserID,JoinDate).由于数据库数据不大,没有很明显的效果.但是原来需要4秒多点.现在相对来说好了一点.3秒多.!数据不是很多.20多W条记录
      

  25.   

    你的select * from `user_info` 需要多少时间,这部分是没有办法优化的。
      

  26.   


    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)
      

  27.   

    创建 (JoinDate)) 一个独立索引 后,再贴  出来看。
      

  28.   


    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)
      

  29.   

    select * from User_info;/* Result : "53 rows fetched (1.156 sec)" */
      

  30.   

    | user_basic_info |          1 | JoinDate |            1 | JoinDate    | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
    你的问题在这儿!
    你表中所有用户的JoinDate几乎都是相同的,210715个用户,一共才有6个不同的JoinDate,这样导致索引失效!
      

  31.   

    是的。如果你全班的同学都是 1月1号生日,你的查询  where 生日=1月1号  还有什么意义?