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 postsCREATE 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=utf8Table tagsCREATE 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=utf8Table posts_tagsCREATE 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=utf8And here is the SQL I've come up with:SELECT posts.id,       title,       posts.text,       date,       t.tagFROM   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.tagWHERE  tags.tag = ?ORDER  BY posts.id DESC  Are there any optimizations that can be done to this code?
Selecting all posts with a certain tag
sql;mysql
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 namingYour 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=utf8Would 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=utf8Foreign keysIt 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=utf8One 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. 