| A nice little article. I've always felt that database indexes are one of those fringe topics that's just esoteric enough that people acquire a basic knowledge of them and know they need them, yet understanding indexes and using them correctly leads amazing performance. On clustered indexes in particular, I try to follow these principles: Narrow - in terms of a data type's byte length, so that more keys can be packed into each level of the B-tree. You can end up having to traverse fewer intermediate levels to reach the leaf level, where the data resides. Unique - This ties into the point above, there will be no need to add a "uniquifier" to the key, helping to save space and the overhead of managing that extra tid-bit of data Static - ideally, never changing. By it's nature, a clustered index is ordered, and updating/changing the keys will lead to data that's in the wrong place. You can kind of get around this by rebuilding the index after every update, but that just adds another task you'll need to manage more often. Increasing - this can lead to faster inserts; in a sense, the DB is just filling up the last page of the index and then adding another when it needs to. (I think after a certain amount of inserts, eventually you'll have to re-build the index (to add more intermediate level nodes) but I can't recall the specifics, and you're going to have to do rebuild indexes anyway with all indexing strategies.) |