[Django] #22125: Unnecessary creation of index for ManyToManyField

Django

unread,

Feb 22, 2014, 11:56:15 AM2/22/14

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField
----------------------------------------------+--------------------
Reporter: tbhtan3@… | Owner: nobody
Type: Cleanup/optimization | Status: new
Component: Database layer (models, ORM) | Version: 1.6
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 1 | UI/UX: 0
----------------------------------------------+--------------------
Suppose I have the following model:
class Food(models.Model):
restaurants = models.ManyToManyField(Restaurant)

The following table is created:

CREATE TABLE "main_food_restaurants" (
"id" integer NOT NULL PRIMARY KEY,
"food_id" integer NOT NULL,
"restaurant_id" integer NOT NULL,
UNIQUE ("food_id", "restaurant_id")
)

and the indexes:

CREATE INDEX "main_food_restaurants_0899c464" ON "main_food_restaurants"
("food_id");
CREATE INDEX "main_food_restaurants_be4c8f84" ON "main_food_restaurants"
("restaurant_id");

Notice that the single index on food_id is not needed due to the unique
index (food_id, restaurant_id)

--
Ticket URL: <https://code.djangoproject.com/ticket/22125>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Feb 28, 2014, 12:46:50 PM2/28/14

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

-------------------------------------+-------------------------------------
Reporter: tbhtan3@… | Owner: nobody
Type: | Status: new
Cleanup/optimization | Version: 1.6
Component: Database layer | Resolution:
(models, ORM) | Triage Stage:
Severity: Normal | Unreviewed
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 1 |
-------------------------------------+-------------------------------------
Changes (by ceaess):

* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0

Comment:

Is there some of the code missing? I'm not seeing a unique index created
in the SQL code you have here, just a unique constraint on food_id and
restaurant_id.

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:1>

Django

unread,

Mar 1, 2014, 10:24:16 AM3/1/14

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

-------------------------------------+-------------------------------------
Reporter: tbhtan3@… | Owner: nobody

Comment (by anonymous):

Let me clarify.
Suppose you have the following code:

class B(models.Model):
pass

class A(models.Model):
b = models.ManyToManyField(B)
===========================

The following SQL is produced:

CREATE TABLE "main_b" (

"id" integer NOT NULL PRIMARY KEY

)
;
CREATE TABLE "main_a_b" (

"id" integer NOT NULL PRIMARY KEY,

"a_id" integer NOT NULL,
"b_id" integer NOT NULL REFERENCES "main_b" ("id"),
UNIQUE ("a_id", "b_id")
)
;
CREATE TABLE "main_a" (

"id" integer NOT NULL PRIMARY KEY

)

CREATE INDEX "main_a_b_4d6cc2fb" ON "main_a_b" ("a_id");
CREATE INDEX "main_a_b_2c14050b" ON "main_a_b" ("b_id");

==============

Note that the index on a_id is redundant due to the unique constraint on
a_id, b_id

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:2>

Django

unread,

Mar 2, 2014, 8:25:46 PM3/2/14

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

-------------------------------------+-------------------------------------
Reporter: tbhtan3@… | Owner: bwreilly
Type: | Status: assigned

Changes (by bwreilly):

* owner: nobody => bwreilly
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:3>

Django

unread,

Mar 8, 2014, 4:13:03 PM3/8/14

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

Comment (by bwreilly):

I think this fixes it:
https://github.com/OneOcean/django/compare/django:master...master

But I am not sure I am entirely happy with it, and perhaps it needs a
test. Either way, I'll need to get a CLA and company CLA signed before I
can make a pull request.

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:4>

Django

unread,

Mar 9, 2014, 1:14:30 AM3/9/14

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

Comment (by anonymous):

looks good, but I can't comment much because I don't know Django ORM
internals.
Thanks for the work!

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:5>

Django

unread,

Mar 12, 2014, 11:47:09 AM3/12/14

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

-------------------------------------+-------------------------------------
Reporter: tbhtan3@… | Owner: bwreilly

Type: | Status: closed
Cleanup/optimization | Version: 1.6
Component: Database layer | Resolution: needsinfo

Changes (by shai):

* status: assigned => closed
* resolution: => needsinfo

Comment:

The question of whether an index for the first column of a unique
constraint is redundant, depends on how the database implements the unique
constraint. I am quite certain that the SQL standard does not specify,
that unique constraints must be implemented by indexes which can be used
partially.

