1

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

CREATE TABLE `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',
  PRIMARY KEY (`ID`),
  KEY `User` (`User`),
  KEY `EventKind` (`EventKind`),
  KEY `SiteSection` (`SiteSection`),
  KEY `Object` (`Object`)
) ENGINE=MyISAM;

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.

  • Just a further thought - you should use lookup tables for your EventKind and SiteSection fields. Here and here tell you why - see Bill Karwin's response in the second link. Karwin is Percona's "Senior Knowledge Manager" (whatever that is!). – Vérace Jun 24 '14 at 13:39
2

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.

  • Why would a unique index have to be fully scanned? – ypercubeᵀᴹ Jun 24 '14 at 12:29
  • Depends on how B-Tree has been built and i use almost fully not fully – Antonios Jun 24 '14 at 12:36
  • If you search a unique index with a WHERE a=? AND b=? (and (a,b) are the first parts of the unique index: (a,b,...)), I don't see how any implementation would not stop after not finding the element. If you meant to say that a partial (range) scan of the index has to made, that's fine. But that's not a full index scan. And if the WHERE condition matches all the parts of the index, then there is never need for a range scan. Just a seek and the search stops - either positively or negatively. – ypercubeᵀᴹ Jun 24 '14 at 13:05
  • My point is that there is no luck involved. It depends on the WHERE condition and not luck. – ypercubeᵀᴹ Jun 24 '14 at 13:12
  • I mention the case where "the WHERE part to match exactly with the index" means WHERE a=? AND b=? and having a unique index on (a,b) . Of-course there is no luck but you might get to worse scenario for B-tree or might find the pair on the first steps. – Antonios Jun 24 '14 at 13:13
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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