The Use of Indexes in Relational Databases

By definition, an index refers to a structure of data that optimize accessing and searching information. It is similar to an index found at the back of a book. Performance is always a concern when the database starts to grow (Chakraoui, 2016). For this reason, using the least possible time to get directly to a specific row is a priority. Indexes are helpful in the quick location of data without having to search every row in the table of the database. Either one or more columns of a table of the database are used to create indexes. It provides the basis for both efficient access and rapid lookups of ordered records.

In simple terms, an index is said to be a copy of selected data columns from a table that can be searched easily. An index also includes a direct link or low-level disk block address to the complete row of data that is was copied from (Sinha & Winslett, 2007). Normally, some of the databases extend the indexing power by allowing the developers to create indexes on the expressions or functions. For example, it is possible to create an index on “upper,” which helps in storing the versions of the upper case of the last name field.

The use of partial indices is another option that is sometimes supported. In this case, the entries of the index are created only for those records that can satisfy some of the conditional expression (Chakraoui, 2016). Furthermore, the aspect of flexibility is realized by permitting indexing on functions that are user-defined, as well as the expressions that are from the built-in assortment functions.

Addition of an Index

Practically, a table could not benefit from the addition of an index. In general, an addition of an index will tax the DBMS too much regarding its maintenance processing. Such scenario is mainly common when the table has several rows, attributes, and require many updates, inserts, and deletes. One of the measures for determining the need for an index is the column’s data sparsity that requires indexing. Data sparsity is simply the number of various values that a column is possible to have. The knowledge of sparsity is helpful in deciding whether or not the addition of index is appropriate (Chakraoui, 2016). For this reason, a table is not likely to benefit from the addition of an index.

Moreover, a performance of a search in the column with low sparsity increases the chances of reading a high percentage of the rows of the table. An index has to be updated every time a row is deleted or inserted. Therefore, more indexes mean the slower performance for the write operations. Read operations can also be potentially slowed as every index takes up further space of the disc and memory (Sinha & Winslett, 2007).

Trade-Off that is Associated with the use of Index

The creation of an index is a trade-off. One has to determine whether the overhead of its maintenance as well as the additional space that it consumes is worth the performance gain got from its use. In turn, that depends on how critical those queries are and the much their benefit. In this cost-benefit equation, the number of indexes on a table becomes irrelevant (Chakraoui, 2016). Each of the indexes has to be considered on its own.

Again, one must always evaluate the write speed or the requirements of maintenance to read. All the scenarios have to be tested thoroughly on a particular product such as system. However, in some situations, the index adds might slow down the compilation of query/updates considerably. The need of every index has to be justified as the general practice of database design. Such process can only be achieved by examining, in details, the needs of the user reporting. Practically, god database and the analysis of query should be helpful in the exact determination of whether to use an index or not. The type of index required can also be readily determined.

The Reason for not Creating an Index for Every Column

Creating an index for every column is not good as it decreases the insertion performance. For this reason, an index has to be built only if there is a need for it, especially if a column is included in the select queries several times. Even though the creation of the index for every column can improve the memory a lot, the process can also result in the taking up of more allocated space both from the ram and drive (Chakraoui, 2016). When the system reaches its limit of memory, it will have to surrender the space of the drive thus risking the performance. For this reason, it is not always recommended to index any field that does not involve in any process of the algorithm of traversing data, neither searching nor inserting. By default, all fields normally have to be indexed. However, the ones that need not be indexed are the ones where the moderator use queries (Sinha & Winslett, 2007).

References

Chakraoui, M. (2016). TUNING DIFFERENT TYPES OF COMPLEX QUERIES USING THE APPROPRIATE INDEXES IN PARALLEL/DISTRIBUTED DATABASE SYSTEMS. International Journal of Geomate. http://dx.doi.org/10.21660/2016.24.1392

Sinha, R., & Winslett, M. (2007). Multi-resolution bitmap indexes for scientific data. ACM Transactions on Database Systems, 32(3), 16-es. http://dx.doi.org/10.1145/1272743.1272746