When we should use "db_index=True" in Django?

Question

When we should define db_index=True on a model fields ?

I'm trying to optimize the application & I want to learn more about db_index, in which conditions we should use it ?

The documentation says that using db_index=True on model fields is used to speed up the lookups with slightly disadvantages with storage and memory.

Should we use db_index=True only on those fields that have unique values like the primary field id ?
What happens if we enabled indexing for those fields which are not unique and contains repetitive data ?

This is an extremely complicated question. It depends on the database you are using, the data you are storing, how you are querying the data, how many writes/reads you are performing, it goes on... usually it is a case of tweaking things here and there until you get the performance you desire. As a simple rule though: any fields that you will be consistently filtering on will benefit from being indexed — Iain Shelvington, Jan 05 '20 at 01:02
A very similar question: [Add Indexes db_index=True](https://stackoverflow.com/q/14786413/4744341) — natka_m, May 28 '20 at 21:28

Benji · Accepted Answer · 2022-07-12T14:28:44.077

21

I would say you should use db_index=True when you have a field that is unique for faster lookups.

For example, if you a table customers with many records of users they'll each have their own unique user_id. When you create an index, a pointer is created to where that data is stored within your database so that the next look up against that column will give you a much more desirable time of query than say using their first_name or last_name.

Have a look here to learn more about indexing

edited Jul 12 '22 at 14:28

answered Jan 05 '20 at 01:15

Benji

403
5
11

4

Most databases add an index for foreign keys automatically anyway – Iain Shelvington Jan 05 '20 at 01:25
4

In other words you use `db_index=True` if you are going to use `Entity.objects.get(some_field=some_value)` . – chaulap Aug 23 '21 at 12:41

score 4 · Answer 2 · answered Apr 05 '22 at 07:11

You should use db_index=True when you use unique=True, there is a specific reason to use it,

By using this method you can boost a little bit of performance,

When we fire a query in SQL, finding starts from the top to bottom

Case: 'Without db_index=True': It will search and filter till all bottom rows even if we find the data

Case: 'With db_index=True': When Object finds it will just stop their

It will boost a little bit of performance

score 4 · Answer 3 · answered Jul 06 '22 at 06:30

When you set db_index=True on some field, queries based on that field would be much faster O(log(n)) instead O(n). Under the hood, it is usually implemented using B-Tree.

The trade-off for these accelerated queries is increased memory usage and time for writes. So the best use case for indexing would be if you have a read-heavy database that is often quired by non-primary field.

When we should use "db_index=True" in Django?

3 Answers3

Linked