Database Indexes: How They Work and their Main Types

So index is an external structure inside your database.

It's different from tables, views, functions.

And this structure is usually related to a specific table.

you can literally see it as a table of contents in a book so it helps you to find data inside your table faster like much faster so let's imagine you just have a regular table with no indexes and you want to find all the data for a specific id and you write a query like okay give me everything from this table where id equals some value so database management system has to scan all the table to be able to find this ID.

But index is built using specific data structures which are different from tables.

And this structure helps you to find this value in a much faster time.

So what are the benefits and downsides of using indexes? The most obvious benefit is that developers create indexes, so it makes it much faster to run the queries when you want to find some data or filter it somehow.

so the performance in read speed can be considerable and very noticeable when you use indexes it's also really important not only which indexes do you create but also which queries then you write because even if you have an index it's not necessarily will be used if you don't write the queries which will use those indexes I will cover it a bit later But actually, it doesn't go without any drawbacks.

It's really important to know here because firstly, indexes have to be maintained because it's usually an external structure.

So every time the data is updated or changed or deleted in the table, then the index, which is a table of contents, you can say, for this table has to be also updated because it has to be up to date with the table it's built on.

This is really important because especially if you have a lot of indexes or several indexes on a single table, it can noticeably decrease the performance of update queries or delete queries because every time you change the data in your table, you have to update an index.

But it's also important to know indexes are built in such a way it's not necessarily that they have to be updated every time.

Yeah, there is like a buffer, so to say, like an additional space.

in the way they're built.

The parameter, which is called field factor, defines how many space inside the page is actually filled with data inside the index.

It is performance tuning for indexes kind of thing.

But you just have to know there is a parameter which is called fill factor.

And if you have a fill factor which is like 100%, indexes will fill all the space they have in all the pages.

So every change, every addition of the data will have to rebuild this index.

Bigger fill factors are usually used if you're mostly reading your data.

But if you have to update it often, so fill factor should be lower, you will have an additional space.

So you don't have to rebuild your index right away, so it will be updated not fully, so to say.

Yeah, so just for you to know, there is a thing called fill factor.

And another downside, which is also really important, and in my experience I had some cases when this downside was really noticeable, is that indexes usually take an additional space, and often it can take a considerable space, which is comparable with the space a table itself takes, if you have several indexes.

So you can have a table which is, let's say, 50 gigabytes big, and then if you have indexes on several fields of this table, I don't know, let's say, four or five indexes, Those five indexes can take the same amount of space, like 50 gigabytes.