Selecting all posts with a certain tag

The task is to select all posts tagged with a certain tag, along with other tags the post may have, given as text rather than ID.

Table posts

CREATE TABLE `posts` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `title` text NOT NULL,
 `text` text NOT NULL,
 `date` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

Table tags

CREATE TABLE `tags` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `tag` varchar(20) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `tag` (`tag`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

Table posts_tags

CREATE TABLE `posts_tags` (
 `post` int(10) unsigned NOT NULL,
 `tag` int(11) NOT NULL,
 PRIMARY KEY (`post`,`tag`),
 KEY `tag` (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And here is the SQL I've come up with:

SELECT posts.id,
       title,
       posts.text,
       date,
       t.tag
FROM   tags
       LEFT JOIN posts_tags
              ON tags.id = posts_tags.tag
       LEFT JOIN posts
              ON posts.id = posts_tags.post
       LEFT JOIN posts_tags pt
              ON posts.id = pt.post
       LEFT JOIN tags t
              ON t.id = pt.tag
WHERE  tags.tag = ?
ORDER  BY posts.id DESC

Are there any optimizations that can be done to this code?

Looks good.

There is not a whole lot to say that could improve performance. It's a pretty minimal example, so take that for what it is.

Table naming

Your DDL naming convention is hard to follow in your post_tags table:

CREATE TABLE `posts_tags` (
 `post` int(10) unsigned NOT NULL,
 `tag` int(11) NOT NULL,
 PRIMARY KEY (`post`,`tag`),
 KEY `tag` (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Would be more logical if you specified it was an Id in the name:

CREATE TABLE `posts_tags` (
 `post_id` int(10) unsigned NOT NULL,
 `tag_id` int(11) NOT NULL,
 PRIMARY KEY (`post`,`tag`),
 KEY `tag` (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Foreign keys

It looks like you don't have any FOREIGN KEY constraints, which may be a problem, or at least something you would want to integrate, so as to be able to raise key violations. For example, using the above-referenced table:

CREATE TABLE `posts_tags` (
 `post_id` int(10) unsigned NOT NULL,
 `tag_id` int(11) NOT NULL,
 PRIMARY KEY (`post`,`tag`),
 KEY `tag` (`tag`)
 FOREIGN KEY (post_id) REFERENCES posts(id),
 FOREIGN KEY (tag_id) REFERENCES tags(id),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

One last thing. The back ticks in MySQL are only needed for names that use reserved characters/delimiters, for example names that have a space between words. The way you are naming your tables and columns, none of those would be needed.

StackExchange Code Review Q#75576, answer score: 6