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