Multi-table inheritance generates many UNIQUE indexes on MySQL

[sylvaintersideral@…]

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.

[Malcolm Tredinnick]

(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.

[Jacob]

Milestone 1.0 deleted