Index

INDEX

It is a db object used to retrieve data in a quickest possible way.

It is used to help search and make a query run fast for an indexed table.

Every table is not indexed because:

• It increases complexity
• It requires extra memory area
  

There can be multiple indexes on a single table:

• It do not identify records uniquely
• It do not suppresses redundancy

TYPEs of INDEX:

1. Dense Index: it is a file with pairs of keys and pointers for every record in the data file.
2. Every key in this file is associated with a particular pointer to a record in the stored data file.
3. Sparse Index: It is a file with pairs of keys and pointers for every block in the data file.
4. Every key in this file is associated with a particular pointer to a block in the stored data file.
5. Reverse Index: It reverse the key value before entering it in the index. eg: 245 becomes 542 in this index.

How to create Index?

CREATE INDEX index_name ON table_name (column_name) USING BTree/HASH;

 //alter index

ALTER TABLE table_name ADD INDEX index_name (column_name) USING BTree/ HASH;

 //show index

SHOW INDEX FROM table_name; // to find whether table is indexed or not.

//delete index

DROP INDEX index_name ON table_name;

NOTE: it can be applied on NULL as well as NOT NULL.