Django models: are there reasons to make searched field with db_index?

Question

Supose there is table UserProfile:

class UserProfile(models.Model):
    name = models.CharField(max_length=30, db_index=True)     # db_index 1
    email = models.EmailField(unique=True, db_index=True)     # db_index 2
    password = models.CharField(max_length=60)
    birthday = models.DateField(db_index=True)                # db_index 3
    about = models.TextField(blank=True)
    created = models.DateField(auto_now_add=True)
    lang = models.CharField(max_length=1, choices=LANG, blank=True)

On the site there is search form with such filters: name, age, email.

So, are there real reasons to use db_index in these filters?

Thanks!

score 3 · Accepted Answer · edited Nov 25 '15 at 07:54

3

Yes, of course. Django uses the database, so if you're searching on those fields the lookup will benefit from a database index.

Note that once you've created your table, syncdb won't add indexes even if you run it again after adding db_index to the definition - you'll need to modify the table definition directly in the database shell, or use a tool like South.

Keep in mind, like most problems of scale, these only apply if you have a statistically large number of rows (10,000 is not large).

Additionally, every time you do an insert, indexes need to be updated. So be careful on which column you add indexes.

edited Nov 25 '15 at 07:54

praveen

105
2
8

answered Dec 16 '11 at 19:23

Daniel Roseman

588,541
66
880
895

prior to Django 1.5 when you could only do indexes on individual fields, what is the behavior when you do qs.filter(field1=foo, field2=2), assuming you have both fields indexed. Index used? – Pier1 Sys Jan 18 '13 at 13:55

Django models: are there reasons to make searched field with db_index?

1 Answers1