Opened 9 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:

Description

Creating the following very simple model :

class Interactable(models.Model):
  pass

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 :

  • PRIMARY
  • interactable_ptr_id (unique)
  • files_news_interactable_ptr_id (unique)

The SQL transaction looks like this :

BEGIN;
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` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY
)
;
ALTER TABLE `files_news` ADD CONSTRAINT
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`
(`interactable_ptr_id`);
COMMIT;

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 9 years ago by anonymous

Cc: gav@… added

comment:2 Changed 9 years ago by George Vilches

Keywords: qsrf-cleanup added

comment:3 Changed 8 years ago by Jacob

milestone: 1.0

comment:4 Changed 8 years ago by Malcolm Tredinnick

Resolution: fixed
Status: newclosed

(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

Milestone 1.0 deleted

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