Here’s my query:

SELECT *
FROM conferences co
JOIN calls ca ON ca.conference_id = co.id       
JOIN conference_participants p ON co.id = p.conference_id AND p.caller_id = 40
WHERE ca.duration >= 60
ORDER BY ca.created_at desc
LIMIT 0, 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: index
possible_keys: PRIMARY
key: PRIMARY
ref: NULL
rows: 54474
filtered: 10.00
Extra: Using where; Using index; 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 index condition; 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: Using where

Here are my (simplified) tables:

CREATE TABLE `conference_participants` (
  `conference_id` bigint(20) NOT NULL,
  `caller_id` int(11) NOT NULL,
  PRIMARY KEY (`conference_id`,`caller_id`)
) ENGINE=InnoDB;

CREATE TABLE `calls` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `conference_id` bigint(20) unsigned DEFAULT NULL,
  `duration` int(10) unsigned NOT NULL DEFAULT '0',
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `calls_created_at_index` (`created_at`),
  KEY `calls_user_id_index` (`user_id`),
  KEY `created_user` (`created_at`,`user_id`),
  KEY `user_created` (`user_id`,`created_at`),
  KEY `calls_conference_id_index` (`conference_id`),
  KEY `idx_duration_created_at` (`duration`,`created_at`),
  KEY `duration_queue` (`duration`,`queue_name`),
  KEY `duration_user_created` (`duration`,`user_id`,`created_at`),
  CONSTRAINT `calls_conference_id_foreign` FOREIGN KEY (`conference_id`) REFERENCES `conferences` (`id`),
  CONSTRAINT `calls_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB;

CREATE TABLE `conferences` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;