- 论坛徽章:
- 0
|
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`t1_2id` int(11) DEFAULT NULL,
`date_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `test` (`t1_2id`,`date_time`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
CREATE TABLE `t2` (
`2id` int(11) NOT NULL AUTO_INCREMENT,
`2name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`2id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
这里有两个表,我想问下left join的索引执行情况
访问的语句类似这样
select `t1`.`name`,`t2`.`2name` from t1 left outer join `t2` on `t1`.`t1_2id`=`t2`.`2id` order by `t1`.`date_time` desc
explain 的执行情况
explain select `t1`.`name`,`t2`.`2name` from t1 left outer join `t2` on `t1`.`t1_2id`=`t2`.`2id` order by `t1`.`date_time` desc ;
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+----------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | Using filesort |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | yingyuan_test.t1.t1_2id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+----------------+
2 rows in set (0.00 sec)
我想问下,为什么会有using filesort ,我尝试过使用force index ,按照官方提供的语法,却是错误的 |
|