When to use database Indexes

Transferred from "http://www.gootry.com/java-base/article/101010154844"

 

Although the original intention of using indexes is to improve the database performance, you must avoid using indexes sometimes. The following describes how to use indexes.

1. Indexes should not be used for small-scale tables.

2. When a field is used as a filter for the WHERE clause to return most records in the table, this field is not suitable for indexing.

 

For example, the index in a book does not contain words like the OR and.

3,Tables that are often updated in batches can have indexes.But the performance of batch operations will be reduced by indexing.

 

For tables that are often loaded or operated in batches, you can remove the index before performing the batch operation and re-create the index after completing the operation. This is because when data is inserted in the table, the index is updated, which increases the overhead.

4. You should not set indexes for fields that contain a large number of null values.

5. Indexes should not be set for frequently-operated fields, because the maintenance of indexes will become very heavy.

 

Warning: Exercise caution when creating an index with a particularly long keyword, because a large amount of I/O overhead will inevitably reduce database performance.

It can be seen that setting indexes for fields such as gender does not have any benefit.

 

For example, submit the following query to the database:

Select * From table_name
Where gender = "female ";

We can see that there is a continuous Behavior between the table and the index when running the preceding query.

 

Because the where gender = 'female '(or 'male') clause returns a large number of records, the Database ServiceProgramYou must continuously read the index, then read the table content, then read the index, and then read the table.

 

In this example, because most of the data in the table must be read, full table scan may be more efficient.

In general, when a field is used as a condition in the query to return most of the data in the table, we will not set an index for it.

 

In other words,Do not set indexes for fields with few different values, such as gender. This is usually called the base of a field or the uniqueness of data.

 

A high base indicates a high uniqueness, such as data like an ID number. The uniqueness of a low base is not high, such as a field such as gender.

Prompt: Indexes are helpful for improving performance, but may also reduce performance in some cases. We should avoid creating indexes for fields that only contain a few different values, such as gender and state name.