What to Index - Firebird

The length of time it takes to search a whole table is directly proportional to the number of rows in the table. An index on a column can mean the difference between an immediate response to a query and a long wait. So, why not index every column?

The main drawbacks are that indexes consume additional disk space, and inserting, deleting, and updating rows takes longer on indexed columns than on non-indexed columns. The index must be updated each time a data item in the indexed column changes and each time a row is added to or deleted from the table.

Nevertheless, the boost in performance for data retrieval usually outweighs the overhead of maintaining a conservative but useful collection of indexes. You should create an index on a column when

  • Search conditions frequently reference the column. An index will help in date and numeric searches when direct comparisons or BETWEEN evaluations are wanted. Search indexes for character columns are useful when strings are to be evaluated for exact matches or against STARTING WITH and CONTAINING predicates. They are not useful with the LIKE predicate.
  • The column does not carry an integrity constraint but is referenced frequently as a JOIN condition.
  • ORDER BY clauses frequently use the column to sort data. When sets must be ordered on multiple columns, composite indexes that reflect the output order specified in ORDER BY clauses can improve retrieval speed.
  • You need an index with special characteristics not provided by data or existing indexes, such as a non-binary collation or an ascending or descending direction.
  • Aggregations are to be performed on large sets. Single-column or suitably ordered complex indexes can improve the speed with which aggregations are formed in complex GROUP BY clauses.

    You should not create indexes for columns that

  • Are seldom referenced in search conditions
  • Are frequently updated non-key values, such as timestampers or user signatures
  • Have a small number of possible or actual values spread over a wide campus of rows
  • Are styled as two- or three-phase Boolean

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics