When we should use db_index=True in Django?

Question

When we should define db_index=True on model fields?

I'm trying to optimize the application & wanted 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 to faster the lookups with slightly disadvantages with storage and memory.

Should we use db_index=True only on those fields which has 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

score 11 · Accepted Answer · answered Jan 05 '20 at 01:15

11

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

For example if you a table customers with records of many users they'll each have their own unique user_id. Each user_id would be unique and having to index this field to find that unique user is more often desirable than say their first_name or last_name. Actually this is also ok but since they're not unique you will get probably recieve multiple results from your queries and this might not be as useful as using an referencing their id.

Have a look here to learn more about indexing

answered Jan 05 '20 at 01:15

Benji

306
4
11

3

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

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 0 · 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

When we should use db_index=True in Django?

2 Answers2