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