Mysql – Find employees latest activity is slow when adding ORDER BY

I am working on a legacy system in Laravel and I am trying to pull the latest action of some specific types of actions an employee has done.

Performance is good when I don't add ORDER BY. When adding it the query will go from something like 130 ms to 18 seconds. There are about 1.5 million rows in the actions table. There are 2.5 million rows in the pivot table.

How do I fix the performance problem?

I have tried to isolate the problem by cutting out all the other parts of the query so it is more readable for you:

SELECT
    employees.id,
    (
        SELECT actions.date)
        FROM pivot
        JOIN actions
        ON pivot.actions_id = actions.id
        WHERE employees.id = pivot.employee_id
        AND actions.type = 'meeting'
        LIMIT 1
        ORDER BY actions.id DESC
    ) AS latest_action
FROM employees
ORDER BY latest_action DESC

I tried using LEFT JOIN and MAX() instead but it didn't seem to solve my problem.

I just added a subquery because it was the original query is already very complex. But if you have an alternative suggestion I am all ears.

Result of EXPLAIN:

id  select_type         table       partitions  type    possible_keys                       key                 key_len ref                     rows    filtered    Extra
1   PRIMARY             employees   NULL        ALL     NULL                                NULL                NULL    NULL                    15217   10          Using where
2   DEPENDENT SUBQUERY  pivot       NULL        ref     actions_type_index,pivot_type_index pivot_type_index    4       dev.employees.id        104     11.11       Using index condition
2   DEPENDENT SUBQUERY  actions     NULL        eq_ref  PRIMARY,Logs                        PRIMARY             4       dev.pivot.actions_id    1       6.68        Using where

Here is the indexes. The index employee_type I don't think is important for my specific query, but maybe it should be re-worked?

# pivot table
KEY `actions_type_index` (`actions_id`,`employee_type`),
KEY `pivot_type_index` (`employee_id`,`employee_type`)

# actions table
KEY `Logs` (`type`,`id`,`is_log`)
# I tried to add `date` index to `actions` table but the problem remains.
KEY `date_index` (`date`)

UPDATE

Tables

CREATE TABLE `pivot` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order` int(11) DEFAULT NULL,
  `actions_id` int(11) unsigned NOT NULL,
  `employee_id` int(11) unsigned NOT NULL,
  `employee_type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `actions_type_index` (`actions_id`,`employee_type`),
  KEY `employee_type_index` (`employee_id`,`employee_type`)
) ENGINE=InnoDB AUTO_INCREMENT=2960893 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `actions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `is_log` tinyint(1) NOT NULL DEFAULT '0',
  `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `text` text COLLATE utf8_unicode_ci,
  `data` text COLLATE utf8_unicode_ci,
  `file` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `user_group` tinyint(1) NOT NULL DEFAULT '0',
  `user_type` int(11) NOT NULL DEFAULT '0',
  `endDate` datetime DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `Logs` (`type`,`id`,`is_log`),
  KEY `date_index` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=1368630 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `employees` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `employees_id_unique` (`id`),
  KEY `employees_created_by_user_index` (`created_by_user`),
  KEY `employees_updated_by_user_index` (`updated_by_user`)
) ENGINE=InnoDB AUTO_INCREMENT=22012 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;