Why Indexes?

Basic use of index is to find data in a table more quickly by speeding up the query searches. Consider an employees table with 1 million records. If I query the table to extract data for 1 employee based on emp_id, all the records are compared for the emp_id. This approach will be very time taking in real time when many queries are searching for records. Thus we should use indexes to speed up the searching process.

Syntax for creating Index:

CREATE INDEX index_name ON table_name(col_name);

Syntax for unique index(duplicate values are not allowed):

CREATE UNIQUE INDEX index_name ON table_name(col_name);

A simple example:

CREATE INDEX idx_name ON employees(emp_firstname, emp_lastname);

Syntax for dropping index:

DROP INDEX index_name;

Quick tip

Always create an index on a column that is not frequently updated as updating the column will also update the index, hence will take more time. 

Data Structures used for Indexes
The most common type of data structure used for indexes is a B-tree. The main reason for this is that the values can be sorted inside a B-tree. Also, the searches and updations can be done in logarithmic time.

What is stored in an Index?
An index stores the column values on which it's created, as well as a pointer to the row containing that value. This pointer is nothing but the address of the row on disk. In order to fetch the other values in the row corresponding to a column value, we would need the pointer to point us to that specific row. 

Disadvantages of creating Indexes
An index takes disk space. It needs to be updated whenever any changes are made to the underlying columns. So, it should be created only when the column needs to be frequently queried and less updated.

Disclaimer - Views expressed in this blog are author's own and do not necessarily represents the policies of aclnz.com