Indexes
- Indexes are data structures used for faster retrieval of data.
- Index is made up of two parts: first ,Reference/ address of the indexed column .Second ,The Indexed column of the table and its value
- Create Index Syntax: CREATE INDEX ON <TABLENAME> <COLLUMN NAME>
- Drop Index Syntax: DROP INDEX <INDEX NAME>
Understanding Indexes :
To understand what are Indexes, let’s take a very common example of Index at the back of a book. In Book, Index displays the reference page number for each topic.For finding a particular topic , we just see the page number for that topic in the index and then can directly jump to that page number instead of searching whole book, thus Index act as pointer to the topic in book. Similarly, assuming table to be a book, topic to be a column value and page number to be a reference , we have Indexes in database working the same way and pointing to data in the table.
Defining Indexes :
Indexes can be defined as special ‘Look-up tables or Data Structures ‘ that act as pointers and points to the column in the table been indexed. Thus it helps in fast retrieval of data and improves efficiency .
Structure of an Index :
Index is composed of two parts:
First, it stores the references / address of the columns in the table on which index is created.
Second , it stores the column and its values on which index has been created.
Key-Facts about Indexes :
- Indexes are not visible to the users.
- Indexes improve the efficiency of Select queries but slows down the Insertion / Updation process, because updating a table with indexes takes more time than updating a table without indexes as along with table indexes also needs to be updated.
- Indexes are independent of the data in the table, Hence Creating / Dropping an index has no affect on base tables or other indexes.
- Indexes occupies a separate cache or storage space in the database as it is independent of the table.
- Database automatically updates the indexes in case of any insertion / deletion/updation
- Indexes are automatically created by database for columns having primary key constraint and Unique key constraint.
Simple Index :
Index created on single column is called Single Column Index
Syntax : CREATE INDEX <Index Name> ON <Table Name> <Column Name>
Composite Index :
Index can also be created on multiple columns called Composite Index
Syntax :CREATE INDEX <Index Name> ON <Table Name> (<Column 1, Column 2>)
Implicit Index :
