Concept of Indexing

Article created 2005-01-11 by Tayyab Arif.

What are indexes?

An index

• Is sorted by key values, (that need not be the same as those of the table)
• Is small and has just a few columns of the table.
• Refers for a key value to the right block within the table.
• Speeds up reading a row, when you know the right search arguments.

Why we need indexes?

For large tables an index can improve performance. An index can

1. Make rows unique using single or compound keys
2. Speed up search actions with

• Data or key values
• Foreign keys. This kind of indexes will especially speed up joins.
• Combination of keys, foreign keys and data values.
  

Types of indexes

Unique Index

A unique index is one in which no two rows are permitted to have the same index value.

For example, if you create a unique index on the employee's last name (lname) in the employee table, then no two employees can share the same last name.

Primary Key Index

A database table often has a column or combination of columns whose value uniquely identifies each row in the table. This column is called the primary key of the table. Defining a primary key for a table in a database diagram automatically creates a primary key index that is a specific type of unique index. This index requires each value in the primary key to be unique. It also permits fast access to data when you use the primary key index in queries.

Clustered Index

In a clustered index, the physical order of the rows in the table is the same as the logical (indexed) order of the key values. A table can contain only one clustered index.

If an index is not clustered, the physical order of the rows in the table does not match the logical order of the key values. A clustered index usually provides faster access to data than does a nonclustered index.

Cluster indexing concept

An overview of indexing related to our database design

The SystemEvents table in the database is of the main concern here. Indexes introduce paging within the database which improves the performance. Clustered indexing normally clusters the similar values in a column of the database. That is, if we apply index on ‘fromhost’, it shall cluster all the records closely having ‘fromhost’ value equals workstation1, workstation2 and so on. Indexes introduce their own paging mechanism and are handled by the database.

This diagram below shall further clarify:

Clustered indexing introduces intermediate pages on which direct pointers are assigned the pages where the actual data exists. For example, we make a simple 2 level paging with no intermediate level. See Fig. 1 below:

Level 1: Pointer pages
Level 2: Data pages

Figure 1

All the similar ‘fromhost’ values are grouped together on minimum pages so that access time is considerably reduced. The indexes now know where and on which page to locate the ‘fromhost’ value exactly. It would not have to traverse the whole table.

Note: