| Although indexes are intended to enhance a database's performance, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered: -
Indexes should not be used on small tables. -
Indexes should not be used on columns that return a high percentage of data rows when used as a filter condition in a query's WHERE clause. For instance, you would not have an entry for the word "the" or "and" in the index of a book. -
Tables that have frequent, large batch update jobs run can be indexed. However, the batch job's performance is slowed considerably by the index. The conflict of having an index on a table that is frequently loaded or manipulated by a large batch process can be corrected by dropping the index before the batch job, and then re-creating the index after the job has completed. -
Indexes should not be used on columns that contain a high number of NULL values. -
Columns that are frequently manipulated should not be indexed. Maintenance on the index can become excessive.  | Caution should be taken when creating indexes on a table's extremely long keys because performance is inevitably slowed by high I/O costs. |
You can see in Figure 16.2 that an index on a column, such as gender, may not prove beneficial. For example, suppose the following query was submitted to the database: Figure 16.2. When to avoid using an index.  SELECT * FROM TABLE_NAME WHERE GENDER = 'FEMALE'; By referring to Figure 16.2, which is based on the previous query, you can see that there is constant activity between the table and its index. Because a high number of data rows is returned WHERE GENDER = 'FEMALE' (or MALE), the database server constantly has to read the index, and then the table, and then the index, and then the table, and so on. In this case, it may be more efficient for a full table scan to occur because a high percentage of the table must be read anyway. As a general rule, you do not want to use an index on a column used in a query's condition that will return a high percentage of data rows from the table. In other words, do not create an index on a column such as gender, or any column that contains very few distinct values.  | Indexes can be very good for performance, but in some cases may actually hurt performance. Refrain from creating indexes on columns that will contain few unique values, such as gender, state of residence, and so on. |
Dropping an Index An index can be dropped rather simply. Check your particular implementation for the exact syntax, but most major implementations use the DROP command. Care should be taken when dropping an index because performance may be slowed drastically (or improved!). The syntax is as follows :   DROP INDEX INDEX_NAME  | MySQL uses the ALTER TABLE command to drop indexes. Again, different SQL implementations may vary widely in syntax, especially when dealing with indexes and data storage. |
The most common reason for dropping an index is in an attempt to improve performance. Remember that if you drop an index, you can also re-create it. Indexes may need to be rebuilt sometimes to reduce fragmentation. It is often necessary to experiment with the use of indexes in a database to determine the route to best performance, which may involve creating an index, dropping it, and eventually re-creating it, with or without modifications.  |
