how to add db_index=True to email field of django auth_user

Question

suddenly, my page got so many users in db that the a filter for email over the auth_user table almost failing because of the extremely big number of users.

Since the table comes built-in, I need to add db_index=True to columns in this table, any idea how to do this?

Answer 1

One quick and easy way would be to manually add the index using RunSQL in a migration.

operations = [
    migrations.RunSQL("CREATE INDEX..."),
]

It's not very elegant. For one thing, the migration will be for a different app (since you don't control the auth migrations). For another, the schema will technically be out of sync with the database. However, I don't think there are any negative consequences in this case, since Django doesn't do anything with db_index other than create the index.

Answer 2

One possibility is to substitute the user model with a custom one, which will have proper indices and any other field that you require. There is extensive documentation on Django docs: Substituting a custom User model on how to achieve this. This is how I did it on a particular case with a similar issue.

Another possibility is to extend the user model, which could have a particular field repeated from the original model, on which there is an index. Disclaimer: I am genuinely against that for obvious reasons, but I have seen this happening, as this approach is easier to code than the first. This would be very bad though if there are many fields.

This is a good question imo. I would love to know if there is another possibility which I miss.

Answer 3

I had the same problem, but with an additional twist--- I already had an index created by South in my table. So if I added a RunSQL("Create index") to my migration, it would add a second index to my database. But at the same time, if I don't include some create index action in the migrations, then I won't be able to properly spin up new databases.

Here's my solution, some python code to check for the existence of an index using some of the private-ish methods in schema_editor

project/appname/migrations/0002.py:

# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import models, migrations
from django.db.migrations import RunPython


def forward__auth_user__email__index(apps, schema_editor):
    auth_user = apps.get_model("auth", "User")
    target_column_to_index = 'email'
    se = schema_editor

    # if there aren't any indexes already, create one.
    index_names = se._constraint_names(auth_user, [target_column_to_index], index=True)
    if len(index_names) == 0:
        se.execute(se._create_index_sql(auth_user, [auth_user._meta.get_field('email')]))

def reverse__auth_user__email__index(apps, schema_editor):
    auth_user = apps.get_model("auth", "User")
    target_column_to_index = 'email'
    se = schema_editor

    # delete any indexes for this table / column.
    index_names = se._constraint_names(model, [target_column_to_index], index=True)
    for index_name in index_names:
        se.execute(se._delete_constraint_sql(se.sql_delete_index, auth_user, index_name))


class Migration(migrations.Migration):

    dependencies = [
        ('frontend', '0001_initial'),
    ]

    operations = [
        RunPython(forward__auth_user__email__index, reverse_code=reverse__auth_user__email__index)
    ]