Opened 6 years ago

Closed 22 months ago

#14180 closed Cleanup/optimization (fixed)

Creating redundant indexes on foreign keys for MySQL/InnoDB tables

Reported by: Piotr Czachur Owned by: Claude Paroz <claude@…>
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


During adding a foreign key contraint on InnoDB table, index is created automaticaly if it doesn't exist.

(MySQL 5.x manual)
"InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.)"

After adding FK constraint, Django creates index for every FK, which just "overwrites" index that was created in background by InnoDB engine.
It's a waist of time. It's not a big deal if you just run syncdb, but if you run unit tests waiting every time for those indexes be created is a bit annoying.

Change History (10)

comment:1 Changed 6 years ago by Malcolm Tredinnick

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset
Triage Stage: UnreviewedSomeday/Maybe

Any patch for this would have to include a way to auto-detect whether an appropriate engine is being used by MySQL to require indexing (InnoDB is only one of many storage engines MySQL supports). It's not something people should be required to specify in settings or anything like that.

comment:2 Changed 5 years ago by Julien Phalip

Severity: Normal
Type: Cleanup/optimization

comment:3 Changed 5 years ago by Ramiro Morales

Component: ORM aggregationDatabase layer (models, ORM)
Easy pickings: unset

comment:4 Changed 5 years ago by Aymeric Augustin

UI/UX: unset

Change UI/UX from NULL to False.

comment:5 Changed 3 years ago by Aymeric Augustin

Triage Stage: Someday/MaybeAccepted

comment:6 Changed 22 months ago by Claude Paroz

Has patch: set
Version: 1.2master

comment:7 Changed 22 months ago by Simon Charette

Patch needs improvement: set

comment:8 Changed 22 months ago by Claude Paroz

Patch needs improvement: unset

comment:9 Changed 22 months ago by Simon Charette

Triage Stage: AcceptedReady for checkin

comment:10 Changed 22 months ago by Claude Paroz <claude@…>

Owner: set to Claude Paroz <claude@…>
Resolution: fixed
Status: newclosed

In 2ceb10f3b02cbebad6ed908880f49a7c3e901d12:

Fixed #14180 -- Prevented unneeded index creation on MySQL-InnoDB

Thanks zimnyx for the report and Simon Charette, Tim Graham for
the reviews.

Note: See TracTickets for help on using tickets.
Back to Top