id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
23367	MySQL composite indexes create duplicate indexes (ManyToManyField, unique_together)	Jon Dufresne	nobody	"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`."	Bug	closed	Database layer (models, ORM)	dev	Normal	duplicate			Unreviewed	0	0	0	0	0	0