Should I create a multi-column UNIQUE index?

Posted on

Question :

I have created the following MySQL table, which stores which sections of our site a user visited (and the Count of visits):

  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `User` int(10) unsigned NOT NULL,
  `EventKind` enum('search','purchase','subsectionList','event','like','superprice','toursList','abonementsList','abonement') NOT NULL,
  `SiteSection` enum('section','subsection','superprice','region','city','children','tour','abonement') NOT NULL,
  `Object` int(10) unsigned NOT NULL DEFAULT '0',
  `Count` int(10) unsigned NOT NULL DEFAULT '0',
  KEY `User` (`User`),
  KEY `EventKind` (`EventKind`),
  KEY `SiteSection` (`SiteSection`),
  KEY `Object` (`Object`)

The quadruple User/EventKind/SiteSection/Object is always unique. My question: Is it worth (especially due performance considerations) to add a 4-columns UNIQUE key?

This data will be used to show a user these sections of the site which are related with his previous visits.

Answer :

The good thing with unique indexes is that search stops when the first value matches, but that requires the WHERE part to match exactly with the index. In your case the index will be big. If you are lucky the value might be found quickly on the b-tree, else it might need to scan almost the entire index.

You would create the index if any of this is true:

  1. There are queries or DML statements that benefit from this index.
  2. You want to enforce uniqueness of the combination of the index columns.

You would not create the index if all of this is true:

  1. There are no queries or DML statements that benefit from it.
  2. You don’t want to enforce uniqueness of these columns.
  3. DML statement performance against this table is critical and the benefit of one fewer index offsets the loss (if any) of query performance.

This applies equally to non-multi-column indexes.

Leave a Reply

Your email address will not be published. Required fields are marked *