Defining Indexes
Go Up to Database Design Principles
Proper use of indexes is an important factor in database performance. Effective policies for defining and maintaining indexes can be the key to a very high performance client/server system. The self-tuning nature of indexes in InterBase greatly benefits performance, but you can gain some additional benefit by periodic maintenance tasks.
Contents
What is an Index?
An index in InterBase is a Balanced-Tree data structure stored inside the database file that provides a quick lookup mechanism for the location of specific values in a table. Queries make use of appropriate indexes automatically by means of the cost-based optimizer, which analyzes the tables and columns used in a given query and chooses indexes that speed up the searching, sorting, or joining operations.
Defining indexes for some columns is part of designing a production database. Indexes dramatically improve performance of SELECT queries. The greater the number of rows in the table, the greater the benefit of using an index. Intelligently analyzing your database and defining indexes appropriately always improves performance.
Indexes incur a small cost to maintain the index B-tree data structure during INSERT and UPDATE operations. Because of this cost, it is not recommended to be overly liberal with index definitions. Do not create redundant indexes, and do not make an index on every column as a substitute for database usage analysis.
You should not define an index for columns that have few distinct data values. For example, a column FISCAL_QUARTER might have only four distinct values over a potentially very large data set. An index does not provide much benefit for retrieval of data with this kind of distribution of values, and the work required to maintain the index tree might outweigh the benefits.
What Queries Use an Index?
InterBase uses indexes to speed up data fetching for the following query elements:
- Primary and foreign keys
- Join keys
- Sort keys, including
DISTINCTandGROUP BY - Search criteria (
WHERE)
In general, you should define indexes on all columns that you use in JOIN criteria or as sorting keys in an ORDER BY clause. You do not have to define indexes on primary or foreign key columns, because these table constraints implicitly create indexes.
What Queries Don’t Use Indexes?
InterBase does not employ an index in the following operations, even if an index exists for the specified columns:
- Search criteria for
CONTAINING,LIKE, and < > inequality operations - Columns used in aggregate functions, like
COUNT() - Other expressions, like
UPPER()
Directional Indexes
Indexes are defined as either ASCENDING or DESCENDING. To sort in both directions, you need one index of each type. This is also very important if you are using a scrolling list in a Delphi form, or when using the TTable.Last method.