看一个关联查询索引问题

abbish

1. 
   
2. 
   
3. SELECT tt.s_id topic_id,
   
4.         tt.s_tags_id tags_id,
   
5.         tt.s_title title,
   
6.         tt.s_member_id member_id,
   
7.         tt.s_reply_amount reply_amount,
   
8.         tt.s_read_amount read_amount,
   
9.         tt.s_last_update last_update,
   
10.         tt.s_add_time add_time,
    
11.         tt.s_add_ip add_ip,
    
12.         mi.s_face_image member_face_image,
    
13.         mm.s_nick_name member_nick_name
    
14. FROM ds_w_tags_topic tt,
    
15.         ds_w_member mm,
    
16.         ds_w_member_information mi
    
17. WHERE  tt.s_tags_id=1
    
18.         AND tt.s_status=1
    
19.         AND tt.s_member_id=mm.s_id
    
20.         AND mm.s_id=mi.s_member_id
    
21. ORDER BY tt.s_last_update DESC
    
22. 
    

复制代码

上面是执行的SQL语句，目的是从ds_w_tags_topic表取出帖子和帖子作者的一些信息，作者的信息分两张表存储ds_w_member和ds_w_member_information

现在，在ds_w_tags_topic中对s_member_id做索引，ds_w_member的主键s_id(其他表的s_member_id)索引，ds_w_member_information的s_member_id索引，
ds_w_tags_topic中再对s_tags_id，s_status，s_last_update做了一个组合索引

下面是EXPLAIN对SQL运行分析的结果

1. 
   
2. id        select_type        table  type        possible_keys                key                key_len                ref                rows                Extra  
   
3. 1        SIMPLE                tt        ref        s_member_id,s_tags_id        s_tags_id        5                const,const        2                Using temporary; Using filesort
   
4. 1        SIMPLE                mm        ALL        PRIMARY                        NULL                NULL                NULL                2                Using where
   
5. 1        SIMPLE                mi        ALL        s_member_id                NULL                NULL                NULL                2                Using where
   

复制代码

结果显示，只有tt表使用到了索引，而其他两张表均没有使用索引而采用全表扫描，这里想请问针对mm和mi两张表索引的创建方法，谢谢

cooljia

建议楼主把你的几张表table structure export出来, 作为附件.

cooljia

你的tt.s_member_id, mm.s_id, mi.s_member_id会不会不一样长?

abbish

原帖由 cooljia 于 2007-6-21 17:00 发表
建议楼主把你的几张表table structure export出来, 作为附件.

表结构在这里

1. 
   
2. --
   
3. -- 表的结构 `ds_w_member`
   
4. --
   
5. 
   
