For now let's ignore the benefits of having a surrogate key for business logic reasons. Let's only consider performance (speed and disk usage).

CREATE TABLE `person` (
  `ssn` bigint(20) unsigned NOT NULL DEFAULT '0',
  `fav_color` char(10) DEFAULT NULL,
  PRIMARY KEY (`ssn`)

Let's say the only read query I want to do is selecting fav_color for a contiguous range of ssn. Let's assume ssn is globally unique.

Unfortunately the order in which I add people to this table is random WRT their ssn, and so the clustered index would become fragmented, right?

Well, to solve that, I could change to this:

CREATE TABLE `person` (
  `person_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ssn` bigint(20) unsigned DEFAULT NULL,
  `fav_color` char(10) DEFAULT NULL,
  PRIMARY KEY (`person_id`),
  UNIQUE KEY `ssn` (`ssn`,`fav_color`)

I made the ssn key a covering index because I want "USING INDEX" when I select fav_color on a range query of ssn.

So now the clustered index is nice and non-fragmented, but my secondary index will still be fragmented, right? And that's all that will matter for reads since I'm USING INDEX. So as a novice, I don't know why having a non-fragmented PK layout helps much here.

And remember, I only care about doing retrieval on ranges of ssn.

So can we analytically say whether one is likely preferable to the other for space/speed reasons? I imagine the latter would use more space?

Or would it just require an empircal benchmark?


  • You're correct, but depending on the random-read speed of the I/O, the size of the table (or index), and memory usage of that table (or index), the fragmentation in the non-clustered index may not matter as much as you think it does. Or in other words, create a test and see what happens. Sep 10 '16 at 1:45
  • @RandolphWest, I will. Since my secondary index is covering, is there anything fundamentally different about it's structure from the 'proper' PK clustered index? In a sense they're both clustered, right? Sep 10 '16 at 1:47
  • 1
    Don't say it that way to purists. A clustered index is the data itself, whereas a non-clustered index is a copy of the data - and the structure of the NC index will include a reference to the clustered index. If your NC contains SSN and fav_color, then it's covering and you may as well use SSN for the clustering key if you want to save space. It comes down to random writes, as you surmised in the start, and speed of I/O vs fragmentation. Sep 10 '16 at 1:53

Your Answer

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

Browse other questions tagged or ask your own question.