MySQL sorting by max child date

Question

I have a table with nested children. I'm trying to fetch a list of parents sorted by the most recent child, when available, otherwise the parent's created date. My query seemed to work at first, but as I started importing more and more records (@13.6K atm), performance has become a problem.

Version: 10.5.5-MariaDB

Table structure (excluded fields for brevity):

CREATE TABLE `emails` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NULL DEFAULT NULL,
  `_lft` int(10) unsigned NOT NULL DEFAULT 0,
  `_rgt` int(10) unsigned NOT NULL DEFAULT 0,
  `parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `emails__lft__rgt_parent_id_index` (`_lft`,`_rgt`,`parent_id`) USING BTREE,
  KEY `emails__lft__rgt_created_at_index` (`_lft`,`_rgt`,`created_at`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=13484 DEFAULT CHARSET=utf8

Here's the query I'm working with (@21s):

SELECT
    `emails`.`id`,
    (
        SELECT  MAX(`descendants`.`created_at`) AS `created_at`
        FROM `emails` AS `descendants`
        WHERE `descendants`.`_lft` >= `emails`.`_lft`
        AND `descendants`.`_rgt` <= `emails`.`_rgt`
    ) `descendants_created_at`
FROM `emails`
WHERE `parent_id` IS NULL
ORDER BY `descendants_created_at` DESC
LIMIT 25 OFFSET 0;

The _lft and _rgt fields are provided by the lazychaser/laravel-nestedset package and are essentially giving me the descendants for each of the records returned in the main query. It includes the parent as well, so a created_at value is always returned.

Sample output:

| id    | created_at          | descendants_created_at |
|-------|---------------------|------------------------|
| 13483 | 2021-07-22 12:35:55 | 2021-07-22 12:35:55    |
|  8460 | 2021-04-29 12:56:57 | 2021-07-22 12:35:00    |
| 13481 | 2021-07-22 12:33:22 | 2021-07-22 12:33:22    |
|  3514 | 2021-01-16 09:43:42 | 2021-07-22 12:23:28    |
| 13479 | 2021-07-22 11:28:07 | 2021-07-22 11:28:07    |
| 13478 | 2021-07-22 11:27:09 | 2021-07-22 11:27:09    |
| 13407 | 2021-07-21 10:05:41 | 2021-07-22 10:21:14    |
| 13408 | 2021-07-21 10:05:41 | 2021-07-22 10:21:14    |
| 13389 | 2021-07-21 08:17:23 | 2021-07-22 10:21:14    |
| 13303 | 2021-07-19 14:25:38 | 2021-07-22 10:21:14    |

The problem seems to be once I'm doing the actual ordering here:

ORDER BY `descendants_created_at` DESC

My EXPLAIN looks like this:

UPDATE #1 - Using a LEFT JOIN & adding a parent_id key, this query is now @10s which is better, but still not great:

https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=f5442fdfba119cc750c09a19024ccf7c