6. CREATE TABLE ds_w_member (
   
7.   s_id int(10) unsigned NOT NULL auto_increment,
   
8.   s_passport varchar(255) NOT NULL,
   
9.   s_password char(32) NOT NULL,
   
10.   s_security_question varchar(255) NOT NULL,
    
11.   s_security_answer varchar(255) NOT NULL,
    
12.   s_nick_name varchar(255) NOT NULL,
    
13.   s_status tinyint(3) unsigned NOT NULL,
    
14.   s_passport_valid tinyint(3) unsigned NOT NULL,
    
15.   s_identifying_code varchar(255) NOT NULL,
    
16.   s_add_time int(10) unsigned NOT NULL,
    
17.   PRIMARY KEY  (s_id),
    
18.   KEY s_passport (s_passport)
    
19. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
20. 
    
21. -- --------------------------------------------------------
    
22. 
    
23. --
    
24. -- 表的结构 `ds_w_member_information`
    
25. --
    
26. 
    
27. CREATE TABLE ds_w_member_information (
    
28.   s_id int(10) unsigned NOT NULL auto_increment,
    
29.   s_member_id int(10) unsigned NOT NULL,
    
30.   s_face_image varchar(255) NOT NULL,
    
31.   s_first_name varchar(255) NOT NULL,
    
32.   s_last_name varchar(255) NOT NULL,
    
33.   s_province int(10) unsigned NOT NULL,
    
34.   s_city int(10) unsigned NOT NULL,
    
35.   s_gender tinyint(3) unsigned NOT NULL,
    
36.   s_birthday char(8) NOT NULL,
    
37.   s_email varchar(255) NOT NULL,
    
38.   s_phone_number varchar(255) NOT NULL,
    
39.   s_mobile_number varchar(255) NOT NULL,
    
40.   s_address_1 varchar(255) NOT NULL,
    
41.   s_address_2 varchar(255) NOT NULL,
    
42.   s_postcode varchar(255) NOT NULL,
    
43.   s_connubiality tinyint(3) unsigned NOT NULL,
    
44.   s_blood_group tinyint(3) unsigned NOT NULL,
    
45.   s_interest_brand varchar(255) NOT NULL,
    
46.   s_interest_style varchar(255) NOT NULL,
    
47.   s_interest_book varchar(255) NOT NULL,
    
48.   s_interest_music varchar(255) NOT NULL,
    
49.   s_interest_movie varchar(255) NOT NULL,
    
50.   s_certificate_type tinyint(3) unsigned NOT NULL,
    
51.   s_certificate_code varchar(255) NOT NULL,
    
52.   PRIMARY KEY  (s_id),
    
53.   KEY s_member_id (s_member_id)
    
54. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
55. 
    
56. -- --------------------------------------------------------
    
57. 
    
58. --
    
59. -- 表的结构 `ds_w_tags_topic`
    
60. --
    
61. 
    
62. CREATE TABLE ds_w_tags_topic (
    
63.   s_id int(10) unsigned NOT NULL auto_increment,
    
64.   s_tags_id int(10) unsigned NOT NULL,
    
65.   s_title varchar(255) NOT NULL,
    
66.   s_content longtext NOT NULL,
    
67.   s_image_url varchar(255) NOT NULL,
    
68.   s_member_id int(10) unsigned NOT NULL,
    
69.   s_reply_amount int(10) unsigned NOT NULL,
    
70.   s_read_amount int(10) unsigned NOT NULL,
    
71.   s_status tinyint(3) unsigned NOT NULL,
    
72.   s_reply_enabled tinyint(3) unsigned NOT NULL,
    
73.   s_last_update int(10) unsigned NOT NULL,
    
74.   s_add_ip varchar(15) NOT NULL,
    
75.   s_add_time int(10) unsigned NOT NULL,
    
76.   PRIMARY KEY  (s_id),
    
77.   KEY s_member_id (s_member_id,s_add_time),
    
78.   KEY s_status (s_status)
    
79. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
80. 
    

复制代码

不会存在不一样长的情况~

cooljia

没有数据的情况, 再看看手册...

cooljia

加上alter table ds_w_tags_topic add index i_tsl(s_tags_id, s_status, s_last_update);
于是order by也可以用索引鸟...~

abbish

看看这句

1. 
   
2. SELECT wm.s_id message_id,
   
3.                                                 wm.s_member_id member_id,
   
4.                                                 wm.s_from_member_id from_member_id,
   
5.                                                 mm.s_nick_name from_member_nick_name,
   
6.                                                 wm.s_title title,
   
7.                                                 wm.s_is_read is_read,
   
8.                                                 wm.s_is_reply is_reply,
   
9.                                                 wm.s_add_time add_time
   
10.                                         FROM ds_w_wardrobe_message wm,
    
11.                                                 ds_w_member mm
    
12.                                         WHERE wm.s_member_id=1
    
13.                                                 AND mm.s_id=wm.s_from_member_id
    
14.                                         ORDER BY wm.s_is_read ASC, wm.s_add_time DESC
    

复制代码

1. 
   
2. --
   
3. -- 表的结构 `ds_w_member`
   
4. --
   
5. 
   
6. CREATE TABLE ds_w_member (
   
7.   s_id int(10) unsigned NOT NULL auto_increment,
   
8.   s_passport varchar(255) NOT NULL,
   
9.   s_password char(32) NOT NULL,
   
10.   s_security_question varchar(255) NOT NULL,
    
11.   s_security_answer varchar(255) NOT NULL,
    
12.   s_nick_name varchar(255) NOT NULL,
    
13.   s_status tinyint(3) unsigned NOT NULL,
    
14.   s_passport_valid tinyint(3) unsigned NOT NULL,
    
15.   s_identifying_code varchar(255) NOT NULL,
    
16.   s_add_time int(10) unsigned NOT NULL,
    
17.   PRIMARY KEY  (s_id),
    
18.   KEY s_passport (s_passport)
    
19. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
20. 
    
21. --
    
22. -- 表的结构 `ds_w_wardrobe_message`
    
23. --
    
24. 
    
25. CREATE TABLE ds_w_wardrobe_message (
    
26.   s_id int(10) unsigned NOT NULL auto_increment,
    
27.   s_member_id int(10) unsigned NOT NULL,
    
28.   s_from_member_id int(10) unsigned NOT NULL,
    
29.   s_title varchar(255) NOT NULL,
    
30.   s_message mediumtext NOT NULL,
    
31.   s_is_read tinyint(3) unsigned NOT NULL,
    
32.   s_is_reply tinyint(3) unsigned NOT NULL,
    
33.   s_add_time int(10) unsigned NOT NULL,
    
34.   PRIMARY KEY  (s_id),
    
35.   KEY s_from_member_id (s_from_member_id),
    
36.   KEY s_member_id (s_member_id)
    
37. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

复制代码

结果看附件，也是执行的全表扫描，不用主键

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra  
1 SIMPLE mm ALL PRIMARY NULL NULL NULL 2 Using temporary; Using filesort
1 SIMPLE wm ref s_from_member_id,s_member_id s_from_member_id 4 7looks.mm.s_id 1 Using where

没有整理，凑合看一下，mm表的possible_keys是PRIMARY但是 key是NULL

abbish

原帖由 cooljia 于 2007-6-21 17:57 发表
没有数据的情况, 再看看手册...

我的结果怎么不合你的一样呢？

MySQL版本问题么？ 我用的是 5.0.15