Does Django automatically generate indexes for foreign keys, or does it just depend on the underlying DB policy ?
2 Answers
Django automatically creates an index for all models.ForeignKey columns.
From Django documentation:
A database index is automatically created on the
ForeignKey. You can disable this by settingdb_indextoFalse. You may want to avoid the overhead of an index if you are creating a foreign key for consistency rather than joins, or if you will be creating an alternative index like a partial or multiple column index.
8 Comments
Luke Sneeringer
Run
./manage.py sql appname and you'll see the SQL statements for the index creation. They come just after the SQL for the creation of the last table needed.
Jesse Beder
@LukeSneeringer, I'm not sure if the command you give used to show indices, but it no longer does. Instead, run
./manage.py sqlindexes appname for the indices by themselves.
Jeffrey Bauer
It is also possible to disable creation of an index on a ForeignKey: code.djangoproject.com/ticket/13730
SaeX
@JesseBeder: that
./manage.py sqlindexes appname doesn't work any longer in recent Django versions.
GabLeRoux
manage.py sqlindexes was removed in django 1.9 as shown here: docs.djangoproject.com/en/dev/internals/deprecation. In SQL, you can use SHOW INDEX FROM yourtable;, ref: stackoverflow.com/questions/5213339/… |
In interest of visibility and the discussion going on in the comments of the accepted answer, adding it here.
- Yes, django automatically adds index for FK relationships.
- You can disable this by passing db_index=False like
models.ForeignKey(User, on_delete=models.CASCADE, db_index=False)
- This behavior depends on attached database -- (postgres/my-sql/oracle variants can have different SQL commands that are executed by django. MySQL creates such indexes automatically so django won't do that.)
- To verify what command is running for you: run
./manage.py sqlmigrate <app-name> <migration-number>
like
./manage.py sqlmigrate posts 0005
db_indexparameter in ForeignKey, which by default is True.