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!

Answer 1

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.