0

I read but I'm still confused when to use a normal index or a unique index in MySQL. I have a table that stores posts and responses (id, parentId). I have set up three normal indices for parentId, userId, and editorId.

  1. Would using unique indices benefit me in any way given the following types of queries I will generally run? And why?

Most of my queries will return a post and its responses:

SELECT * FROM posts WHERE id = @postId OR parentId = @postId ORDER BY postTypeId

Some times I will add a join to get user data:

SELECT * FROM posts
JOIN users AS owner ON owner.id = posts.userId
LEFT JOIN users AS editor ON editor.id = posts.editorId
WHERE id = @postId OR parentId = @postId ORDER BY postTypeId

Other times I may ask for a user and his/her posts:

SELECT * FROM users
LEFT JOIN posts ON users.id  = posts.userid
WHERE id = @userId

My schema looks like this:

CREATE TABLE `posts` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `posttypeid` int(10) NOT NULL,
  `parentid` int(10) DEFAULT NULL,
  `body` text NOT NULL,
  `userid` int(10) NOT NULL,
  `editorid` int(10) NOT NULL,
  `updatedat` datetime DEFAULT NULL,
  `createdat` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
KEY `userId` (`userid`),
KEY `editorId` (`editorid`),
KEY `parentId` (`parentid`)
) ENGINE=InnoDB AUTO_INCREMENT=572 DEFAULT CHARSET=utf8
Community
  • 1
  • 1
Mohamad
  • 34,731
  • 32
  • 140
  • 219

3 Answers3

2

When an index is created as UNIQUE, it only adds consistency to your table: inserting a new entry reusing the same key by error will fail, instead of being accepted and lead to strange errors later.

So, you should use it for your IDs when you know there won't be duplicate (it's by default and mandatory for primary keys), but it won't give you any benefits performance wise. It only gives you a guarantee that you won't have to deal with a specific kind of database corruption because of a bug in the client code.

However, if you know there can be duplicates (which I assume is the case for your columns userId, editorId, and parentId), using the UNIQUE attribute would be a serious bug: it would forbid multiple posts with the same userId, editorId or parentId.

In short: use it everywhere you can, but in this case you can't.

Jerome
  • 2,350
  • 14
  • 25
  • thank you for that explanation. It makes sense, I think. Why bother using it in my scenario? A user can have many responses to a post, so userId, parentId will be duplicated, but the post.id will not. Why would it make any sense to use it? Won't the post.id always be unique? – Mohamad Jun 01 '11 at 18:09
1

Unique is a constraint that just happens to be implemented by the index.

Use unique when you need unique values. IE no duplicates. Otherwise don't. That simple really.

Byron Whitlock
  • 52,691
  • 28
  • 123
  • 168
1

Unique keys do not have any benefit over normal keys for data retrieval. Unique keys are indexes with a constraint: they prevent insertion of the same value and so they only benefit inserts.

Mel
  • 6,077
  • 1
  • 15
  • 12