Index in Database

What is an index?

An index is a data structure that stores the values for a specific column in a table. An index is created on a column of a table. So, the key points to remember are that an index consists of column values from one table, and that those values are stored in a data structure.The whole point of having an index is to speed up search queries by essentially cutting down the number of records/rows in a table that need to be examined.
Suppose that we have a database table called Employee with three columns – Employee_Name, Employee_Age, and Employee_Address. Assume that the Employee table has thousands of rows.

Now, let’s say that we want to run a query to find all the details of any employees who are named ‘Jesus’? So, we decide to run a simple query like this:

SELECT * FROM Employee 
WHERE Employee_Name = 'Jesus'

What would happen without an index on the table?
Once we run that query, what exactly goes on behind the scenes to find employees who are named Jesus? Well, the database software would literally have to look at every single row in the Employee table to see if the Employee_Name for that row is ‘Jesus’. And, because we want every row with the name ‘Jesus’ inside it, we can not just stop looking once we find just one row with the name ‘Jesus’, because there could be other rows with the name Jesus. So, every row up until the last row must be searched – which means thousands of rows in this scenario will have to be examined by the database to find the rows with the name ‘Jesus’. This is what is called a full table scan.

How does an index improve performance?
Let’s say that we create a B- tree index on the Employee_Name column This means that when we search for employees named “Jesus” using the SQL we showed earlier, then the entire Employee table does not have to be searched to find employees named “Jesus”. Instead, the database will use the index to find employees named Jesus, because the index will presumably be sorted alphabetically by the Employee’s name. And, because it is sorted, it means searching for a name is a lot faster because all names starting with a “J” will be right next to each other in the index!

How to create an index in SQL:

Here’s what the actual SQL would look like to create an index on the Employee_Name column from our example earlier:

CREATE INDEX name_index
ON Employee (Employee_Name)

What kind of data structure is an index?

B- trees are the most commonly used data structures for indexes. The reason B- trees are the most popular data structure for indexes is because of the fact that they time efficient – because look-ups, deletions, and insertions can all be done in logarithmic time. And, another major reason B- trees are more commonly used is because the data that is stored inside the B- tree can be sorted. But, which data structure is actually used for index is determined by the RDBMS.


Q.What are the tradeoffs with having indexes?

1.Faster selects, slower updates.
2.Extra storage space to store indexes. Updates are slower because in addition to updating the table you have to update the index.