Slow Query: SELECT * FROM User U LEFT JOIN UserReportTo rt ON (U.id = RT.supId) LEFT JOIN User S ON ((S.id = RT.subId) AND (S.deletedTime IS NULL) AND (S.purgedTime IS NULL)) LEFT JOIN vRecord R ON (U.id = R.uId) ORDER BY U.lastname ASC, U.id ASC, S.firstname ASC LIMIT 0, 50; EXPLAIN EXTENDED in 10.4.22 => 50 rows in set (1.797 sec) +------+-----------------+-------------------------+--------+------------------------------------------+----------------------------------+---------+------------------------------+-------+----------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-----------------+-------------------------+--------+------------------------------------------+----------------------------------+---------+------------------------------+-------+----------+----------------------------------------------+ | 1 | PRIMARY | U | ALL | NULL | NULL | NULL | NULL | 25174 | 100.00 | Using temporary; Using filesort | | 1 | PRIMARY | RT | ref | supId | supId | 4 | APPLICATION.U.id | 1 | 100.00 | Using index | | 1 | PRIMARY | S | eq_ref | PRIMARY | PRIMARY | 4 | APPLICATION.RT.subId | 1 | 100.00 | Using where | | 1 | PRIMARY | H1 | ref | PRIMARY,fkHistory2User | fkHistory2User | 4 | APPLICATION.U.id | 1 | 100.00 | | | 1 | PRIMARY | HE | eq_ref | PRIMARY | PRIMARY | 4 | APPLICATION.H1.eventId | 1 | 100.00 | Using where | | 1 | PRIMARY | | ref | key0 | key0 | 4 | APPLICATION.U.id | 2 | 100.00 | Using where | | 3 | LATERAL DERIVED | History | ref | fkHistory2User | fkHistory2User | 4 | APPLICATION.U.id | 1 | 100.00 | Using where; Using temporary; Using filesort | | 3 | LATERAL DERIVED | | ref | key0 | key0 | 37 | APPLICATION.History.uId,func | 2 | 100.00 | Using where | | 5 | DERIVED | History | ALL | fkHistory2User | NULL | NULL | NULL | 50377 | 100.00 | Using where; Using temporary; Using filesort | +------+-----------------+-------------------------+--------+------------------------------------------+----------------------------------+---------+------------------------------+-------+----------+----------------------------------------------+ EXPLAIN EXTENDED in 10.11.5 => 50 rows in set (1 hour 5 min 23.912 sec) +------+-----------------+-------------------------+--------+------------------------------------------+----------------------------------+---------+------------------------------+-------+----------+---------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-----------------+-------------------------+--------+------------------------------------------+----------------------------------+---------+------------------------------+-------+----------+---------------------------------------------------------------------+ | 1 | PRIMARY | U | ALL | NULL | NULL | NULL | NULL | 24808 | 100.00 | Using temporary; Using filesort | | 1 | PRIMARY | RT | ref | supId | supId | 4 | APPLICATION.U.id | 1 | 100.00 | Using index | | 1 | PRIMARY | S | eq_ref | PRIMARY | PRIMARY | 4 | APPLICATION.RT.subId | 1 | 100.00 | Using where | | 1 | PRIMARY | H1 | ref | PRIMARY,fkHistory2User | fkHistory2User | 4 | APPLICATION.U.id | 1 | 100.00 | | | 1 | PRIMARY | HE | eq_ref | PRIMARY | PRIMARY | 4 | APPLICATION.H1.eventId | 1 | 100.00 | Using where | | 1 | PRIMARY | | ref | key0 | key0 | 4 | APPLICATION.U.id | 2 | 100.00 | Using where | | 3 | LATERAL DERIVED | History | ref | fkHistory2User | fkHistory2User | 4 | APPLICATION.U.id | 1 | 100.00 | Using index condition; Using where; Using temporary; Using filesort | | 3 | LATERAL DERIVED | | ref | key0 | key0 | 37 | APPLICATION.History.uId,func | 2 | 100.00 | Using where | | 5 | DERIVED | History | ALL | fkHistory2User | NULL | NULL | NULL | 49905 | 100.00 | Using where; Using temporary; Using filesort | +------+-----------------+-------------------------+--------+------------------------------------------+----------------------------------+---------+------------------------------+-------+----------+---------------------------------------------------------------------+ CREATE TABLE `HistoryEvent` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `createdTime` timestamp NOT NULL DEFAULT current_timestamp(), `modifiedTime` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `deletedTime` datetime DEFAULT NULL, `isReadonly` tinyint(1) NOT NULL DEFAULT 0, `isHidden` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `History` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `uId` int(7) NOT NULL, `eDate` date DEFAULT NULL, `jobTitleId` int(13) DEFAULT NULL, `jobTitle` varchar(100) DEFAULT NULL, `joinedDate` date DEFAULT NULL, `eventId` int(6) DEFAULT NULL, `modifiedTime` datetime DEFAULT NULL, `modifiedById` bigint(20) DEFAULT NULL, `modifiedByName` varchar(100) DEFAULT NULL, `isReadonly` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY `fkHistory2User` (`uId`), KEY `fkHistory2HistoryEvent` (`eventId`), KEY `fkHistory2JobTitle` (`jobTitleId`), CONSTRAINT `fkHistory2User` FOREIGN KEY (`uId`) REFERENCES `User` (`id`) ON DELETE CASCADE, CONSTRAINT `fkHistory2HistoryEvent` FOREIGN KEY (`eventId`) REFERENCES `HistoryEvent` (`id`) ON DELETE SET NULL, CONSTRAINT `fkHistory2JobTitle` FOREIGN KEY (`jobTitleId`) REFERENCES `JobTitle` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;