The index is nothing but a data structure that stores the values for a specific column in a table. An index is created on a column of a table.
Example: We have a database table called User
with three columns – Name
, Age
and Address
. Assume that the User
table has thousands of rows.
Now, let’s say that we want to run a query to find all the details of any users who are named 'John'. If we run the following query:
SELECT * FROM User
WHERE Name = 'John'
The database software would literally have to look at every single row in the User
table to see if the Name
for that row is ‘John’. This will take a long time.
This is where index
helps us: index is used to speed up search queries by essentially cutting down the number of records/rows in a table that needs to be examined.
How to create an index:
CREATE INDEX name_index
ON User (Name)
An index
consists of column values(Eg: John) from one table, and those values are stored in a data structure.
So now the database will use the index to find employees named John because the index will presumably be sorted alphabetically by the Users 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!