mysql大表和大表连接_如何在大表上优化这个mysql连接？

我有一个项目,管理员需要创建多个新闻简报,其中包含来自网络的一些抓取的帖子.

我在抓取完成后在帖子表中插入帖子,并为其分配一个feed_id来标识来源.这是posts表的结构(截断)：

CREATE TABLE `posts` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`feed_id` int(11) NOT NULL,

`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`updated_at` timestamp NULL DEFAULT NULL,

`identifier` varchar(255) DEFAULT NULL,

`published` timestamp NULL DEFAULT NULL,

`content` longtext,

...

...

`is_unread` int(1) NOT NULL DEFAULT '1',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

每个管理员(用户)都可以访问一个或多个“提要”.因此,在时事通讯创建页面中,我想向他们展示他们可以看到的提要中的帖子列表,并且,我还会显示一个按钮,将帖子放在该时事通讯的特定类别中,如果用户之前选择了该帖子,我应该告诉他,让他从类别中删除它.所以我也有其他一些表：newsletters,categories,newsletter_post,category_post.这是他们的结构：

通讯：

CREATE TABLE `newsletters` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`updated_at` timestamp NULL DEFAULT NULL,

`sent_at` timestamp NULL DEFAULT NULL,

`title` varchar(255) DEFAULT NULL,

`date` date DEFAULT NULL,

`topic_id` int(11) NOT NULL,

`user_id` int(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

类别：

CREATE TABLE `categories` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`topic_id` int(11) NOT NULL,

`title` varchar(255) DEFAULT NULL,

`slug` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

newsletter_post：

CREATE TABLE `newsletter_post` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`updated_at` timestamp NULL DEFAULT NULL,

`newsletter_id` int(11) NOT NULL,

`post_id` int(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

category_post：

CREATE TABLE `category_post` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`updated_at` timestamp NULL DEFAULT NULL,

`category_id` int(11) NOT NULL,

`post_id` int(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

因此,我正在使用此查询查找允许的Feed的帖子,并检查帖子是否属于此特定新闻稿的特定类别：

SELECT DISTINCT `posts`.`id`, `published`, `posts`.`title`, `posts`.`content`, `source_name`, `category_id`, `newsletter_id`, `link_href`, categories.title as category_title

FROM `posts`

LEFT JOIN `category_post` ON `posts`.`id` = `category_post`.`post_id`

LEFT JOIN `categories` ON `categories`.`id` = `category_post`.`category_id`

LEFT JOIN `newsletter_post` ON `posts`.`id` = `newsletter_post`.`post_id`

LEFT JOIN `newsletters` ON `newsletters`.`id` = `newsletter_post`.`newsletter_id`

WHERE `feed_id` IN (6, 7) ORDER BY `posts`.`published` DESC LIMIT 40 OFFSET 0

但问题是这很糟糕而且没有优化.我的帖子表每月最多包含50,000行,每行包含3~10kbs的平均数据,所以有时当我尝试运行查询时(经常由管理员运行来制作时事通讯,分页等)mysql显示此错误：要加入的行太多等等,而且大多数时候它的速度非常慢.

我在一个查询中执行所有这些操作的原因是因为我希望结果存在于一个json响应中,因此我可以快速向用户显示,而无需执行其他请求.

我想知道是否有更好的方法来执行此查询或使用索引或其他东西.

提前感谢您的帮助.