36

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 ?

Improve this question
2
  • 2
    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 Commented Jan 5, 2020 at 1:02
  • A very similar question: Add Indexes db_index=True Commented May 28, 2020 at 21:28

4 Answers 4

Reset to default
30

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Most databases add an index for foreign keys automatically anyway
In other words you use db_index=True if you are going to use Entity.objects.get(some_field=some_value) .
14

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.

Improve this answer

Comments

7

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

Improve this answer

1 Comment

You dont have to specify db_index=True when unique=True. Take a look at docs.djangoproject.com/en/3.2/ref/models/fields/#unique
0

Old question, but still not enough answers...

You should use indexes only for fields that are used to filter results.

For example:

my_data = My_Model.objects.filter(color="blue")

In this case, the "color" field can benefit from an index.

In RDBMSs like PostgreSQL, fields with unique=True are automatically indexed.

Also, as I understand it, boolean fields usually should not be indexed, since doing so can actually worsen performance rather than improve it.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.