Introduction to Indexes in database

Q: What are indexes?
Indexes are the special tables in the database that makes the search faster.
The users cannot see the indexes, they are just used to speed up searches/queries.
Q: How indexes reads the data faster?
Indexes allow to find data faster without reading the whole table as it stores entries in sorted order.
Database maintains index just like as index present in the book. Index hold the index key and reference which points to the table row. Indexes use B-tree(Balanced Search Tree) data structure to make the search faster with minimum amount of disk reads.

Index is assign to Primary key as soon as you creates a table with primary key. These are called Implicit Indexes

Note: If you assign a primary key to a column then index is automatically assign to it.
Some kind of an index is required to police the PRIMARY KEY, otherwise it would require scanning the whole table on each insert (to ensure uniqueness).

Explicit Indexes
You can also assign index to a column while creating table using following query

Once the table is created, you can assign index using following query

To drop index

But be careful while using indexes. Inappropriate use of indexes may lead to performance degradation.
See how to use indexes to get better performance

1 thought on “Introduction to Indexes in database

Leave a Reply

Contact Us
close slider
Contact Us

How can we help you with?