Mysql – Should I create a multi-column UNIQUE index


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.

Best 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.