avoiding temp/filesort for order by on child table

[Matthew Lenz]

I've got a layout similar to:

CREATE TABLE `tbl` (
`ID` int unsigned NOT NULL AUTO_INCREMENT,
`MemberA` int unsigned NOT NULL,
`MemberB` int unsigned NOT NULL,
`status` enum('S1','S2','S3','S4','S5','S6','S7','S8','S9') NOT NULL,
... various other fields
PRIMARY KEY (`ID`),
KEY `MemberA_status` (`MemberA`,`status`),
KEY `MemberB_status` (`MemberB`,`status`),
... various other indexes
);

CREATE TABLE `tblDates` (
`tblID` int unsigned NOT NULL, -- <-- tbl.ID
`type` enum('T1','T2','T3','T4','T5','T6','T7','T8','T9','T10','T11','T12', ... 'T50') NOT NULL,
`MemberA` int unsigned DEFAULT NULL,
`MemberB` int unsigned DEFAULT NULL,
`createdDT` datetime DEFAULT NULL,
`createdDate` date GENERATED ALWAYS AS (cast(`createdDT` as date)) VIRTUAL,
PRIMARY KEY (`tblID`,`type`), -- <-- one date `type` per `tblID`
KEY `MemberA_type_createdDate` (`MemberA`,`type`,`createdDate`),
KEY `MemberB_type_createdDate` (`MemberB`,`type`,`createdDate`),
KEY `tblID_type_createdDate` (`tblID`, `type`, `createdDate`)
);

Essentially `tblDates` is a location to store a variety of dates acquired from various tables associated with `tbl`. Some of those dates are min/max aggregate values from audit type tables and some are dates stored directly on `tbl`. This allows me to quickly identify large numbers of `tbl` records without having to perform aggregates.

NOTE: `tbl`.ID = `tblDates`.`tblID` is implied for all of these various queries discussed.

`MemberA/B` and the two additional keys on `tblDates` (`MemberA/B_type_createdDate`) are so I can quickly identify `tbl` records where `tblDates`.`MemberA/B` = 'specific ID' and `tblDates`.`type` = 'specific type` and `tblDates`.`createdDate` = 'some specific date or range'. This works great and is very fast for multi-millions of records. Index `tblID_type_createdDate` was added in an attempt to avoid the file sort issue discussed below but it's totally spitballing and

The need is to be able to identify `tbl` records where `tbl`.`MemberA/B` = 'specific ID' and `tbl`.`status` = 'specific status' ordered by `tblDates`.`type` = 'specific type` & `tblDates`.`createdDate`.

I'm trying to avoid the temporary/filesort that I'm seeing. Example:

select `tbl`.`ID`, ...., `tblDates`.`createdDate`
from `tbl`
join `tblDates`
on `tblDates`.`tblID` = `tbl`.ID
and `tblDates`.`type` = 'specific type'
where `tbl`.`MemberA/B` = 'specific ID'
and `tbl`.`status` = 'specific status`
order by `tblDates`.`createdDate`

`tblDates` as a concept is a new and still in development so I'm not sure if I need to structure it differently or if I'm just not able to query it in a way that will not result in that temporary/filesort.

I'm using MySQL 8.newest so if there is anything I can leverage I'm game. Any thoughts?