How Database Indexes Work

Database indexing features are very important, and you'll see that although the implementation is often complex, the concept itself is very simple and easy to understand!

Note that this concept applies to both SQL and NoSQL databases, but we'll use SQL examples to illustrate the article!

Let's take a very simple query, say all users whose job is web development, which would give us this:

SELECT firstname, lastname, email
FROM User
WHERE job = 'web_developer';

When the database searches for the result of this query, the system will have to go through ALL the rows of the table in question to find every occurrence.

If our software is a carpooling site for example, then searching for users by their job isn't really very common, which means that with a lot of users, the query might take a long time to execute, but it will only run once in a while.

Now, if our software is a professional social network, then this query will be executed thousands of times a day, and the greater the number of users, the more potentially blocking it could be for our system...

In short: Executed 10,000 times a day, on 100,000 users, that's 1 billion rows scanned, just for this query...

Needless to say, in terms of resources, the consumption of this simple query is not something to overlook... But fortunately, indexes are here to save us!

What is an index?

It's not for nothing that the word "index" is used in literature, since it's a concept invented hundreds of years ago to make it easier to find information in encyclopedias.

In a book, an index lets you find all the pages related to a specific important word.

In the example below, we can see that pages 21 to 24 talk about the topic "animated cartoons".

It's easy to understand that here, the index lets us avoid having to go through every page of the book (and every word), looking for a particular subject.

It's the same thing in a database!

Each index added to our database is actually an additional, very simple table containing each value to be indexed, along with a pointer to the rows containing that value.

Here's an example of an index for users' professions:

Going back to our previous calculation, and assuming that 10% (10,000) of users are web developers, and that there are 50 different professions, the result is as follows:

Executed 10,000 times a day, on 10,000 indexed users, that's 100,000,000 rows scanned, which is 10 times more efficient...

But then, are indexes a miracle solution that can solve all our problems? No, because as with everything, there are advantages and disadvantages!

Advantages and disadvantages

As mentioned earlier, an index is an additional table that will contain a row for each indexed piece of data (for example, for each different job).

So the greater the number of unique values, the heavier the index will be and the potentially slower it will become...

On top of that, these indexes have to be updated every time data related to the index is added, deleted, or modified.

So is it really a performance gain?

The simple answer is: A good index will make your database much more efficient, a bad index will make it slower!

The more detailed answer is: Creating an index means making a compromise (or "trade-off"). You're trading a bit of performance when updating data for a big gain when reading that same data.

If the data is updated more often than it is read, then the index is poorly chosen... It's common practice to design new indexes as the database evolves over time and based on the analysis of query performance, so as not to make mistakes and end up with poor, premature optimization!