TurboDB Engine Documentation

Secondary Indexes

Previous  Top  Next

Additional indexes for database tables can accelerate queries and filters by some orders of magnitude. Consider a simple query like:

select * from Customers where State = 'NJ'

or the similar filter condition

State = 'NJ'

Without an index, TurboDB has to scan every record in the table to select those, which satisfy the condition. And while TurboDB is optimizing multiple reads quite well, the operation nevertheless can take up to some minutes, if the table is large (a few million records).

If however there is an index, which starts with the State column, calculation the result of this selection is instantaneous, because TurboDB will be able to directly sort out the correct rows.

Also with joins, an additional index can do wonders. Look a this query:

select * from Customers, Orders where Orders.CustNo = Customers.No

or the equivalent

select * from Customers join Orders on Orders.CustNo = Customers.No

Also in this case, an index over Orders.CustNo or Customers.No will speed the query considerably. Depending on which one exists, TurboDB will execute the statement such that it can be used. However, since Orders will be a much larger table than Customers (the average number or orders per customer should be greater than one), an index over the Orders.CustNo field will bring you more in terms of execution time gain than an index over Customers.No. (The latter will most  probably exist nevertheless, because No tends to be the primary key for the Customers table.)

The disadvantage with indexes is that their maintenance takes time during the change operations delete, insert and update, which must be taken into account as well, when regarding the overall performance of your application. Because in most applications queries are much more frequent than changes, indexes for crucial use cases will pay off most of the time.