“I Don’t Want To Create Indexes Because It Will Slow Down My Application.”
I hear that argument all the time from software developers at clients when I tell them that we need to create indexes. This is usually followed up by “If we add indexes it will slow down the INSERT, UPDATE and DELETE statements.” And yes it will, normally by nanoseconds at most. If you are building the next Twitter, or Facebook then yes having lots of Indexes will be a problem that you can measure. But small applications with hundreds or thousands of users will have such a small performance impact by having the indexes there that you’ll never be able to measure the difference.
But with the indexes in place those slow queries which you have, which are typically the reason that I’m there in the first place, will go from seconds or minutes down to milliseconds. That’s a good pain for an acceptable cost. The gain is a well performing application, the cost is an unperceivable performance difference on data change.
The reason that people are having this argument is because conference speakers (myself included) have apparently done too good of a job telling people about the cost of having indexes on their system without explaining why we still need indexes.
So to the software developers and architects of the world, indexes are good when they are needed and are being used. If I’m there telling you that we need to create a bunch of indexes then we need to create them (my record is a CREATE INDEX script that was 450,000 lines long as there were 1000 databases, one per customer). After we create them we’ll remove any indexes which aren’t being used anymore.
To the speakers who talk about indexes we need to explain not just the downsides of indexes but do a better job of explaining why we need to create indexes and what the real cost of not creating the needed indexes is.
Denny
One Response
I hear this one all the time. And I explain to everyone that all design decisions come down to cost, benefit and risk. People forget that this needs to apply to the entire system, not just the tiny part they work on.
Indexing is the closest thing we have to “free” performance gains. As you say, they aren’t free. But well designed ones sure do pay off.