Bad Standards: #1--Limits on Indexing
Actually, this blog entry is the first in a new series on bad standards. Every company has that binder full of corporate and/or IT standards. You know, it is that one over there in the corner with the cobwebs on it? The one that you only use when you need an excuse to avoid work… well, maybe its not quite that bad.
Basically, what happens is that some well-meaning authority comes up with a “rule” or “guideline” that makes sense at some point – and then decides to enshrine it for eternity in the standards manual. It is the eternity part that I take exception with.
Standards can be worthwhile as a measuring stick to work from, hopefully ensuring that reliable and efficient databases and applications are built in a standard manner. But a rule that made sense 10 or 15 or 20 years ago probably is no longer reasonable. Every standard in your book should be reviewed at least annually to determine whether it is still reasonable to enforce.
One such rule is today’s topic and if a form of it still exists in your standards manual drop everything you were going to do today and expunge it immediately from your standards book. There should be no arbitrary limit on the number of indexes that you can create for any database table. Indexes are undoubtedly one of the most important factors in creating efficient queries. Relational optimizers rely on indexes to build fast access paths to data. Without indexes data must be scanned – and that can be a long, inefficient means by which to retrieve your data.
When a rule such as what I am talking about here exists, it usually is stated in the standards manual something like this:
“Each table can have at most 5 indexes created for it” - or
“Do not create more than 3 indexes for any one table in a database.”
These are bad standards. Very bad standards. Horrible standards. I should probably use a bold red font for the previous bulleted items because people tend to focus on statements that stand out like that and don’t read entire article or posting. Don't do that!
Anyway, a good indexing standard would read something like this: “Create indexes as necessary to support your database queries. Limitations on creating new indexes should only be entertained when they begin significantly to impede the efficiency of data modification.”
Now that is a good standard! I guess I should use bold green text on that one, huh? Red is bad; green is good.
I intend to take on other nasty standards that should be eliminated in subsequent blog entries here. Please feel free to e-mail me your favorites (or perhaps I should say least favorites) standards that should be eliminated. I’d be happy to take them on here for you.
I can be reached at craig@craigsmullins.com
© 2005, Mullins Consulting, Inc.
