by Robert Laws

Created on: October 20, 2009

How a database functions depends upon the Database Management System (DBMS) used. Microsoft SQL Server, Oracle and MySQL are examples of a DBMS. There are some standard features that almost all DMBS share, but each one has its own proprietary features too.

It is not necessary to establish table indexes unless the DBMS demands it. In some cases, the DBMS will insist on a key being provided for each table and may automatically create a table index based on that key. However, most DBMS do not insist on any indexes being created.

Although not 'necessary', table indexes are usually desirable for performance reasons.

When new data is added to a table, it is appended to the bottom of the table. As more and more data is added and the table grows larger, the data remains in the same order as it was added (the 'arrival sequence'). If this table represents a customer list and you want to retrieve information on Mr. Brown, the only way Mr. Brown can be found is to start at the top of the table and read each record in turn until we get to Mr. Brown. Without an index, this is exactly what the DBMS will do when the search is requested.

When you create a table index, you are creating and maintaining a sorted index of the information within the table. So, if you create an alphabetical index of customers for your customer table, a sorted list of customer names is created, which will reference the original record in the table. This means a search for Mr. Brown can now use the index to quickly find the correct record number. Exactly how the index is used will depend on the DBMS internal query engine (or optimizer).

Modern computers are so fast that you will not notice any speed difference in this retrieval if you have few records in your table, but for large tables the speed improvement can be substantial. For software applications that frequently read from the same large tables, the impact can be quickly magnified. Therefore, table indexes may be pointless on tables that will never have many records and are rarely accessed, but can be absolutely essential on large, frequently used tables.

It is important to note that indexes have to be maintained too. When a table with an index has a new record added, the new data is appended to the bottom of the table and the index is re-sequenced to include the new data. In some cases, where there are many indexes and frequent changes impacting them, the extra work that has to be done in continually re-sequencing the indexes may outweigh the performance benefit when reading data. The DBMS may allow the database administrator to specify whether indexes are updated with every change or on some less frequent basis.

To know whether an index should or should not be created requires an understanding of the application(s) that will use the data. It is not unusual for additional indexes to be created after the application is built and tested in order to address a performance issue. However, as a rough guideline, it is usually sensible to create indexes for the primary keys of all large, non-static files.

Learn more about this author, Robert Laws.
Click here to send this author comments or questions.