Indexes

  • A database index is a data structure that is used to improve the performance of database queries.
  • An index allows the database to quickly locate and retrieve data without having to search through the entire table.
  • Think of a database index as being similar to the index in the back of a book. Just as the index in a book allows you to quickly locate a specific page or topic, a database index allows you to quickly locate a specific record in a table.
  • The users cannot see the indexes, they are just used to speed up searches/queries.
  • A SQL index is a quick lookup table for finding records users need to search frequently. An index is small, fast, and optimized for quick lookups. It is very useful for connecting the relational tables and searching large tables.
  • Types of indexes:
    • B-tree index: based on a balanced tree data structure, efficient lookups and insertions
    • Hash-table index: based on hash-table, great for lookups, but no efficient for insertions and updates (because of rehashing). Bad for BETWEEN, ORDER BY operations.
    • Composite index: index that is based on multiple columns. Good for BETWEEN, ORDER BY operations. Cons: overhead in insert and update, and more space is used if column is large
    • Clustered index: determines the physical order of the rows in a table. Data is sorted. In PostgreSQL only 1 such index is allowed.
    • Non-clustered index: data rows are stored in a separate structure from the index, and the index contains pointers to the rows in the table.
      • Used to improve the performance of queries that look up specific rows in a table, to quickly locate the rows without having to scan the entire table.
      CREATE INDEX index_name
ON table_name (column1, column2, ...);

CREATE INDEX idx_lastname
ON Persons (LastName);

Indexing strategy guidelines

  • Avoid indexing highly used table/columns – The more indexes on a table the bigger the effect will be on a performance of Insert, Update, Delete, and Merge statements because all indexes must be modified appropriately. This means that SQL Server will have to do page splitting, move data around, and it will have to do that for all affected indexes by those DML statements.
  • Use narrow index keys whenever possible – Keep indexes narrow, that is, with as few columns as possible. Exact numeric keys are the most efficient SQL index keys (e.g. integers). These keys require less disk space and maintenance overhead.
  • Use clustered indexes on unique columns – Consider columns that are unique or contain many distinct values and avoid them for columns that undergo frequent changes.
  • Non-clustered indexes on columns that are frequently searched and/or joined on – Ensure that non-clustered indexes are put on foreign keys and columns frequently used in search conditions, such as Where clause that returns exact matches.
  • Cover SQL indexes for big performance gains – Improvements are attained when the index holds all columns in the query.
SuperMade with Super