Index optimization

Question

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,

• N.B.: PRIMARY KEY has only one job and that's what you said earlier - uniqueness. It just exists so you can distinguish between rows. Now, having an autoincremented integer is the easiest method to implement this kind of behaviour. Also, the smart guys that developed InnoDB used the integer-auto_incremented PK to also boost performance. To keep it short, there's nothing wrong with your DB schema, I doubt you can do much there except min/max maybe, but personally I wouldn't change anything.

Answer 1

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 |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+

• ypercubeᵀᴹ: No primary key for table posttags?
• Cristian Porta: @ypercube Ops... yes on (postid,tagid) :)
• Peeyush Kushwaha: could you explain how mysql works PRIMARY KEY postid (postid,tagid), UNIQUE KEY tagid (tagid,postid) ? As far as I know mysql concats both values for primary keys and stores that as index, so unless my query is somethin like SELECT * FROM table WHERE tagid = ?,postid = ?, it wont do me any good. will the primary key you suggest be much useful in JOINs?
• Michiel van Vaardegem: No foreign keys in de posttag table?