So, for accepting this change, I'd need some indications that the index is
actually redundant -- at least in all core backends; hence, needsinfo.
Even if we accept it, I'd require the code to allow a (3rd-party) backend
to opt-out of it easily -- that is, with no need to override the entire
`create_model` method.

Your code is not a PR, so I can't comment there; I think it will break on
`unique_together=[('a','b','c')]`. It certainly does need tests (does it
pass the Django test-suite?) and also documentation changes.

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:6>

Django

unread,

Mar 15, 2014, 4:57:45 PM3/15/14

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

Comment (by sfrost@…):

The SQL standard *doesn't talk about indexes*, so claiming that it doesn't
happen to talk about how unique constraints are implemented is a bit like
trying to rearrange deck chairs on the Titanic.

The index is redundant in every database that I've ever come across- and
any that are sensible will do the same. How can a unique constraint be at
all performant if there isn't a fast way to find an existing value?
Allowing some backend to "opt-out" is fine, but it certainly shouldn't be
the default.

Note that only the index on the *first* column would be redundant; an
independent index on the second or subsequent columns which are included
in a unique constraint could be quite useful.

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:7>

Django

unread,

Mar 15, 2014, 7:10:29 PM3/15/14

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

Comment (by bwreilly):

I've confirmed PostgreSQL and SQLite both exhibit this behavior
([https://gist.github.com/anonymous/9575316 Gist]), I'll check MySQL when
I get a chance. I don't have access to an Oracle instance, but I'll check
their documentation.

Additionally, I've fixed the issue with `unique_together=[('a','b','c')]`,
added tests, and moved the functionality to an overridable method
([https://github.com/OneOcean/django/compare/django:master...master
compare]). Sorry it isn't a pull request at the moment, I'm going to fix
that Monday.

Besides [https://docs.djangoproject.com/en/dev/ref/models/options/#unique-
together Model Meta options unique_together] and
[https://docs.djangoproject.com/en/dev/ref/models/fields/#db-index Fields
db_index], should I update the documentation anywhere else?

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:8>

Django

unread,

Mar 15, 2014, 8:17:13 PM3/15/14

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

Comment (by Joshua Yanovski):

SQL Fiddle may help (I can't link it since it would be marked as spam, but
the address is pretty easy to determine). Oracle and MySQL both do this
as well.

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:9>

Django

unread,

Mar 15, 2014, 11:38:58 PM3/15/14

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

Comment (by alucard):

For the core DBs, they usually use B-Tree indexes so I think if [a,b] is
indexed, then [a] would be a redundant index. To generalize this, any
leftmost prefix of an indexed tuple would be redundant. This is definitely
true for MySQL.

However, for hash indexes (and possibly other types), both [a] and [a,b]
would be perfectly valid and non-redundant.

Source: https://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html
To quote: "[Hash index] Only whole keys can be used to search for a row.
(With a B-tree index, any leftmost prefix of the key can be used to find
rows.)"

I think we need to have an opt-out on a per table basis (based on Meta
options), or even better, per ManyToManyField basis (based on a setting to
__init__)

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:10>

Django

unread,

Mar 15, 2014, 11:48:34 PM3/15/14

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

Comment (by anonymous):

By the way, a very quick way to verify is to actually create a compound
index, and then use "EXPLAIN" to check whether a SELECT statement based on
the leftmost prefix can make use of the compound index. Of course this is
assuming you have access to those core DBs, of which Oracle could be a bit
of a problem.

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:11>

Django

unread,

Mar 19, 2014, 4:08:07 AM3/19/14

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

Comment (by Joshua Yanovski):

Again, please look at SQL Fiddle. It allows you to write queries against
a database of every type mentioned here, including Oracle (and also
Microsoft SQL Server). And every single one of them uses the composite
index for a query that uses only the first indexed column.

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:12>

Django

unread,

Mar 22, 2014, 10:25:28 AM3/22/14

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

-------------------------------------+-------------------------------------
Reporter: tbhtan3@… | Owner: bwreilly

Type: | Status: new

Cleanup/optimization | Version: 1.6
Component: Database layer | Resolution:

Changes (by anonymous):

* status: closed => new
* resolution: needsinfo =>

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:13>

Django

unread,

Mar 30, 2014, 3:17:47 AM3/30/14

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

-------------------------------------+-------------------------------------
Reporter: tbhtan3@… | Owner: bwreilly
Type: | Status: new
Cleanup/optimization | Version: 1.6
Component: Database layer | Resolution:

Easy pickings: 1 |
-------------------------------------+-------------------------------------
Changes (by anonymous):

* stage: Unreviewed => Accepted

Comment:

This seems like a reasonable optimization to me -- at a minimum we can do
it by adding a new flag to backends and then only performing it on
databases we've validated have this behavior (you'd have to have a REALLY
terrible database for index looks not to support a subset of fields with a
left-side prefix though).

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:14>

Django

unread,

Apr 20, 2014, 3:19:30 PM4/20/14

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

Has patch: 1 | UI/UX: 0

Needs tests: 0 |
Easy pickings: 1 |
-------------------------------------+-------------------------------------

Changes (by bwreilly):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:15>

Django

unread,

Apr 22, 2014, 1:06:59 PM4/22/14

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

-------------------------------------+-------------------------------------
Reporter: tbhtan3@… | Owner: bwreilly
Type: | Status: new

Cleanup/optimization | Version: master

Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by timo):

