Does Django automatically generate indexes for foreign keys columns?

Question

Does Django automatically generate indexes for foreign keys, or does it just depend on the underlying DB policy ?

depend on `db_index` parameter in ForeignKey, which by default is True. — Sławomir Lenart, Jul 13 '17 at 12:35

score 153 · Accepted Answer · edited Jul 21 '21 at 18:14

153

Django automatically creates an index for all models.ForeignKey columns.

A database index is automatically created on the ForeignKey. You can disable this by setting db_index to False. 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.

edited Jul 21 '21 at 18:14

smac89

39,374
15
132
179

answered May 15 '11 at 19:02

Luke Sneeringer

9,270
2
35
32

I couldn't find any reference on this fact. Any reference to this ? – GabiMe May 16 '11 at 09:27
1

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. – Luke Sneeringer May 17 '11 at 04:49
7

@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. – Jesse Beder Jan 03 '12 at 16:49
4

It is also possible to disable creation of an index on a ForeignKey: https://code.djangoproject.com/ticket/13730 – Jeff Bauer Apr 01 '12 at 13:28
2

@JesseBeder: that `./manage.py sqlindexes appname` doesn't work any longer in recent Django versions. – SaeX Dec 07 '15 at 22:01
Link to source is broken, you might want to update it to https://docs.djangoproject.com/en/1.9/ref/models/fields/#django.db.models.ForeignKey – Ronen Ness Jul 01 '16 at 18:45
1

`manage.py sqlindexes` was removed in django 1.9 as shown here: https://docs.djangoproject.com/en/dev/internals/deprecation/. In SQL, you can use `SHOW INDEX FROM yourtable;`, ref: https://stackoverflow.com/questions/5213339/how-to-see-indexes-for-a-database-or-table – GabLeRoux Feb 16 '18 at 16:34
Does Django also create indexes for the primary key / Autofield? – alias51 Jul 30 '20 at 00:07

score 0 · Answer 2 · answered Jul 01 '23 at 13:58

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

Does Django automatically generate indexes for foreign keys columns?

2 Answers2

Linked