Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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,

share|improve this question
    
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. –  N.B. May 6 '13 at 15:47

1 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 |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
share|improve this answer
    
No primary key for table posttags? –  ypercube May 7 '13 at 8:14
    
@ypercube Ops... yes on (postid,tagid) :) –  Cristian Porta May 7 '13 at 8:29
    
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? –  Peeyush Kushwaha May 7 '13 at 17:10
1  
No foreign keys in de posttag table? –  Michiel van Vaardegem May 8 '13 at 7:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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