* version: 1.6 => master
* easy: 1 => 0

Comment:

[https://github.com/django/django/pull/2578 PR]

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:16>

Django

unread,

Aug 12, 2014, 4:04:05 AM8/12/14

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

Keywords: | Patch needs improvement: 1

Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------

Changes (by akaariai):

* needs_better_patch: 0 => 1

Comment:

I don't think the solution in PR2578 is the right solution at all.

If I read the patch correctly it removes all indexes for fields that are
already first element in some unique_together index. There are valid cases
for asking a separate index for a field even if it is part of an
unique_together index. The most important reason for this is that a
single-field index is faster than multicolumn index. In addition we
shouldn't override what users have asked explicitly.

We should just skip index creation for m2m tables unless the user asks for
single column index. I am not sure if we should add flags to m2m field for
creation or skipping of these indexes.

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:17>

Django

unread,

Aug 27, 2014, 8:52:32 AM8/27/14

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

Comment (by timgraham):

#23367 was a duplicate.

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:18>

Django

unread,

May 15, 2016, 6:06:41 PM5/15/16

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

-------------------------------------+-------------------------------------
Reporter: tbhtan3@… | Owner: bwreilly
Type: | Status: new
Cleanup/optimization |

* keywords: => db-indexes

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:19>

Django

unread,

Sep 20, 2016, 10:10:21 AM9/20/16

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

Changes (by edmorley):

* cc: emorley@… (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:20>

Django

unread,

Sep 24, 2018, 6:06:50 AM9/24/18

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

Changes (by Phil Krylov):

* cc: Phil Krylov (added)

Comment:

Replying to [comment:17 Anssi Kääriäinen]:

> We should just skip index creation for m2m tables unless the user asks
for single column index. I am not sure if we should add flags to m2m field
for creation or skipping of these indexes.

I would say that if someone needs a single column index in their through
table, they can use an explicit through table without `db_index=False` on
that (`ForeignKey`) column.

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:21>

Django

unread,

Sep 24, 2018, 7:54:06 AM9/24/18

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

Comment (by Phil Krylov):

Pull request: https://github.com/django/django/pull/10435

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:22>

Django

unread,

Oct 25, 2018, 10:07:13 AM10/25/18

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tim Graham):

* needs_better_patch: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:23>

Django

unread,

Oct 30, 2018, 2:29:37 PM10/30/18

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Tim Graham):

* needs_better_patch: 0 => 1

Old description:

> Suppose I have the following model:
> class Food(models.Model):
> restaurants = models.ManyToManyField(Restaurant)
>
> The following table is created:
>
> CREATE TABLE "main_food_restaurants" (
> "id" integer NOT NULL PRIMARY KEY,
> "food_id" integer NOT NULL,
> "restaurant_id" integer NOT NULL,
> UNIQUE ("food_id", "restaurant_id")
> )
>
> and the indexes:
>
> CREATE INDEX "main_food_restaurants_0899c464" ON "main_food_restaurants"
> ("food_id");
> CREATE INDEX "main_food_restaurants_be4c8f84" ON "main_food_restaurants"
> ("restaurant_id");
>
> Notice that the single index on food_id is not needed due to the unique
> index (food_id, restaurant_id)

New description:

