MariaDB 10.4

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


10.4.12-MariaDB


CREATE TABLE `users`
(
  `id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR (30),
  `avatar`   VARCHAR (30)
);
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`)
  -- FOREIGN KEY (author_id) REFERENCES users (id) -- can't get this to work!
) ENGINE=InnoDB AUTO_INCREMENT=102183 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- doesn't work in this fiddle!

ALTER TABLE forum_posts
ADD FOREIGN KEY (author_id) REFERENCES users(id)
Can't create table `db_1815534085`.`forum_posts` (errno: 150 "Foreign key constraint is incorrectly formed")

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`),
  FOREIGN KEY (category_id) REFERENCES forum_categories (id),  -- couldn't get this to work
  KEY `FK_forum_topics_forum_categories` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7040 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,  -- << needs to be a FOREG
  `user_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY (`topic_id`, `user_id`),   -- added this
  KEY (`user_id`),     -- added this
  FOREIGN KEY (topic_id) REFERENCES forum_topics (id)  -- added this, it works!
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb4;
EXPLAIN EXTENDED 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


id
select_type
table
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
forum_topics
ALL
null
null
null
null
1
100.00
Using where; Using temporary; Using filesort

1
SIMPLE
users
eq_ref
PRIMARY
PRIMARY
4
db_1815534085.forum_topics.author_id
1
100.00
Using where

1
SIMPLE
forum_topics_seen
ref
topic_id,user_id
topic_id
8
db_1815534085.forum_topics.id,const
1
100.00
Using index

1
SIMPLE
forum_posts
ALL
null
null
null
null
1
100.00
Using where; Using join buffer (flat, BNL join)

1
SIMPLE
forum_categories
eq_ref
PRIMARY
PRIMARY
4
db_1815534085.forum_topics.category_id
1
100.00



EXPLAIN EXTENDED WITH p_cnt AS
(
  SELECT COUNT(*) AS pcnt FROM forum_posts
)
SELECT  
         ft.id, 
         ft.title, 
         ft.date_created, 
         ft.updated, 
         ft.last_activity, 
         ft.category_id,
         SUBSTR(ft.content, 1, 70) as content, 
         ft.author_id,
         u.username,
         u.avatar,
         p.pcnt,
         CASE WHEN fts.user_id = 49 then 1 else 0 end as seen,
         fc.category_order,
         fc.name as cat_name
FROM forum_topics ft 
INNER JOIN users u ON u.id = ft.author_id  -- author_id needs to be a FOREIGN KEY but I can't get it to work in a fiddle!
LEFT JOIN forum_topics_seen fts ON  fts.topic_id = ft.id 
                                AND fts.user_id = 49
LEFT JOIN forum_categories fc ON fc.id = ft.category_id CROSS JOIN p_cnt p
GROUP BY ft.id
ORDER BY ft.last_activity DESC 
LIMIT 3


id
select_type
table
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
PRIMARY
ft
ALL
null
null
null
null
1
100.00
Using where; Using temporary; Using filesort

1
PRIMARY
u
eq_ref
PRIMARY
PRIMARY
4
db_1815534085.ft.author_id
1
100.00
Using where

1
PRIMARY
fts
ref
topic_id,user_id
topic_id
8
db_1815534085.ft.id,const
1
100.00
Using index

1
PRIMARY
fc
eq_ref
PRIMARY
PRIMARY
4
db_1815534085.ft.category_id
1
100.00


1
PRIMARY
<derived2>
ALL
null
null
null
null
2
100.00
Using join buffer (flat, BNL join)

2
DERIVED
forum_posts
index
null
PRIMARY
4
null
1
100.00
Using index
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	forum_topics	ALL	null	null	null	null	1	100.00	Using where; Using temporary; Using filesort
1	SIMPLE	users	eq_ref	PRIMARY	PRIMARY	4	db_1815534085.forum_topics.author_id	1	100.00	Using where
1	SIMPLE	forum_topics_seen	ref	topic_id,user_id	topic_id	8	db_1815534085.forum_topics.id,const	1	100.00	Using index
1	SIMPLE	forum_posts	ALL	null	null	null	null	1	100.00	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	forum_categories	eq_ref	PRIMARY	PRIMARY	4	db_1815534085.forum_topics.category_id	1	100.00