|
mysql>EXPLAIN SELECT t.* FROM cdb_posts p, cdb_threads t WHERE
t.fid IN ('37', '45', '4', '6', '17', '41', '28', '32', '31', '1', '42')
AND p.tid=t.tid AND p.author LIKE 'JoansWin'
GROUP BY t.tid ORDER BY lastpost DESC LIMIT 0, 80;
|
|
+-----------+------------+----------+--------------+-------------+-----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
+-----------+------------+----------+--------------+-------------+-----------+-------------+
| 1 | SIMPLE | t | range | PRIMARY,fid | fid | 2 | NULL | 66160 | Using where;
Using temporary; Using filesort |
| 1 | SIMPLE | p | ref | tid | tid | 3 | Forum.t.tid | 10 | Using where
| +----+-------------+-------+-------+---------------+------+---------+-------------+-------+
---------
|
只用到了 t.fid 和 p.tid,而 p.author 则没有索引可用,总共需要扫描
66160*10 = 661600 次索引,够夸张吧 :(
再分析 cdb_threads 和 cdb_posts 的索引情况:
|
mysql>show index from cdb_posts;
|
|
+-----------+------------+----------+--------------+-------------+-----------+----------
---+----------+--------+------+--+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part |
Packed | Null | Index_type | Comment | +-----------+------------+----------+--------------+----
---------+-----------+-------------+----------+--------+------+--+
| cdb_posts | 0 | PRIMARY | 1 | pid | A | 680114 | NULL | NULL |
| BTREE | |
| cdb_posts | 1 | fid | 1 | fid | A | 10 | NULL | NULL |
| BTREE | |
| cdb_posts | 1 | tid | 1 | tid | A | 68011 | NULL | NULL |
| BTREE | |
| cdb_posts | 1 | tid | 2 | dateline | A | 680114 | NULL | NULL |
| BTREE | |
| cdb_posts | 1 | dateline | 1 | dateline | A | 680114 | NULL | NULL |
| BTREE | |
+-----------+------------+----------+--------------+-------------+-----------+---
|
以及
|
mysql>show index from cdb_threads;
|
|
+-----------+------------+----------+--------------+-------------+-----------+-------------+
----------+--------+------+-----+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part |
Packed | Null | Index_type | Comment | +-----------+------------+----------+--------------+-----
--------+-----------+-------------+----------+--------+------+-----+
| cdb_threads | 0 | PRIMARY | 1 | tid | A | 68480 | NULL | NULL |
| BTREE | |
| cdb_threads | 1 | lastpost | 1 | topped | A | 4 | NULL | NULL |
| BTREE | |
| cdb_threads | 1 | lastpost | 2 | lastpost | A | 68480 | NULL | NULL |
| BTREE | |
| cdb_threads | 1 | lastpost | 3 | fid | A | 68480 | NULL | NULL |
| BTREE | |
| cdb_threads | 1 | replies | 1 | replies | A | 233 | NULL | NULL |
| BTREE | |
| cdb_threads | 1 | dateline | 1 | dateline | A | 68480 | NULL | NULL |
| BTREE | |
| cdb_threads | 1 | fid | 1 | fid | A | 10 | NULL | NULL |
| BTREE | |
| cdb_threads | 1 | enablehot | 1 | enablehot | A | 2 | NULL | NULL |
| BTREE | | +-------------+------------+-----------+--------------+-------------+------
|
