Database Indexes, please be careful when using it!
Tags: database, sql
Published: 2022-04-06 05:28:52

---
title: Database Indexes, please be careful when using it!
published: true
description: A short explanation about usage of database index
tags: database, sql
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vc4y3j6parcx9bp4bauk.png
---

Lets do disclaimer before we start 😉.
> This is my current understanding of Database Index.
> If there is any mistake that write that lead to misunderstanding, please let me know and I will update this post ASAP.

Let's begin 🚀

## What is database index?
Database index is a built-in tools that will help us for searching data inside database. Index will quickly locate record of our queries.

## The problem
To help to understand, I will make up some use case. Example, we have a **users** table that have `id, full_name & gender`

![users table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gmeli90ltacf4z53tlel.png)

That **users** table will have 1000 rows. 2 of them is **male** and the rest (998) of them is **female**.
Say that we need to find our **male user**, we will create query something like this `SELECT * FROM users WHERE gender = 'male'`.
After we executed that query, we will have result of 2 users.

Behind the scene, database engine will **search all of 1000 rows to find that 2 rows**.
Yes, that sound inefficient. But that is how database engine works. 😵

## What is the solution?
The solution is to use Database Index. 💡
With index, database engine will make some of algorithm that map our table based on the index we chose.

Okay, so we create an index on `gender` column inside **users** table.
Back again to our query, now if we execute `SELECT * FROM users WHERE gender = 'male'`, we will get 2 users again as result (of course!).

The difference is, right now database engine will **search on 2 rows to find that 2 rows**.
Now that sound efficient❗

## But wait...
After we know that, maybe there is a thinking like this:
> "let's create index for all of our column, so we can get quick result every time we search something 🤔"

Thats right, you will get a quick query every search.
But please don't over-used it. Even though Index have upside, Index also have downside.

If we create an index, behind the scene database engine will write additional data to our database.

Lets back again to our users table. The calculation is like this:
```
2 index (1 for ID, 1 for gender) * 1000 rows = 2000
# PK is always get index
```

Imagine if we put index to all of our column
```
4 index (ID, gender, full_name, created_at) * 1000 rows = 4000
# Thats only 4 columns.
# The more column we create, the more usage it will take when CREATE, UPDATE, DELETE, etc.
```

the conclusion is: please use it wisely. 😇

## Bonus: How to create index
- PostgreSql
```sql
CREATE TABLE "customers" (
  "id" SERIAL PRIMARY KEY,
  "gender" varchar,
  "full_name" varchar,
  "created_at" timestamp
);

CREATE INDEX ON "customers" ("gender");
```

- MySql
```sql
CREATE TABLE `customers` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `gender` varchar(255),
  `full_name` varchar(255),
  `created_at` timestamp
);

CREATE INDEX `customers_index_0` ON `customers` (`gender`);
```