by Raj Kumar

Created on: August 25, 2009

Need of an index makes more sense when the amount of data processed within a database table is large. Large enough that it takes too much time to fulfill the request of search criteria within a SQL (Structured Query Language) "select" query or command. The performance of "select" query tests the patience and persistence of the user. If the query is directly executed by database developers or administrators, they can better understand the technical bottleneck of the query and its process of SQL parsing which in turn affect the cycle of data request to response of final output. But the situation is different when the end user is indirectly accessing the data through any web or windows application. The simple user can not understand the complex working of data process. The user just desires the application to be fast with speedy response. Sluggishness of the data query impacts the entire performance which gets reflected on user interface. It can create very bad impression for the application which processes enormous data in the absence of table indexes.

Indexes provide quick relief and excellent solution to improve the performance of overall "select" query. In many relational databases like Microsoft SQL Server, there is wizard for performance tuning, which can analyze and indicate the weak link or point within the chain of elements involved in the SQL query. Developer can reduce the execution time by introducing appropriate index in target table which takes longer time to process. When columns, involved in SQL joins, are indexed, the retrieval of data and search conditions based on them become reasonably fast. The execution time gets reduced to minimum bearable cost. Thus overall performance of the database query improves significantly which in turn saves lot of time of application and its end users.

Index should not be created on any column within a database table. Before implementation, it should be properly analyzed keeping in mind the scaling power of application and number of users connecting the database simultaneously. Indexes are of different types. They are defined with proper SQL syntax as per respective database management system. Unique index provide uniqueness within a column of database table. Clustered index is an important type of index which sorts the data physically within a table and normally created with primary key constraint definition. As the data is physically sorted, sequential data search is extremely fast in such structural combination. Only one clustered index can be created on a database table as compared to non-clustered index.

If number of indexes per database table increases, memory required to store their information also increases. Data manipulation process on such table can also be impacted with slow data insertion, deletion and modification as indexes information needs to be updated. Hence number of indexes on each database table should be balanced in order to retain smooth execution of data manipulation commands in reasonable time. Indexes can not be ignored and they really contribute lot within huge databases. If properly organized and created, they can ease out the life of database developer. Application users will be impressed with turn around time. It will perform their request in very less time which will keep up their interest. On the other hand, mismanagement of indexes may fail to improve the application performance.

Learn more about this author, Raj Kumar.
Click here to send this author comments or questions.