CODEWITHSUNDEEP
djangodjango-modelsdjango-orm
Arbazz Hussain
Arbazz Hussain

Reputation: 1932

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

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 ?

Upvotes: 35

Views: 28329

Answers (3)

Islam Murtazaev
Islam Murtazaev

Reputation: 1778

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 table that is often queried by non-primary field.

Upvotes: 13

Benji
Benji

Reputation: 458

I would say you should it when you have a field that is unique for fast lookups.

e.g., You have a table of customers with manyusers. Each row representing a user will have their own unique user_id. When you create an index, a pointer is created within the DB to where that data is stored so that the next look up won't take as long. Have a look here to learn more

Upvotes: 28

Gulshan Prajapati
Gulshan Prajapati

Reputation: 983

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

Upvotes: 7

Related Questions