CODEWITHSUNDEEP
mysqldjangoindexing
jbastos
jbastos

Reputation: 1217

Django: create Index: non-unique, multiple column

Given the following model, I want to index the fields (sequence,stock) 

class QuoteModel(models.Model):  
    quotedate =  models.DateField()  
    high = models.FloatField() #(9,2) DEFAULT NULL,  
    low  = models.FloatField() #(9,2) DEFAULT NULL,  
    close  = models.FloatField() #(9,2) DEFAULT NULL,  
    closeadj  = models.FloatField() #(9,2) DEFAULT NULL,  
    volume  = models.IntegerField() #(9,2) DEFAULT NULL,  
    stock  = models.IntegerField(db_index=True) #(9,2) DEFAULT NULL,  
    open  = models.FloatField() #(9,2) DEFAULT NULL,  
    sequence = models.IntegerField() #(9,2) DEFAULT NULL,

This index should be non-unique - in mysql it should be something like: 

create index ndx_1 on model_quotemodel(sequence,stock);

The only Django workaround I know of is creating an "sql" file that will be executed by django upon table creation. So, I created a "stockmodel.sql" containing the following query (same as above:) 

create index ndx_1 on model_quotemodel(sequence,stock);

Is there any "cleaner" way of doing it?

Upvotes: 38

Views: 37042

Answers (8)

Samuel Dauzon
Samuel Dauzon

Reputation: 11324

The index_together feature is deprecated.

The unique_together also is deprecated.

You should use UniqueConstraint or indexes option instead.

Example of UniqueConstraint usage (Create DB UNIQUE constraint) :

class Customer(models.Model):
    first_name = models.CharField(max_length=100)
    last_name = models.CharField(max_length=100)
    country = models.ForeignKey(Country)
    social_security_number = models.CharField(max_length=32)

    class Meta:
        constraints = [
            models.UniqueConstraint(fields=['country', 'social_security_number'], name='unique_social_security_number_country')
        ]

Example of indexes option (Create DB Index):

from django.db import models

class Customer(models.Model):
    first_name = models.CharField(max_length=100)
    last_name = models.CharField(max_length=100)

    class Meta:
        indexes = [
            models.Index(fields=['last_name', 'first_name']),
            models.Index(fields=['first_name'], name='first_name_idx'),
        ]

Upvotes: 12

Chris
Chris

Reputation: 6382

Update for 2022:

The newer indexes option provides more functionality than index_together. index_together was deprecated in Django 4.1.

Usage:

from django.db import models

class Customer(models.Model):
    first_name = models.CharField(max_length=100)
    last_name = models.CharField(max_length=100)

    class Meta:
        indexes = [
            models.Index(fields=['last_name', 'first_name']),
            models.Index(fields=['first_name'], name='first_name_idx'),
        ]

Upvotes: 6

excieve
excieve

Reputation: 596

As of Django 1.5, you can use the Meta.index_together option:

class QuoteModel(models.Model):
    # ... fields ...

    class Meta:
        index_together = [
            ("sequence", "stock"),
        ]

(note: the original answer from 2009 said it was not possible to index multiple fields; it has since been replaced)

Upvotes: 34

Lingster
Lingster

Reputation: 1087

I just wanted to add that as of Django 1.11 there is a new feature, Options.indexes, which will allow you to specify the indexes to create:

Django Options.indexes

Upvotes: 2

cashmere
cashmere

Reputation: 2831

It is index_together in django 1.5

See here https://docs.djangoproject.com/en/dev/ref/models/options/#index-together

Upvotes: 16

Ivan Virabyan
Ivan Virabyan

Reputation: 1686

There is a ticket for this feature. Take a look http://code.djangoproject.com/ticket/5805

You may apply the patch from this ticket yourself.

UPDATE

It's now in Django: https://docs.djangoproject.com/en/1.5/ref/models/options/#django.db.models.Options.index_together

Upvotes: 24

George Lund
George Lund

Reputation: 1276

To follow on from the accepted answer, if you are using South, you can easily add a composite key as follows:

manage.py schemamigration your_app_name name_for_migration --add-index ModelName.first_field_in_index

You can then edit the generated migration file to add the additional fields into the one index (you'll see it's just a list of field names that's needed).

Don't forget to update the reverse migration as well as the forward one.

Upvotes: 17

craesh
craesh

Reputation: 3738

unique_together might be what you are looking for. Just put it in your Meta class inside your model.

Upvotes: 11

Related Questions