Django Model Fields Indexing

Question

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',]), ] 

Answer 1

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) 

Answer 2

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']),