Why won't developers use indexes

It keeps turning up again and again. The application is released to the customer and after a few weeks, or even a few days, the first issues arrive on the slow performance.

Time and again, this is due to the application queries not using the right indexes, or even using any indexes at all. The queries can be well written, as I usually keep repeating to the developers how important that is. On the other hand, I keep telling everybody about the usefulness of indexes, and somehow that doesn't seem to stick...

So, let's try this one more time ;-)

If you write a query (any query), it is important to ask yourself a few questions:

• What data will this query access
• How are the tables in this query connected. Am I using the right connections (comparing the right attributes)
• Am I using the right filter conditions on the data

These are questions of a more logical nature. This has everything to do with constructing a good query. The "hard" part is the technical part of the puzzle:

• How will the database access my data
• Are there enough fast access paths (at least one) to choose from
• Will this query perform when retrieving the needed amount of data

These questions are really not that tough. Most of the time, when writing a query, you will have quite a good idea about what data and what amount of data is to be retrieved. Try to picture this query in the datamodel. How would the database be able to retrieve your data in the fastest way possible? On many occasions, the answer would be to use an index. When accessing a table with an index, only a portion of the table will have to be scanned and the access can be much, much faster.

So, to all developers out there, please consider your queries carefully. After creating the query, try to anticipate how it will function. If there is any doubt if the query will perform adequately, at least test it with a representative amount and distribution of data.

Of course, I am fully aware of the many types of queries out there, not to mention the many types of indexes you can use. Usually, it pays off to invest some time reading up on the use of indexes. There are a great number of resources to use out here on the internet, so I won't even try to begin explaining them. As a developer, do yourself a favor and spend some time getting to know the workings of the most common types of indexes.

And if all else fails: please contact your DBA...