4
\$\begingroup\$

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?

\$\endgroup\$
  • \$\begingroup\$ Is the tag or the post given as text not id? \$\endgroup\$ – ArtOfCode Jan 14 '15 at 9:46
  • \$\begingroup\$ @ArtOfCode the tag, example site.com/posts/tagged/general, the tag would be general \$\endgroup\$ – php_nub_qq Jan 14 '15 at 9:55
6
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ About backticks: MySQL WorkBench adds them automatically in my own painful experience. \$\endgroup\$ – Mathieu Guindon May 28 '15 at 1:03
  • \$\begingroup\$ I have not experienced that at all, perhaps it's a setting? Mine takes whatever I type in and will raise a syntax error if I don't use back ticks where I should've used them \$\endgroup\$ – Phrancis May 28 '15 at 1:12
  • \$\begingroup\$ I believe I've gotten this from phpMyAdmin or Navicat, which both add backticks for me, I don't know if it's a setting but I don't mind it. \$\endgroup\$ – php_nub_qq May 28 '15 at 8:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.