CODEWITHSUNDEEP
pythonmysqldjango
Xerion
Xerion

Reputation: 3281

Does Django ManyToManyField create table with a redundant index?

If I have a model Foo that has a simple M2M field to model Bar:

class Foo(Model):
    bar = ManyToManyField(Bar)

Django seems to create a table foo_bar which has the following indices:

index 1: primary, unique (id)
index 2: unique (foo_id, bar_id)
index 3: non_unique (foo_id)
index 4: non_unique (bar_id)

I recall from my basic knowledge of SQL, that if a query needs to look for conditions on foo_id, index 2 would suffice (since the left-most column can be used for lookup). index 3 seems to be redundant.

Am I correct to assume that index 3 does indeed take up index space while offering no benefit? That I'm better off using a through table and manually create a unique index on (foo_id, bar_id), and optionally, another index on (bar_id) if needed?

Upvotes: 1

Views: 4297

Answers (2)

L. Martineau
L. Martineau

Reputation: 71

For someone else who is still wondering. This is a known issue and there is an open ticket on the django bug tracker : https://code.djangoproject.com/ticket/22125

Upvotes: 2

madzohan
madzohan

Reputation: 11808

The key to understanding how a many-to-many association is represented in the database is to realize that each line of the junction table (in this case, foo_bar) connects one line from the left table (foo) with one line from the right table (bar). Each pk of "foo" can be copied many times to "foo_bar"; each pk of "bar" can also be copied many times to "foo_bar". But the same pair of fk's in "foo_bar" can only occur once.

So if you have only one index (pk of "foo" or "bar") in "foo_bar" it can be only one occurrence of it ... and it is not Many to many relation.

For example we have two models (e-commerce): Product, Order.

Each product can be in many orders and one order can contain many products.

class Product(models.Model):
    ...

class Order(models.Model):
    products = ManyToManyField(Product, through='OrderedProduct')


class OrderedProduct(models.Model):
    # each pair can be only one time, so in one order you can calculate price for each product (considering ordered amount of it).
    # and at the same time you can get somewhere in your template|view all orders which contain same product 

    order = models.ForeignKey(Order)
    product = models.ForeignKey(Product)

    amount = models.PositiveSmallIntegerField()  # amount of ordered products
    price = models.IntegerField()  # just int in this simple example

    def save(self, *args, **kwargs):
        self.price = self.product__price * self.amount

        super(OrderedProduct, self).save(*args, **kwargs)

Upvotes: 1

Related Questions