Understanding Indexes

The best way to understand indexes is to envision the index at the back of a book (this book, for example).

Suppose that you wanted to find all occurrences of the word "datatype" in this book. The simple way to do this would be to turn to page one and scan every line of every page looking for matches. Although that would work, it is obviously not a workable solution. Scanning a few pages of text might be doable, but scanning an entire book is not. As the amount of text to be searched increases, so does the time it takes to pinpoint the desired data.

And so books have indexes. An index is an alphabetical list of words with references to their locations in the book. To search for "datatype," you would find that word in the index to determine what pages it appears on. You could then turn to those specific pages to find your matches.

So what is it that makes an index work? Simply, the fact that it is sorted correctly. The difficulty in finding words in a book is not the amount of content that needs to be searched; rather, it is the fact that the content is not sorted by word. If the content were sorted like a dictionary, an index would not be needed (which is why dictionaries don't have indexes).

Database indexes work in much the same way. Primary key data is always sortedthat's just something the DBMS does for you. So retrieving specific rows by primary key is always a fast and efficient operation.

But searching for values in other columns is usually not as efficient. For example, what if you wanted to retrieve all customers who lived in a specific state? Because the table is not sorted by state, the DBMS would have to read every row in the table (starting at the very first row) looking for matchesjust as you would have to do if you were trying to find words in a book without using an index.

The solution is to use an index. You may define an index on one or more columns so that the DBMS keeps a sorted list of the contents for its own use. After an index is defined, the DBMS uses it in much the same way that you would use a book index. It searches the sorted index to find the location of any matches and then retrieves those specific rows.

But before you rush off to create dozens of indexes, bear in the mind the following:

• Indexes improve performance of retrieval operations, but they degrade the performance of data insertion, modification, and deletion. This is because when those operations are executed, the DBMS has to dynamically update the index.

• Index data can take up lots of storage space.

• Not all data is suitable for indexing. Data that is not sufficiently unique (a state, for example) will not benefit as much from indexing as data that has more possible values (first name or last name, for example).

• Indexes are used for data filtering and for data sorting, so if you frequently sort data in a specific order, that order might be a candidate for indexing.

• Multiple columns may be defined in an index (for example, State plus City), in which case that index will be of use only when data is sorted in that order. If you wanted to sort by City, that index would not be of any use.

There is no hard and fast rule as to what should be indexed and when. Most DBMSes provide utilities that you can use to determine the effectiveness of indexes, and you should use these regularly.

Indexes are created with the CREATE INDEX statement (the syntax of which varies dramatically from one DBMS to another). The following statement creates a simple index on a Products table's product name column:

 CREATE INDEX prod_name_ind ON PRODUCTS (prod_name) 

Every index must be uniquely named. Here the name prod_name_ind is defined after the keywords CREATE INDEX. ON is used to specify the table being indexed, and the columns to include in the index (just one in this example) are specified in parentheses after the table name.

TIP

Index effectiveness changes as table data is added or changed. Many database administrators find that what was once an ideal set of indexes might not be so ideal after several months of data manipulation. As such, it is a good idea to revisit indexes on a regular basis to fine-tune them as needed.

NOTE

Index use is essentially transparent to ColdFusion development.