Tech moves fast! Stay ahead of the curve with Techopedia!
Join nearly 200,000 subscribers who receive actionable tech insights from Techopedia.
In database systems, an index (IDX) is a data structure defined on columns in a database table to significantly speed up data retrieval operations. An index is a small copy of a database table sorted by key values. Without an index, query languages like SQL may have to scan the entire table from top to bottom to choose relevant rows.
An index on a database table must be created with care, as indexes consume space and are stored in the database. An index can also cause write operations to take more time, as the index must be updated when the underlying table is updated.
The different data structures used to create indexes are B+ trees, balanced trees and hashes. Using these data structures, a scan on the entire database table is relatively easy.
Indexes can also be created on functions or expressions. A unique index prevents duplicate entries in the index and the related table, thus acting as a constraint. Column order is also an important aspect of indexing. The first indexed column always has a priority; row identifiers are retrieved based on the first indexed columns. In a normal scenario, indexes do not return a value but only locate records.
There are generally two types of index architecture: