Why 90% of production databases aren't indexed properly

By David M Williams
Wednesday, 18 February 2009 15:55

Market

Database design isn’t rocket science but, at the same time, it is a craft which must take into account how the database will be used. More often than not this doesn’t happen and businesses everywhere are losing billions of micro-seconds on underperforming databases due to simple lack of care.

Ok, my figure of 90% is totally made up. However, I’m prepared to wager the real figure is high. I’ve not conducted any empirical studies – and it would probably be very hard to do so – but this is my genuine real-world experience and in fact this article is occasioned by my having to fix up yet another database provided by a vendor who didn’t know how to, or think to, optimise it.

What I’m talking about here comes down to one simple thing: indexing. That’s it.

Ok, sure, most developers and designers know they ought to index important fields. The database system will itself index the primary key by default.

(The primary key is the record or combination of records that uniquely identify any single row. For instance, a car registration plate is a natural primary key, as is a social security number or tax file number. A person’s first name is not a primary key because it has no guarantees of being unique in any arbitrary table of people.)

Actually, it’s possible to over-index a database. Every index will add time when data is inserted, deleted or modified because the database system has to adjust all those indexes.

However, I’m talking about something different. Clustered indexes. And I’m sick and tired of seeing clustered indexes ignored.

Let me tell you just what a clustered index is and why it matters. I’ll even show you with a real-world leading financial application.