Suppose I have the following model:
{{{
class Food(models.Model):
restaurants = models.ManyToManyField(Restaurant)
}}}
The following table is created:
{{{
CREATE TABLE "main_food_restaurants" (
"id" integer NOT NULL PRIMARY KEY,
"food_id" integer NOT NULL,
"restaurant_id" integer NOT NULL,
UNIQUE ("food_id", "restaurant_id")
)
}}}
and the indexes:
{{{
CREATE INDEX "main_food_restaurants_0899c464" ON "main_food_restaurants"
("food_id");
CREATE INDEX "main_food_restaurants_be4c8f84" ON "main_food_restaurants"
("restaurant_id");
}}}
Notice that the single index on food_id is not needed due to the unique

index `(food_id, restaurant_id)`.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:24>

Django

unread,

Jan 2, 2019, 9:37:48 AM1/2/19

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField

Comment (by László Károlyi):

Replying to [comment:24 Tim Graham]:

You might want to reconsider having the extraneously-seeming indexes
removed on the ManyToMany relationships. I just did a
[https://dev.to/karolyi/django-why-you-might-want-to-set-up-extra-db-
level-indexes-for-your-manytomany-relationships-1ecg case study] where it
turned out that MariaDB uses them.

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:25>

Django

unread,

Dec 21, 2020, 2:58:37 AM12/21/20

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField
-------------------------------------+-------------------------------------
Reporter: tbhtan3@… | Owner: bwreilly
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: db-indexes | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak):

#32288 was a duplicate.

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:26>

Django

unread,

Mar 21, 2023, 5:24:20 AM3/21/23

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField
-------------------------------------+-------------------------------------

Reporter: tbhtan3@… | Owner: (none)
Type: | Status: assigned
Cleanup/optimization |
Component: Database layer | Version: dev

Changes (by Mariusz Felisiak):

* owner: bwreilly => (none)

* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:27>

Django

unread,

Mar 21, 2023, 5:24:25 AM3/21/23

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField
-------------------------------------+-------------------------------------
Reporter: tbhtan3@… | Owner: (none)

Type: | Status: new

* status: assigned => new

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:28>

Django

unread,

Jun 14, 2023, 10:43:11 PM6/14/23

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField
-------------------------------------+-------------------------------------

Reporter: tbhtan3@… | Owner: Akash
Type: | Kumar Sen
Cleanup/optimization | Status: assigned

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Akash Kumar Sen):

* owner: (none) => Akash Kumar Sen

* needs_better_patch: 1 => 0

* status: new => assigned

Comment:

[https://github.com/django/django/pull/16979 PR]

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:29>

Django

unread,

Jun 14, 2023, 11:09:34 PM6/14/23

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField
-------------------------------------+-------------------------------------
Reporter: tbhtan3@… | Owner: Akash
Type: | Kumar Sen
Cleanup/optimization | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: db-indexes | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Akash Kumar Sen):

* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:30>

Django

unread,

Mar 9, 2024, 11:39:43 AMMar 9

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField
-------------------------------------+-------------------------------------
Reporter: tbhtan3@… | Owner: Akash
Type: | Kumar Sen
Cleanup/optimization | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: db-indexes | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Dan LaManna):

* cc: Dan LaManna (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:31>

Django

unread,

Mar 10, 2024, 10:22:37 PMMar 10

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField
-------------------------------------+-------------------------------------
Reporter: tbhtan3@… | Owner: Akash
Type: | Kumar Sen

Cleanup/optimization | Status: closed

Component: Database layer | Version: dev
(models, ORM) |

Severity: Normal | Resolution: wontfix

Keywords: db-indexes | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by django tester):

* resolution: => wontfix
* status: assigned => closed

--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:32>

Django

unread,

Mar 11, 2024, 12:27:55 AMMar 11

to django-...@googlegroups.com

#22125: Unnecessary creation of index for ManyToManyField
-------------------------------------+-------------------------------------
Reporter: tbhtan3@… | Owner: Akash
Type: | Kumar Sen

Cleanup/optimization | Status: new

Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:

Keywords: db-indexes | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* resolution: wontfix =>

* status: closed => new

Comment:

Replying to [comment:32 django tester]:

Please don't close ticket without any feedback.
--
Ticket URL: <https://code.djangoproject.com/ticket/22125#comment:33>

Reply all

Reply to author

Forward