Code

Opened 4 years ago

Last modified 5 months ago

#14180 new Cleanup/optimization

Creating redundant indexes on foreign keys for MySQL/InnoDB tables

Reported by: zimnyx Owned by:
Component: Database layer (models, ORM) Version: 1.2
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Attachments (0)

Change History (5)

comment:1 Changed 4 years ago by mtredinnick

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Someday/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 3 years ago by julien

  • Severity set to Normal
  • Type set to Cleanup/optimization

comment:3 Changed 3 years ago by ramiro

  • Component changed from ORM aggregation to Database layer (models, ORM)
  • Easy pickings unset

comment:4 Changed 2 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:5 Changed 5 months ago by aaugustin

  • Triage Stage changed from Someday/Maybe to Accepted

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as new
The owner will be changed from (none) to anonymous. Next status will be 'assigned'
as The resolution will be set. Next status will be 'closed'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.