question originates from this article: MySQL answering questions using filesort VS using temporary
in the article:
mysql> explain select * from t1 force index(id), t2 where t1.id=1 and t1.col1 = t2.col2 order by t2.col1 ;
+----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------+
| 1 | SIMPLE | t1 | ref | id | id | 5 | const | 1 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------+Article conclusion:
The case2: order by predicate is done on the first table T1, so only needs to use filesort on the T1 table, and then sort the result set join T2 table.
case 3: the field of order by is on the T2 table, so you need to save the result of the T1 strong T2 table join to the temporary table , then filesort the temporary table, and finally output the result.
my question:
Why does case2 case3 need to do join, and why does case3 need to save to the temporary table, but case2 doesn"t?
ask the boss to solve the problem.