query is super slow until either i remove one particular JOIN or the ORDER BY

[Thomas Anderson]

Here's my query:

SELECT *
FROM calls ca
JOIN conferences co ON ca.conference_id = co.id
JOIN conference_participants p ON co.id = p.conference_id AND p.caller_id IN ('group:40')
WHERE ca.duration >= 60
ORDER BY ca.created_at DESC
LIMIT 100;

If I remove the "JOIN conference_participants p" bit or if I remove the "ORDER BY" bit the query is plenty fast but with both of those in there the query slows to crawl and I don't have a clue how to fix it.

Here's the EXPLAIN:

id: 1
select_type: SIMPLE
table: p
partitions: NULL
type: ref
possible_keys: conference_participants_conference_id_index,conference_participants_caller_id_index,idx_conference_id_caller_id,conference_participants_caller_id_index2
key: conference_participants_caller_id_index
key_len: 130
ref: const
rows: 49600
filtered: 100.00
Extra: Using temporary; Using filesort

id: 1
select_type: SIMPLE
table: ca
partitions: NULL
type: ref
possible_keys: calls_conference_id_index,duration_queue,duration_user_created,idx_duration_created_at
key: calls_conference_id_index
key_len: 9
ref: mediphone.p.conference_id
rows: 1
filtered: 50.00
Extra: Using where

id: 1
select_type: SIMPLE
table: co
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: mediphone.p.conference_id
rows: 1
filtered: 100.00
Extra: NULL

Here are the CREATE TABLES:

CREATE TABLE `calls` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`organization_id` int(10) unsigned NOT NULL,
`conference_id` bigint(20) unsigned DEFAULT NULL,
`user_id` bigint(20) unsigned DEFAULT NULL,
`sid` char(34) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`original_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`queue_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`from` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`to` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`direction` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`priority` int(10) unsigned DEFAULT NULL,
`age` int(10) unsigned DEFAULT NULL,
`duration` int(10) unsigned NOT NULL DEFAULT '0',
`user_hold_duration` int(10) unsigned NOT NULL DEFAULT '0',
`total_hold_duration` int(10) unsigned NOT NULL DEFAULT '0',
`status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`reason` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`reverseCreated` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `calls_sid_unique` (`sid`),
KEY `calls_created_at_index` (`created_at`),
KEY `calls_organization_id_index` (`organization_id`),
KEY `calls_conference_id_index` (`conference_id`),
KEY `calls_user_id_index` (`user_id`),
KEY `calls_original_type_index` (`original_type`),
KEY `calls_type_index` (`type`),
KEY `calls_queue_name_index` (`queue_name`),
KEY `calls_from_index` (`from`),
KEY `calls_to_index` (`to`),
KEY `calls_direction_index` (`direction`),
KEY `calls_priority_index` (`priority`),
KEY `calls_status_index` (`status`),
KEY `calls_reason_index` (`reason`),
KEY `type` (`type`),
KEY `type_sid` (`type`,`sid`),
KEY `sid_type` (`sid`,`type`),
KEY `duration_queue` (`duration`,`queue_name`),
KEY `queue_duration` (`queue_name`,`duration`),
KEY `duration_user_created` (`duration`,`user_id`,`created_at`),
KEY `user_duration_created` (`user_id`,`duration`,`created_at`),
KEY `created_user_duration` (`created_at`,`user_id`,`duration`),
KEY `created_duration_user` (`created_at`,`duration`,`user_id`),
KEY `sid_user_duration_created` (`sid`,`user_id`,`duration`,`created_at`),
KEY `created_user` (`created_at`,`user_id`),
KEY `user_created` (`user_id`,`created_at`),
KEY `search_test` (`created_at`,`type`,`queue_name`),
KEY `search_test2` (`created_at`,`type`,`queue_name`,`from`),
KEY `search_test3` (`created_at`,`type`),
KEY `idx_duration_created_at` (`duration`,`created_at`),
KEY `reverseCreated` (`reverseCreated`),
CONSTRAINT `calls_conference_id_foreign` FOREIGN KEY (`conference_id`) REFERENCES `conferences` (`id`),
CONSTRAINT `calls_organization_id_foreign` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`),
CONSTRAINT `calls_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2318098 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `conferences` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`organization_id` int(10) unsigned DEFAULT NULL,
`sid` char(34) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`name` char(34) COLLATE utf8mb4_unicode_ci NOT NULL,
`owner_user_id` bigint(20) unsigned DEFAULT NULL,
`duration` int(10) unsigned DEFAULT NULL,
`events` int(10) unsigned NOT NULL DEFAULT '1',
`ending_participant_id` bigint(20) unsigned DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `conferences_name_unique` (`name`),
UNIQUE KEY `conferences_sid_unique` (`sid`),
UNIQUE KEY `conferences_ending_participant_id_unique` (`ending_participant_id`),
KEY `conferences_organization_id_index` (`organization_id`),
KEY `conferences_owner_user_id_index` (`owner_user_id`),
CONSTRAINT `conferences_ending_participant_id_foreign` FOREIGN KEY (`ending_participant_id`) REFERENCES `conference_participants` (`id`),
CONSTRAINT `conferences_organization_id_foreign` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`),
CONSTRAINT `conferences_owner_user_id_foreign` FOREIGN KEY (`owner_user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1861837 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `conference_participants` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`conference_id` bigint(20) unsigned NOT NULL,
`call_leg_id` bigint(20) unsigned DEFAULT NULL,
`user_id` bigint(20) unsigned DEFAULT NULL,
`sid` char(34) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`task_sid` char(34) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`caller_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
`has_joined_conference` tinyint(1) NOT NULL DEFAULT '1',
`is_hidden` tinyint(1) NOT NULL DEFAULT '0',
`is_muted` tinyint(1) NOT NULL DEFAULT '0',
`is_on_hold` tinyint(1) NOT NULL DEFAULT '0',
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `conference_participants_call_leg_id_unique` (`call_leg_id`),
UNIQUE KEY `conference_participants_sid_unique` (`sid`),
KEY `conference_participants_conference_id_index` (`conference_id`),
KEY `conference_participants_user_id_index` (`user_id`),
KEY `conference_participants_caller_id_index` (`caller_id`),
KEY `conference_participants_has_joined_conference_index` (`has_joined_conference`),
KEY `conference_participants_is_hidden_index` (`is_hidden`),
KEY `conference_participants_is_muted_index` (`is_muted`),
KEY `conference_participants_is_on_hold_index` (`is_on_hold`),
KEY `conference_participants_task_sid_index` (`task_sid`),
KEY `idx_conference_id_caller_id` (`conference_id`,`caller_id`),
KEY `conference_participants_caller_id_index2` (`caller_id`,`conference_id`),
CONSTRAINT `conference_participants_call_leg_id_foreign` FOREIGN KEY (`call_leg_id`) REFERENCES `call_legs` (`id`),
CONSTRAINT `conference_participants_conference_id_foreign` FOREIGN KEY (`conference_id`) REFERENCES `conferences` (`id`),
CONSTRAINT `conference_participants_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3969413 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci