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

CREATE table table_name(
id int,
firstname varchar(50)
lastname varchar(50), INDEX(id)

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

CREATE INDEX index_name ON Table_name(column_name);

To drop index

ALTER TABLE table_name DROP INDEX keywords;

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