mysql - mysql 在加入时忽略 order by 索引

Question

CREATE TABLE `call_session` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `contact_id` bigint(20) unsigned NOT NULL,
  `campaign_id` bigint(20) unsigned DEFAULT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `type` varchar(16) DEFAULT NULL,
  `status` varchar(16) DEFAULT NULL,
  `continue_reason_id` bigint(20) unsigned DEFAULT NULL,
  `continue_by` bigint(20) unsigned DEFAULT NULL,
  `end_reason_id` bigint(20) unsigned DEFAULT NULL,
  `comment` varchar(255) DEFAULT '',
  `call_count` int(11) NOT NULL DEFAULT '0',
  `answered_call_count` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `ix_call_session_contact_id` (`contact_id`),
  KEY `ix_call_session_user_id` (`user_id`),
  KEY `ix_call_session_campaign_id` (`campaign_id`),
  KEY `call_session_continue_by_foreign` (`continue_by`),
  KEY `call_session_end_reason_id_foreign` (`end_reason_id`),
  KEY `ix_call_session_end_time` (`end_time`),
  KEY `ix_call_session_start_time` (`start_time`),
  KEY `ix_call_session_continue_reason_id` (`continue_reason_id`),
  CONSTRAINT `call_session_campaign_id_foreign` FOREIGN KEY (`campaign_id`) REFERENCES `campaign` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `call_session_continue_by_foreign` FOREIGN KEY (`continue_by`) REFERENCES `user` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `call_session_continue_reason_id_foreign` FOREIGN KEY (`continue_reason_id`) REFERENCES `continue_reason` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `call_session_end_reason_id_foreign` FOREIGN KEY (`end_reason_id`) REFERENCES `end_reason` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `call_session_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
)

CREATE TABLE `continue_reason` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `project_id` bigint(20) unsigned DEFAULT NULL,
  `sort` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `ix_continue_reason_project_id` (`project_id`),
  CONSTRAINT `continue_reason_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

• 该call_session表包含超过 500,000 行。
• 该continue_reason表仅包含一行。

当我加入该列的continue_reason表时name，mysql 忽略start_date索引，因此查询需要 1.8 到 3 秒。我不知道为什么。

有问题的查询是：

SELECT `call_session`.`id`,
       `continue_reason`.`name`
FROM `call_session`
LEFT JOIN `continue_reason` ON `continue_reason`.`id` = `call_session`.`continue_reason_id`
ORDER  BY `call_session`.`start_time` DESC LIMIT 1;

解释结果：

没有连接的相同查询效果很好：

SELECT `call_session`.`id`,
       (SELECT `name` FROM `continue_reason` WHERE id = `call_session`.`continue_reason_id`) AS `continue_reason.name`
FROM `call_session`
ORDER  BY `call_session`.`start_time` DESC LIMIT  1;

解释结果：

我发现了很多类似的问题，但是我没有任何 DISTINCT，没有 GROUP BY，而且使用索引有一个非常明显的好处。

有什么想法吗？

提前致谢

PS Force 指数没有帮助。

这是优化器报告

标签： mysqlindexingsql-order-by