A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.
Indexes are created using a few database columns.
The first column is the Search key that contains a copy of the primary key or candidate key of the table. These values are stored in sorted order so that the corresponding data can be accessed quickly.
Note: The data may or may not be stored in sorted order.
The second column is the Data Reference or Pointer which contains a set of pointers holding the address of the disk block where that particular key value can be found.
Two primary methods of constructing indices are clustered and non-clustered:
A non-clustered index (or regular b-tree index) is an index where the order of the rows does not match the physical order of the actual data. It is instead ordered by the columns that make up the index. In a non-clustered index, the leaf pages of the index do not contain any actual data, but instead contain pointers to the actual data
A clustered index is a special index which physically orders the data according to the indexed columns. The leaf nodes of the index store the data for the rest of the columns in the table so when a lookup is performed on this type of index there are no other structures that need to be referenced.
Some other key facts to remember about indexes are:
Indexes take additional disk space.
indexes slow down INSERT,UPDATE and DELETE, but will speed up UPDATE if the WHERE condition has an indexed field. INSERT, UPDATE and DELETE becomes slower because on each operation the indexes must also be updated.
Suppose a database contains N data items and one must be retrieved based on the value of one of the fields. A simple implementation retrieves and examines each item according to the test. If there is only one matching item, this can stop when it finds that single item, but if there are multiple matches, it must test everything. This means that the number of operations in the worst case is O(N) or linear time. Since databases may contain many objects, and since lookup is a common operation, it is often desirable to improve performance.
Indexing is an easy way to improve the performance of our databases
Hopefully this brief look into indexing helped to clear up the concept.
Top comments (0)