MySQL Forums
Forum List  »  Performance

Another Using temporary; Using filesort performance problem
Posted by: Drew Tennenbaum
Date: December 07, 2015 06:37PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Another Using temporary; Using filesort performance problem
2086
December 07, 2015 06:37PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.