Database Indexes

Rakesh singhania
3 min readOct 10, 2023

Lets understand index by a simple example.

Imagine you have a big collection of cars , and you want to find a specific car , let’s say a Red Ferrari from this collection.

Photo by Ivana Cajina on Unsplash

You could go through each car , one by one, until you find your Ferrari . This might take a while, especially if you have a lot of cars !

Now, think about if you had a special list that tells you exactly where to find each type of car . For example,

“The Ferrari cars are in lane 3.”

That list would be like an index.

In a database, an index is like that special list for finding things quickly. It’s a way to organize information so that the computer can find what you’re looking for much faster.

Here’s how it works

When you search your data without index it will go through all the rows 1 by 1 to retrieve your result.

Query :

SELECT * FROM cars WHERE name = 'Ferrari ';

Internal processing on table

But with Index when you make a query in a database (like asking for specific information), the database looks at the index first. The index guides the database to the right place, so it can retrieve the information you want without having to search through all the data.

So,

An index in a database is like a high-speed navigation system that helps the database find information quickly.

Especially important when dealing with large amounts of data. It’s a powerful tool for optimizing the speed of queries and operations in databases.

By using an index, the database minimizes the amount of disk Input/Output operations needed. This leads to quicker query execution because it doesn’t have to read through all the data.

Let’s use a simple analogy to demonstrate indexing in a database and with code.

--

--

Rakesh singhania

As a student of technology, each day I take a single step forward on the path of learning.