Opened 8 years ago

Closed 8 years ago

Last modified 5 years ago

#7170 closed (fixed)

Multi-table inheritance generates many UNIQUE indexes on MySQL

Reported by: sylvaintersideral@… Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords: qsrf-cleanup multi-table inheritance unique primary
Cc: gav@… Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:


Creating the following very simple model :

class Interactable(models.Model):

class News(Interactable):
  title = models.CharField(max_length = 50)
  excerpt = models.CharField(max_length = 100)
  text = models.TextField()

And then synchronizing the database reveals that 2 unique indexes and
1 primary key refers to interactable_ptr_id in the news table :

  • interactable_ptr_id (unique)
  • files_news_interactable_ptr_id (unique)

The SQL transaction looks like this :

CREATE TABLE `files_news` (
    `interactable_ptr_id` integer NOT NULL UNIQUE PRIMARY KEY,
    `title` varchar(50) NOT NULL,
    `excerpt` varchar(100) NOT NULL,
    `text` longtext NOT NULL
CREATE TABLE `files_interactable` (
interactable_ptr_id_refs_id_71833d78 FOREIGN KEY
(`interactable_ptr_id`) REFERENCES `files_interactable` (`id`);
CREATE UNIQUE INDEX `files_news_interactable_ptr_id` ON `files_news`

I think there should be only ONE index (or maybe two ?), but not
three. Though I'm not sure about which one to keep : the primary key
is essential, but the reference to the parent table too, when using
InnoDB... but I think the last one is definitely useless.

Change History (5)

comment:1 Changed 8 years ago by anonymous

  • Cc gav@… added
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

comment:2 Changed 8 years ago by gav

  • Keywords qsrf-cleanup added

comment:3 Changed 8 years ago by jacob

  • milestone set to 1.0

comment:4 Changed 8 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from new to closed

(In [7790]) Make sure we only create the minimum number of table indexes for MySQL.

This patch simplifies a bunch of code for all backends and removes some
duplicate index creation for MySQL, in particular (versions 4.x and later).
Patch from Nis Jørgensen.

Fixed #5671, #5680, #7170, #7186.

comment:5 Changed 5 years ago by jacob

  • milestone 1.0 deleted

Milestone 1.0 deleted

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