Definition

Indexing is the method used to implement fast access to tuples of a relation, given values of one or more attributes. If we just implemented a search it can be expensive to scan all tuples.

Indexing can be implemented with Hash Tables, but in DBMS, it is always implemented using a Balanced Search Tree called B-Tree.

SQL Syntax

General syntax for creating a index table for another table.

CREATE INDEX <IndexName> ON <table>(<attributes>)

Example:

CREATE INDEX BeerIndex ON Beers(manufactuer)
CREATE INDEX SellIndex ON Sells(bar, beer);

Here, the SellIndex will be used to speed up the search, and the keys will be constructed based on the combination of the attributes bar and beer.

Database Tuning

A major problem with making databases run faster is deciding which indexes to create.

• Pro: An index can speed up queries that use it.
• Con: An index slows down all modifications on its relation because the index must be modified too.

Example: