There are some significant performance advantages from having an index replace a query. It can speed up the retrieval of data from your database, assuming that the database is designed so that the index will work, that's it's there, or that you can add an index of important data items.
If your database is well designed, you will have constructed your tables and indexes to support your input screens, queries, and reports. There's a good chance that you may have constructed an index that contains all of the information you would want to obtain from a particular query; or if you hadn't that you can. For example, if you need a list of your employee's names and you have an index with that information in your EMPLOYEES table, just reading the index is what you really need. There are some significant performance advantages from having an index replace a query.
The code necessary to have a query read an index is straightforward. Here's an example:
SELECT DISTINCT EmpName
You can, of course, extend this to retrieve data from more than one index at a time. As you consider what indexes to build and maintain in your database, keep this fundamental time save in mind.
Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.
This was first published in December 2002