Join without indexing the entire table I have a query that pulls the 3 most recently commented posts on our forum. The issue is is that COUNT(forum_posts.id) as posts, is indexing the entire database of 600,000 posts and the query takes forever to complete. I'm left joining the table like this LEFT JOIN forum_posts ON forum_posts.topic_id = forum_topics.id. Apparently I will have to use another approach to this. Any suggestions?

SELECT    forum_topics.id, 
         forum_topics.title, 
         forum_topics.date_created, 
         forum_topics.updated, 
         forum_topics.last_activity, 
         forum_topics.category_id,
         SUBSTR(forum_topics.content, 1, 70) as content, 
         forum_topics.author_id,
         users.username,
         users.avatar,
         COUNT(forum_posts.id) as posts,
         CASE WHEN forum_topics_seen.user_id = 49 then 1 else 0 end as seen,
         forum_categories.category_order,
         forum_categories.name as cat_name
FROM forum_topics 
INNER JOIN users ON users.id = forum_topics.author_id
LEFT JOIN forum_topics_seen ON forum_topics_seen.topic_id = forum_topics.id AND forum_topics_seen.user_id = 49
LEFT JOIN forum_posts ON forum_posts.topic_id = forum_topics.id
LEFT JOIN forum_categories ON forum_categories.id = forum_topics.category_id
GROUP BY forum_topics.id
ORDER BY forum_topics.last_activity DESC LIMIT 3


Result from SHOW CREATE TABLE forum_posts

CREATE TABLE `forum_posts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `content` text CHARACTER SET utf8mb4 NOT NULL,
  `author_id` int(10) unsigned NOT NULL,
  `editor_id` int(10) unsigned DEFAULT NULL,
  `topic_id` int(11) unsigned NOT NULL,
  `date_created` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `updated` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=102183 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `forum_topics_seen` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `topic_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb4

CREATE TABLE `forum_categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `game` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `icon` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `category_order` int(11) DEFAULT NULL,
  `order` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `forum_topics` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(50) CHARACTER SET utf8mb4 NOT NULL,
  `content` longtext CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
  `author_id` int(11) unsigned DEFAULT NULL,
  `editor_id` int(11) unsigned DEFAULT NULL,
  `category_id` int(11) unsigned NOT NULL,
  `date_created` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `updated` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_activity` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `views` int(11) NOT NULL DEFAULT 0,
  `is_locked` int(11) NOT NULL DEFAULT 0,
  `is_sticky` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `FK_forum_topics_forum_categories` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7040 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE users
(
  id INTEGER,
  username VARCHAR (30),
  avatar   VARCHAR (30)
);


Mysql version: 10.4.8-MariaDB
You need to add these indexes:

CREATE INDEX ix_forum_posts_topic_id ON forum_posts(topic_id);
CREATE INDEX ix_forum_topics_last_activity ON forum_topics(last_activity);
CREATE INDEX ix_forum_topics_seen_topic_id_user_id  ON forum_topics_seen(topic_id, user_id);


After that you could use this simple query:

WITH CTE_topics AS (SELECT id FROM forum_topics ORDER BY last_activity DESC LIMIT 3)
SELECT    forum_topics.id, 
         forum_topics.title, 
         forum_topics.date_created, 
         forum_topics.updated, 
         forum_topics.last_activity, 
         forum_topics.category_id,
         SUBSTR(forum_topics.content, 1, 70) as content, 
         forum_topics.author_id,
         users.username,
         users.avatar,
         (SELECT COUNT(forum_posts.id) FROM forum_posts WHERE  forum_posts.topic_id = forum_topics.id) as posts,
         CASE WHEN forum_topics_seen.user_id = 49 then 1 else 0 end as seen,
         forum_categories.category_order,
         forum_categories.name as cat_name
FROM forum_topics 
INNER JOIN CTE_topics ON forum_topics.id = CTE_topics.id
INNER JOIN users ON users.id = forum_topics.author_id
LEFT JOIN forum_topics_seen ON forum_topics_seen.topic_id = forum_topics.id AND forum_topics_seen.user_id = 49
LEFT JOIN forum_categories ON forum_categories.id = forum_topics.category_id;