Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Django Model Fields Indexing

I only know that indexing is helpful and it queries faster.

What is the difference between following two?

1.

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

2.

class Meta:        indexes = [             models.Index(fields=['first_name',]),             models.Index(fields=['last_name',]),             models.Index(fields=['-date_of_birth',]), ] 
like image 982
ShubhamHAgrawal Avatar asked Jul 26 '17 13:07

ShubhamHAgrawal


People also ask

How index is defined in Django model?

To define an index with a descending order for a column, add a hyphen before the field's name. For your query, models. Index(fields=['last_name', 'first_name','-date_of_birth',]), would create SQL with (last_name, first_name, date_of_birth DESC).

Are foreign keys indexed Django?

Django automatically creates an index for all models. ForeignKey columns. From Django documentation: A database index is automatically created on the ForeignKey .

Is there a list field for Django models?

Mine is simpler to implement, and you can pass a list, dict, or anything that can be converted into json. In Django 1.10 and above, there's a new ArrayField field you can use.


2 Answers

Example 1:

The first example creates a single index on the last_name and first_name field.

indexes = [    models.Index(fields=['last_name', 'first_name',]), ] 

It will be useful if you search on the last name and first name together, or the last name by itself (because last_name is the first field in the index).

MyModel.objects.filter(last_name=last_name, first_name=first_name) MyModel.objects.filter(last_name=last_name) 

However, it will not be useful for searching for the first_name by itself (because first_name is not the first field in the index).

MyModel.objects.filter(first_name=first_name)  # not useful 

Example 2:

The second example creates an index for the first_name field and a separate index for the last_name field.

indexes = [     models.Index(fields=['first_name',]),     models.Index(fields=['last_name',]), ] 

It will be useful if you do lookups based on first name or last name in your code

MyModel.objects.filter(first_name=search) MyModel.objects.filter(last_name=search) 
like image 151
Alasdair Avatar answered Sep 22 '22 06:09

Alasdair


Django Model Index was introduced in Django 1.11

what is Model.indexes:

By default, indexes are created with an ascending order for each column. To define an index with a descending order for a column, add a hyphen before the field’s name.

For your query, models.Index(fields=['last_name', 'first_name','-date_of_birth',]), would create SQL with (last_name, first_name, date_of_birth DESC).

Lets move to your question,

you asked difference between 2 queries,

both will take models.Index(fields=['-date_of_birth',]),

because least one will override the assigned variables. from your question least is dateofbirth so it will override above two lines.

so as per documentation preferable method is, because indexing field should be in single list.. so django will prepare SQL indexing from list of fields...

models.Index(fields=['last_name', 'first_name', '-date_of_birth']), 
like image 28
Mohideen bin Mohammed Avatar answered Sep 23 '22 06:09

Mohideen bin Mohammed