mysql - 在表上缓慢加入(10,000行)

我有表操作（30行）和passed_actions（10k行）

动作表：

CREATE TABLE `actions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int(10) unsigned NOT NULL,
  `author_id` int(10) unsigned NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'need for url',
  `about` longtext COLLATE utf8_unicode_ci,
  `image` text COLLATE utf8_unicode_ci,
  `page_title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `page_description` varchar(512) COLLATE utf8_unicode_ci DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `actions_slug_unique` (`slug`),
  KEY `actions_author_id_foreign` (`author_id`),
  KEY `actions_category_id_foreign` (`category_id`),
  CONSTRAINT `actions_author_id_foreign` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `actions_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

和passed_actions（约9500行）

CREATE TABLE `passed_actions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `action_id` int(10) unsigned NOT NULL,
  `hash` varchar(6) COLLATE utf8_unicode_ci NOT NULL,
  `public` tinyint(1) NOT NULL DEFAULT '1',
  `successfully_passed` tinyint(1) NOT NULL DEFAULT '0',
  `started_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `passed_actions_hash_unique` (`hash`),
  KEY `passed_actions_user_id_foreign` (`user_id`),
  KEY `passed_actions_action_id_foreign` (`action_id`),
  CONSTRAINT `passed_actions_action_id_foreign` FOREIGN KEY (`action_id`) REFERENCES `actions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `passed_actions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=25733 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

此查询需要0.3-0.5s：

select
    actions.*
from actions
left join passed_actions.action_id = actions.id
group by actions.id
order by count(passed_actions.id) DESC

这会影响我的api的响应时间...
为什么会这样呢？我认为1万行不是一张大桌子...

我使用默认的mysql配置。我的服务器是1GB的ram和1 cpu（数字海洋液滴）

最佳答案

您的查询实际上是合理的快速。有时，相关的子查询可以帮助您：

select a.*
from actions a
order by (select count(*) from passed_actions pa where pa.action_id = a.id) desc;

这可以使用您在passed_actions(action_id)上定义的索引。