Mysql – Index optimization

database-designMySQLrelational-theory

I'm building a 'tag' system for my blog posts. Currently my tags table has columns tagid - MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY and tag VARCHAR(30) NOT NULL UNIQUE.
And I have another table describing many to many relation between my blog posts and tags, table posttags with columns postid,tagid and dual PRIMARY KEY(postid,tagid) .

Now when I started learning mysql, PRIMARY KEYs were all about the unique content. But now I understand that they are more of performance optimization things.

postid is obviously an auto increment int.

Most of my queries are like :

  • select all from posts join tags where postid = {postid},
  • select all from posts join tags where tag = {tag name}.

Is my current DB structure good enough or are there any improvements I could make? also please suggest what all feilds should I make indexs, primary keys or unique?

UPDATE : InnoDB is the engine I'm using. Also, I have not defined any foreign keys as I don't see any benefit since my PHP side is capable of maintaining the integrity and consistency of data,

Best Answer

I think that the best for your tag and posttag tables is:

  1. ok for the tagid as a primary key
  2. add an index on your tag field (this cover your second query)
  3. add two index on posttag table like INDEX(postid,tagid) and INDEX(tagid,postid) (this cover your first and second query)

So:

CREATE TABLE `posts` (
  `postid` mediumint(9) NOT NULL AUTO_INCREMENT,
  `post` varchar(255) NOT NULL,
  PRIMARY KEY (`postid`)
);

CREATE TABLE `posttags` (
  `postid` mediumint(9) NOT NULL,
  `tagid` mediumint(9) NOT NULL,
  PRIMARY KEY (`postid`,`tagid`),
  UNIQUE KEY `tagid_postid_UQ` (`tagid`,`postid`),
  FOREIGN KEY (postid)
    REFERENCES posts (postid),
  FOREIGN KEY (tagid)
    REFERENCES tags (tagid)
);

CREATE TABLE `tags` (
  `tagid` mediumint(9) NOT NULL AUTO_INCREMENT,
  `tag` char(30) NOT NULL,
  PRIMARY KEY (`tagid`),
  UNIQUE KEY `tag_UQ` (`tag`)
);

And for your query:

mysql> explain select p.* from tags as t    inner join posttags as pt on t.tagid = pt.tagid   inner join posts as p on p.postid = pt.postid  where t.tag =  ?;
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref            | rows | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+--------------------------+
|  1 | SIMPLE      | t     | ref    | PRIMARY,tag   | tag     | 32      | const          |    1 | Using where; Using index |
|  1 | SIMPLE      | pt    | ref    | postid,tagid  | tagid   | 3       | test.t.tagid   |    1 | Using index              |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY       | PRIMARY | 4       | test.pt.postid |    1 |                          |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+--------------------------+


explain select p.* from posts as p    inner join posttags as pt on pt.postid = p.postid   inner join tags as t on t.tagid = pt.tagid  where p.postid = ?;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
|  1 | SIMPLE      | p     | const  | PRIMARY       | PRIMARY | 4       | const         |    1 |             |
|  1 | SIMPLE      | pt    | ref    | postid,tagid  | postid  | 4       | const         |    2 | Using index |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY       | PRIMARY | 3       | test.pt.tagid |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
Related Question