Building Useful Database Indexes

When and where we create index it is difficult task. Here some tips when we creating the index in table.

Index by workload, not by table

Many people make the mistake of just guessing at some indexes to create when they are creating database tables. Without an idea of how the tables are going to be accessed, though, these guesses are often wrong – at least some of them.

Indexes should be built to optimize the access of your SQL queries. To properly create an optimal set of indexes requires a list of the SQL to be used, an estimate of the frequency that each SQL statement will be executed, and the importance of each query. Only then can the delicate balancing act of creating the right indexes to optimize the right queries most of the time be made.

If you are doing it any other way, you are doing it wrong.

 

Index most heavily used queries

Based on the search condition we need to create index on that key.

Index to avoid sorting (Group by, order by)

The GROUP BY and ORDER BY clauses tend to invoke sorts, which can cause performance slowdowns. By indexing on the columns specified in these clauses the relational optimiser can use an index to avoid a sort, and thereby potentially improve performance.

Create index for uniqueness (on primary key or unique key)

Some indexes are required in order to make the database schema valid. Most database systems require    that unique indexes be created when unique constraints and primary key constraints exist.

Create indexes for foreign keys

Creating indexes for each foreign key can optimize the performance when accessing and enforcing referential constraints. Most database systems do not require such indexes, but they can improve performance.

Be aware of data modification implications

The DBMS must automatically maintain every index you create. This means every INSERT and every DELETE to an indexed table will insert and delete not just from the table, but also from its indexes.Additionally, when you UPDATE the value of a column that has been defined in an index, the DBMS must also update the index. So, indexes speed the process of retrieval but slow down modification.