关于MYSQL效率的问题,100分诚恳赐教!!

三张表的查询:
SQL code

EXPLAIN SELECT f.fid,t.tid,t.subject FROM bbs_threads t INNER JOIN bbs_forums f ON f.status=1 AND f.fid=t.fid INNER JOIN bbs_threadtags tt ON 

t.tid=tt.tid AND tt.tagname='标签' ORDER BY t.dateline DESC LIMIT 10 \G


*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tt
         type: ref
possible_keys: tagname,tid
          key: tagname
      key_len: 40
          ref: const
         rows: 1366
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: eq_ref
possible_keys: PRIMARY,displayorder,typeid
          key: PRIMARY
      key_len: 3
          ref: bbs.tt.tid
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: f
         type: eq_ref
possible_keys: PRIMARY,forum
          key: PRIMARY
      key_len: 2
          ref: bbs.t.fid
         rows: 1
        Extra: Using where
3 rows in set (0.08 sec)


EXPLAIN SELECT f.fid,t.tid,t.subject FROM bbs_threadtags tt,bbs_threads t,bbs_forums f WHERE f.fid=t.fid AND f.status=1 AND t.tid=tt.tid AND 

tt.tagname='标签' ORDER BY t.dateline DESC LIMIT 10 \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tt
         type: ref
possible_keys: tagname,tid
          key: tagname
      key_len: 40
          ref: const
         rows: 1366
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: eq_ref
possible_keys: PRIMARY,displayorder,typeid
          key: PRIMARY
      key_len: 3
          ref: bbs.tt.tid
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: f
         type: eq_ref
possible_keys: PRIMARY,forum
          key: PRIMARY
      key_len: 2
          ref: bbs.t.fid
         rows: 1
        Extra: Using where
3 rows in set (0.03 sec)


EXPLAIN之后,结果一样。这两个效率是一样的吗?



其中这三张表的索引结构分别如下:

mysql> SHOW INDEX FROM bbs_threads \G
*************************** 1. row ***************************
       Table: bbs_threads
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: tid
   Collation: A
 Cardinality: 2382067
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: bbs_threads
  Non_unique: 1
    Key_name: digest
Seq_in_index: 1
 Column_name: digest
   Collation: A
 Cardinality: 1
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 3. row ***************************
       Table: bbs_threads
  Non_unique: 1
    Key_name: sortid
Seq_in_index: 1
 Column_name: sortid
   Collation: A
 Cardinality: 1
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 4. row ***************************
       Table: bbs_threads
  Non_unique: 1
    Key_name: displayorder
Seq_in_index: 1
 Column_name: fid
   Collation: A
 Cardinality: 346
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 5. row ***************************
       Table: bbs_threads
  Non_unique: 1
    Key_name: displayorder
Seq_in_index: 2
 Column_name: displayorder
   Collation: A
 Cardinality: 346
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 6. row ***************************
       Table: bbs_threads
  Non_unique: 1
    Key_name: displayorder
Seq_in_index: 3
 Column_name: lastpost
   Collation: A
 Cardinality: 297758
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 7. row ***************************
       Table: bbs_threads
  Non_unique: 1
    Key_name: typeid
Seq_in_index: 1
 Column_name: fid
   Collation: A
 Cardinality: 346
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 8. row ***************************
       Table: bbs_threads
  Non_unique: 1
    Key_name: typeid
Seq_in_index: 2
 Column_name: typeid
   Collation: A
 Cardinality: 346
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 9. row ***************************
       Table: bbs_threads
  Non_unique: 1
    Key_name: typeid
Seq_in_index: 3
 Column_name: displayorder
   Collation: A
 Cardinality: 346
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 10. row ***************************
       Table: bbs_threads
  Non_unique: 1
    Key_name: typeid
Seq_in_index: 4
 Column_name: lastpost
   Collation: A
 Cardinality: 297758
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 11. row ***************************
       Table: bbs_threads
  Non_unique: 1
    Key_name: recommends
Seq_in_index: 1
 Column_name: recommends
   Collation: A
 Cardinality: 1
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 12. row ***************************
       Table: bbs_threads
  Non_unique: 1
    Key_name: heats
Seq_in_index: 1
 Column_name: heats
   Collation: A
 Cardinality: 1
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 13. row ***************************
       Table: bbs_threads
  Non_unique: 1
    Key_name: authorid
Seq_in_index: 1
 Column_name: authorid
   Collation: A
 Cardinality: 1
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 14. row ***************************
       Table: bbs_threads
  Non_unique: 1
    Key_name: bbs_threads_index1
Seq_in_index: 1
 Column_name: dateline
   Collation: A
 Cardinality: 183235
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
14 rows in set (0.01 sec)



mysql> SHOW INDEX FROM bbs_threadtags \G
*************************** 1. row ***************************
       Table: bbs_threadtags
  Non_unique: 1
    Key_name: tagname
Seq_in_index: 1
 Column_name: tagname
   Collation: A
 Cardinality: 12291
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: bbs_threadtags
  Non_unique: 1
    Key_name: tid
Seq_in_index: 1
 Column_name: tid
   Collation: A
 Cardinality: 2382066
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
2 rows in set (0.00 sec)


mysql> SHOW INDEX FROM bbs_forums\G
*************************** 1. row ***************************
       Table: bbs_forums
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: fid
   Collation: A
 Cardinality: 387
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: bbs_forums
  Non_unique: 1
    Key_name: forum
Seq_in_index: 1
 Column_name: status
   Collation: A
 Cardinality: 3
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 3. row ***************************
       Table: bbs_forums
  Non_unique: 1
    Key_name: forum
Seq_in_index: 2
 Column_name: type
   Collation: A
 Cardinality: 3
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 4. row ***************************
       Table: bbs_forums
  Non_unique: 1
    Key_name: forum
Seq_in_index: 3
 Column_name: displayorder
   Collation: A
 Cardinality: 96
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 5. row ***************************
       Table: bbs_forums
  Non_unique: 1
    Key_name: fup
Seq_in_index: 1
 Column_name: fup
   Collation: A
 Cardinality: 38
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
5 rows in set (0.00 sec)


非常诚恳的想请教达人们能给出一些优化与修正的方案,提高查询效率。

作者: rczjp   发布时间: 2011-06-08

执行时间哪个少?
EXPLAIN SELECT f.fid,t.tid,t.subject FROM bbs_threads t INNER JOIN bbs_forums f ON f.status=1 AND f.fid=t.fid INNER JOIN bbs_threadtags tt ON 

t.tid=tt.tid AND tt.tagname='标签' ORDER BY t.dateline DESC LIMIT 10 \G

TT是什么表?
你的两个SQL语句都是内连接,只是SQL标准不同写法不同,在索引情况一致的情况下,效率应该没有差别
OR 差别很小

作者: WWWWA   发布时间: 2011-06-08