trying to speed up a query - index isn't being used?

Here's my SQL query:

EXPLAIN 
SELECT co.id, ca.duration, ca.type, 
ca.from, ca.to, ca.queue_name, ca.created_at
FROM conferences co
JOIN calls ca ON ca.sid = co.name
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 0, 100;

Here are the schemas for the 3x tables:

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=1851662 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

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,
  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`),
  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=2306330 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`),
  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=3947768 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Here's the EXPLAIN output:

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

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

id: 1
select_type: SIMPLE
table: ca
partitions: NULL
type: ref
possible_keys: calls_sid_unique,sid_type,duration_queue,duration_user_created,sid_user_duration_created
key: sid_user_duration_created
key_len: 137
ref: phone.co.name
rows: 1
filtered: 50.00
Extra: Using index condition

As can be seen in the schema definition p.caller_id has an index on it (conference_participants_caller_id_index) so why is the EXPLAIN showing Using temporary; Using filesort for table p instead of Using index?

Or maybe there are other reasons why this query is slow?

Solution

The query is using the index conference_participants_caller_id_index for table p. You can see this in the EXPLAIN:

table: p
key: conference_participants_caller_id_index

This helps to reduce the examined rows. Only rows matching the condition p.caller_id IN ('group:40') are examined.

You don't see "Using index" because that note in an EXPLAIN report doesn't mean the index is used or not used. It means the index alone is used, and the rest of the row does not need to be read. In this case, the row does need to be read because you reference a column conference_id of that table, which is not in the index.

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html says:

Using index

The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

You may get the "Using index" benefit if you define the index as follows:

KEY `conference_participants_caller_id_index` (`caller_id`, conference_id)

This way both columns referenced in the query are read from the index, and the corresponding rows of the table don't need to be read.

This might reduce overhead enough that it speeds up your query sufficiently for your needs.

Even with this change, you should still expect to see "Using temporary; Using filesort" as the query needs to collect results and sort them before it can apply the LIMIT. This also causes overhead, so your query might still be slower than you need.