MySQL composite indexes create duplicate indexes (ManyToManyField, unique_together)

[Jon Dufresne]

This ticket is similar in spirit to #3030 but covers a different combination and case.

See the blog article ​http://www.mysqlperformanceblog.com/2012/06/20/find-and-remove-duplicate-indexes/ for information on why these tables contain duplicate indexes and why this creates a sub-optimal database. Specifically the section titled "Redundant keys on composite indexes".

In MySQL, given the following model definition in myapp/models.py, Django syncdb creates duplicate indexes.

class ModelA(models.Model):
    name = models.CharField(max_length=100)

class ModelB(models.Model):
    modela = models.ManyToManyField(ModelA)

class ModelC(models.Model):
    modela = models.ForeignKey(ModelA)
    modelb = models.ForeignKey(ModelB)

    class Meta:
        unique_together = ('modela', 'modelb')

The first duplicate index appears in the implicit table created by ManyToManyField:

CREATE TABLE `myapp_modelb_modela` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `modelb_id` int(11) NOT NULL,
  `modela_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `modelb_id` (`modelb_id`,`modela_id`),
  KEY `myapp_modelb_modela_db457816` (`modelb_id`),
  KEY `myapp_modelb_modela_a4d53239` (`modela_id`),
  CONSTRAINT `modelb_id_refs_id_6c09d9ee` FOREIGN KEY (`modelb_id`) REFERENCES `myapp_modelb` (`id`),
  CONSTRAINT `modela_id_refs_id_952611a9` FOREIGN KEY (`modela_id`) REFERENCES `myapp_modela` (`id`)
)

In the UNIQUE KEY modelb_id, modelb_id is the first field in the composite index. MySQL is perfectly capable of using this as an index on both the field modelb_id and the composite (modelb_id, modela_id). Ideally the second index, KEY myapp_modelb_modela_db457816 (modelb_id) would not be created at all.

The second duplicate index comes from ModelC and the unique_together constraint. This creates the database table:

CREATE TABLE `myapp_modelc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `modela_id` int(11) NOT NULL,
  `modelb_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `modela_id` (`modela_id`,`modelb_id`),
  KEY `myapp_modelc_a4d53239` (`modela_id`),
  KEY `myapp_modelc_db457816` (`modelb_id`),
  CONSTRAINT `modela_id_refs_id_f6e0e692` FOREIGN KEY (`modela_id`) REFERENCES `myapp_modela` (`id`),
  CONSTRAINT `modelb_id_refs_id_8e5b78f5` FOREIGN KEY (`modelb_id`) REFERENCES `myapp_modelb` (`id`)
)

Here, the index KEY myapp_modelc_a4d53239 (modela_id) is a duplicate as modela_id appears as the first column in the composite UNIQUE KEY.

[Tim Graham]

Duplicate of #22125.