Indexes and Performance

Prelude: The article Scan vs Seek gives a quick preview into performance considerations of queries.

Performance of a database can be boosted using several methods. Building an index is the first and foremost action performed by developers and administrators. Indexing data is crucial in order to leverage speed of data retrieval of a database.

Indexes are similar to table of contents in a book that helps the reader to jump directly to a specific topic. In database parlance, the actual segment/block where data resides is referred to in an Index. Similar to a book that organizes contents with chapters, pages and paragraphs, a database management system also manages the data using pages and data blocks.

Essentially, an Index is a mini table that is organized (i.e. sorted) with list of pointers to actual block location of data records in the database. Indexes help in a Seek action while fetching data by directly locating the actual data record. Without indexes, queries will invariably result in a full table Scan, which is the worst action in performance and has to be avoided at all costs. Scanning an index is several times faster than the entire table, but seek operations are the fastest in terms of performance.

Indexes are database objects and have to be created, adjusted and maintained regularly.

  • An index is automatically created when a table is created with primary key
  • Number of indices that can be created on a table is not restricted.
    • Due to performance reasons, the number is maintained as minimal as possible.
    • Multiple indices will occupy memory as well as be used by the system to perform search even when it is not required.
  • Index can be created on any data type. Indexes on numeric indexes are usually faster in performance but text search algorithms have made huge gains in performance now
  • One or more fields grouped can be used to create index i.e. composite index
  • There are different types of indexes Bitmap, B-Tree, Unique, Covering, Hashed, Text, Full search etc.
    • Cardinality of the data on which index is built affects the type of index to choose
    • Different SQL operations use different index types effectively e.g. LIKE pattern is best used in B-Tree whereas operators like =,>,< are effective with hash indexes
  • How to know whether indices are used?
    • Analyze query execution plans frequently
    • Update database statistics frequently

Indexes have become necessity in all Relational environments today for both OLTP and OLAP types of applications. Indexes are not restricted to structured data alone, they are heavily used in unstructured data as well (e.g. MongoDB). Indexes have become the de-facto object to be fine tuned when it comes to performance tuning.

Other Related Articles


Comments are closed.