Another Using temporary; Using filesort performance problem
I'm running Aurora on AWS. I've broken down the query to something very small - I can't figure out why the order is forcing a filesort. Can anyone shed some light?
explain SELECT sch_id
FROM schedule_dim s
JOIN production_dim ON pr_production_id = sch_production_did
order by sch_year;
+----+-------------+----------------+-------+--------------------+--------------------+---------+----------------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+--------------------+--------------------+---------+----------------------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | production_dim | index | PRIMARY | pr_name | 130 | NULL | 39926 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | s | ref | sch_production_did | sch_production_did | 4 | public.production_dim.pr_production_id | 47 | NULL |
+----+-------------+----------------+-------+--------------------+--------------------+---------+----------------------------------------+-------+----------------------------------------------+
schedule_dim | CREATE TABLE `schedule_dim` (
`sch_id` int(11) NOT NULL AUTO_INCREMENT,
`sch_showtime_id` int(11) NOT NULL,
`sch_production_did` int(11) NOT NULL,
`sch_studio_did` int(11) DEFAULT NULL,
`sch_distributor_did` int(11) DEFAULT NULL,
`sch_venue_did` int(11) DEFAULT NULL,
`sch_showtime_ts` timestamp NOT NULL,
`sch_showtime_tz_offset` smallint(6) NOT NULL,
`sch_showtime_time_did` int(11) NOT NULL,
`sch_reserved_seat` tinyint(1) DEFAULT NULL,
`sch_dine_in` tinyint(1) DEFAULT NULL,
`sch_auditorium_did` int(11) DEFAULT NULL,
`sch_feature_3d` varchar(20) DEFAULT NULL,
`sch_feature_plf` varchar(20) DEFAULT NULL,
`sch_feature_sound` varchar(20) DEFAULT NULL,
`sch_year` smallint(6) NOT NULL,
`sch_month` smallint(6) NOT NULL,
`sch_day_of_month` smallint(6) NOT NULL,
`sch_day_of_week` smallint(6) NOT NULL,
`sch_day_of_week_text` varchar(10) NOT NULL,
`pr_name` varchar(128) NOT NULL,
PRIMARY KEY (`sch_id`),
UNIQUE KEY `sch_showtime_id` (`sch_showtime_id`),
KEY `sch_production_did` (`sch_production_did`),
KEY `sch_showtime_ts` (`sch_showtime_ts`) USING BTREE,
KEY `sch_showtime_time_did` (`sch_showtime_time_did`) USING BTREE,
KEY `sch_year` (`sch_year`,`sch_month`,`sch_day_of_month`,`sch_day_of_week`,`sch_day_of_week_text`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=31063591 DEFAULT CHARSET=latin1
production_dim | CREATE TABLE `production_dim` (
`pr_production_id` int(11) NOT NULL,
`pr_name` varchar(128) NOT NULL,
`pr_released_time_did` int(11) DEFAULT NULL,
`pr_mpaa_rating` varchar(20) DEFAULT NULL,
`pr_genre` varchar(50) DEFAULT NULL,
`pr_runtime` smallint(6) DEFAULT NULL,
`pr_imdb_id` varchar(50) DEFAULT NULL,
`pr_distributor` varchar(64) DEFAULT NULL,
`pr_has_order` tinyint(1) DEFAULT '0',
PRIMARY KEY (`pr_production_id`),
KEY `pr_name` (`pr_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1