贴出你的 explain select ... 还有所有表的 show index from ...这样别人可以进行分析。
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE b const PRIMARY PRIMARY 32 const 1 Using temporary; Using filesort 1 SIMPLE w ALL NULL NULL NULL NULL 20600 1 SIMPLE r eq_ref PRIMARY PRIMARY 4 lgd.w.rooms 1 Using where 1 SIMPLE c ALL NULL NULL NULL NULL 16936 1 SIMPLE a eq_ref PRIMARY PRIMARY 4 lgd.w.attendance 1 Using where
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE b const PRIMARY PRIMARY 32 const 1 Using temporary; Using filesort 1 SIMPLE w ALL NULL NULL NULL NULL 20600 1 SIMPLE r eq_ref PRIMARY PRIMARY 4 lgd.w.rooms 1 Using where 1 SIMPLE c ALL NULL NULL NULL NULL 16936 1 SIMPLE a eq_ref PRIMARY PRIMARY 4 lgd.w.attendance 1 Using where
explain select r.date, a.code, b.name as bname, r.number, r.master, c.name, c.contact from baseinfo b inner join attendance a on b.code=a.code inner join works w on a.id=w.attendance inner join rooms r on w.rooms=r.id left join contact c on w.id=c.works where r.date between '2010-05-01' and '2010-05-30' and a.code in ('699') order by r.date, convert(r.master using gbk), convert(r.number using gbk), a.code+0id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE b const PRIMARY PRIMARY 32 const 1 Using temporary; Using filesort 1 SIMPLE w ALL NULL NULL NULL NULL 20600 1 SIMPLE r eq_ref PRIMARY PRIMARY 4 lgd.w.rooms 1 Using where 1 SIMPLE c ALL NULL NULL NULL NULL 16936 1 SIMPLE a eq_ref PRIMARY PRIMARY 4 lgd.w.attendance 1 Using whereTable Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment baseinfo 0 PRIMARY 1 code A 644 NULL NULL BTREE attendance 0 PRIMARY 1 id A 25014 NULL NULL BTREE works 0 PRIMARY 1 id A 20600 NULL NULL BTREE rooms 0 PRIMARY 1 id A 5234 NULL NULL BTREE contact 0 PRIMARY 1 id A 16936 NULL NULL BTREE
还有所有表的 show index from ...这样别人可以进行分析。
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE b const PRIMARY PRIMARY 32 const 1 Using temporary; Using filesort
1 SIMPLE w ALL NULL NULL NULL NULL 20600
1 SIMPLE r eq_ref PRIMARY PRIMARY 4 lgd.w.rooms 1 Using where
1 SIMPLE c ALL NULL NULL NULL NULL 16936
1 SIMPLE a eq_ref PRIMARY PRIMARY 4 lgd.w.attendance 1 Using where
1 SIMPLE b const PRIMARY PRIMARY 32 const 1 Using temporary; Using filesort
1 SIMPLE w ALL NULL NULL NULL NULL 20600
1 SIMPLE r eq_ref PRIMARY PRIMARY 4 lgd.w.rooms 1 Using where
1 SIMPLE c ALL NULL NULL NULL NULL 16936
1 SIMPLE a eq_ref PRIMARY PRIMARY 4 lgd.w.attendance 1 Using where
from
baseinfo b inner join attendance a on b.code=a.code
inner join works w on a.id=w.attendance
inner join rooms r on w.rooms=r.id
left join contact c on w.id=c.works
where r.date between '2010-05-01' and '2010-05-30' and a.code in ('699')
order by r.date, convert(r.master using gbk), convert(r.number using gbk), a.code+0id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE b const PRIMARY PRIMARY 32 const 1 Using temporary; Using filesort
1 SIMPLE w ALL NULL NULL NULL NULL 20600
1 SIMPLE r eq_ref PRIMARY PRIMARY 4 lgd.w.rooms 1 Using where
1 SIMPLE c ALL NULL NULL NULL NULL 16936
1 SIMPLE a eq_ref PRIMARY PRIMARY 4 lgd.w.attendance 1 Using whereTable Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
baseinfo 0 PRIMARY 1 code A 644 NULL NULL BTREE
attendance 0 PRIMARY 1 id A 25014 NULL NULL BTREE
works 0 PRIMARY 1 id A 20600 NULL NULL BTREE
rooms 0 PRIMARY 1 id A 5234 NULL NULL BTREE
contact 0 PRIMARY 1 id A 16936 NULL NULL BTREE
另外再建个 attendance (code以)的索引
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| baseinfo | 0 | PRIMARY | 1 | code | A | 644 | NULL | NULL | | BTREE | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------++------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| attendance | 0 | PRIMARY | 1 | id | A | 25014 | NULL | NULL | | BTREE | |
| attendance | 1 | code | 1 | code | A | 595 | NULL | NULL | | BTREE | |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------++-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| works | 0 | PRIMARY | 1 | id | A | 20600 | NULL | NULL | | BTREE | |
| works | 1 | rooms | 1 | rooms | A | 5150 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------++-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| rooms | 0 | PRIMARY | 1 | id | A | 5234 | NULL | NULL | | BTREE | |
| rooms | 1 | date | 1 | date | A | 275 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------++---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| contact | 0 | PRIMARY | 1 | id | A | 16936 | NULL | NULL | | BTREE | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+