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?
thanks
