CREATE INDEX SQL

Using an index is a way to speed up frequent searches through data bases. Indexes can be created on most columns associated with a table except those containing large objects such as images or categorized as text. Indexes work in a tree structure with several levels. Each succeeding level leads more closely to the desired record. In a tree structure with 5 levels, any search would only be required to look at a few "nodes" instead of searching through a column that has not been indexed which may have to cycle its way through thousands and even millions of cells.

While an index speeds up search, it slows down UPDATEs which must update the actual record as well as the appropriate nodes in the index. It should also be noted that an index consumes space.

The CREATE INDEX statement sets up an index for a column of a table.

CREATE INDEX name_of_index
ON table(column)

To create an index where duplicates are not allowed is similar:

CREATE UNIQUE INDEX name_of_index
ON table (column)

When a table is queried and an indexed column appears in the WHERE clause, the index is automatically used if possible. There are times when all of the cells of the column will have to be cycled through. This is when a search does not conform with an hierarchical structure. An example of this is when the query contains a "not equal" (!= or <>) operator.

< CHECK Constraint SQL | CREATE TABLE Statement >


Send notes in disappearing ink!

Interesting Pages