ExampleAn application frequently looks up customers by their mobile phone number. To improve performance, developers configure an index for the field MOBILE_PHONE_NUMBER on the CUSTOMER table. The database sets up a data structure that stores all MOBILE_PHONE_NUMBER sorted numerically with pointers to the rows associated with each field. As the index is sorted, it can be searched quickly resulting in faster retrieval of CUSTOMER records.
Performance TradeoffsIndexes consume resources such as memory and disk space. They potentially reduce data access times and disk I/O. It is common to index primary keys. It is also common to index any fields or set of fields that are often queried. Indexes need to be maintained so indexing transactional tables that are updated more than queried can negatively impact performance.
|Overview: Database Index|
An optional data structure that can be created for a column or list of columns in a database table to speed data access.