Re: Another Using temporary; Using filesort performance problem

[Drew Tennenbaum]

Discovered a straight_join which fixes the problem. I still don't understand why it needed to sort my production_dim table. I would have thought MySQL would just find the pr_production_id in my index and move on with the rest of the rows in schedule_dim.



mysql> explain SELECT sch_id
FROM schedule_dim s
STRAIGHT_JOIN production_dim ON pr_production_id = sch_production_did
group by sch_id order by sch_id;
+----+-------------+----------------+--------+--------------------+---------+---------+-----------------------------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+--------+--------------------+---------+---------+-----------------------------+----------+-------------+
| 1 | SIMPLE | s | index | sch_production_did | PRIMARY | 4 | NULL | 28389421 | NULL |
| 1 | SIMPLE | production_dim | eq_ref | PRIMARY | PRIMARY | 4 | public.s.sch_production_did | 1 | Using index |
+----+-------------+----------------+--------+--------------------+---------+---------+-----------------------------+----------+-------------+