为了使这个查询的效率更高,给其中一个联结列添加索引并重新执行EXPLAIN语句:
| mysql> ALTER TABLE t2 ADD INDEX (i2); mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: i2 key: i2 key_len: 5 ref: sampdb.t1.i1 rows: 10 Extra: Using where; Using index |
| mysql> ALTER TABLE t1 ADD INDEX (i1); mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: index possible_keys: i1 key: i1 key_len: 5 ref: NULL rows: 1000 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: i2 key: i2 key_len: 5 ref: sampdb.t1.i1 rows: 10 Extra: Using where; Using index |
| mysql> ANALYZE TABLE t1, t2; mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: index possible_keys: i1 key: i1 key_len: 5 ref: NULL rows: 1000 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: i2 key: i2 key_len: 5 ref: sampdb.t1.i1 rows: 1 Extra: Using where; Using index |
上一页 [1] [2] [3] [4] [5] [6] 下一页
![]() | 图解MySQL数据库的安装和操作 | 53815 |
![]() | Mysql数据库操作新手入门,手把手的教会你 | 36326 |
![]() | MySQL入门学习(二)入门篇 | 33066 |
![]() | MySQL入门学习(一)安装篇 | 28468 |
![]() | MY SQL常用命令 | 25482 |
![]() | MySQL入门学习(三)学习篇 | 20922 |
![]() | MySQL入门学习(四)学习篇(2) | 12348 |
![]() | MySQL入门学习(六)修改和备份、批处理 | 8931 |