CODEWITHSUNDEEP
pythonmysqldjangoindexing
Lena
Lena

Reputation: 125

How to use Django's index_together for query both with filter and order_by?

I have a problem with building right index for my query.
I have a model like this: 

from django.db import models  

class Record(models.Model):
    user = models.ForeignKey(User, db_index=True, related_name='records')
    action = models.ForeignKey(Action, db_index=True)
    time = models.DateTimeField(db_index=True, default=timezone.now)

    class Meta:
        index_together = (
            ('user', 'time'),
            ('action', 'user', 'time'),
        )

As you can see, there are two custom indexes for this model.

If I wanna get all records, related to specific user, filtered by time, I use this query: user.records.filter(time__gt=some_moment). It works OK and uses first custom index (according to Django Debug Toolbar).

Now, in my situation result must be sorted by action. I use this query: user.records.filter(time__gt=some_moment).order_by('action').
But, although an appropriate index exists, it is not used.

What am I doing wrong? How to build correct index for this query?
Django version = 1.8.4, all migrations are applied, database backend = mysql.

UPD: there is my query: 

SELECT *** FROM `appname_record`
  WHERE (`appname_record`.`user_id` = 1896158 AND 
  `appname_record`.`time` > '2015-10-19 06:39:30.992790') 
  ORDER BY `appname_record`.`action_id` ASC

there is full django toolbar explanation: 

ID: 1  
SELECT_TYPE: SIMPLE  
TABLE: appname_record  
TYPE: ALL  
POSSIBLE_KEYS: 
   appname_record_user_id_3214bab8a46891cc_idx, appname_record_07cc694b  
KEY: None  
KEY_LEN: None  
REF: None  
ROWS: 240  
EXTRA: Using where; Using filesort

There is mysql show create table appname_record; part about keys: 

PRIMARY KEY (`id`),
KEY `appname_record_action_id_3e42ba1d5288899c_idx` (`action_id`, `user_id`,`time`),
KEY `appname_record_user_id_3214bab8a46891cc_idx` (`user_id`,`time`),
KEY `appname_record_07cc694b` (`time`),

So it seems like right index isn't even in possible keys.

Upvotes: 8

Views: 13142

Answers (3)

auvipy
auvipy

Reputation: 1208

For the new versions of Django, using Index class meta over index_together meta options is suggested.

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

https://docs.djangoproject.com/en/3.2/ref/models/options/#unique-together https://docs.djangoproject.com/en/3.2/ref/models/options/#index-together

Upvotes: 6

e4c5
e4c5

Reputation: 53774

If a query does not use any indexes at all, that's often because there isn't enough data in the table for an index to be really useful. However with 500 records there is a good chance that an index ought to come into play.

In the query that you have used, the appname_record_user_id_3214bab8a46891cc_idx is indeed a likely candidate but it's still not used. Why? because your query apparently causes the database to look at approximately half the table, as such an index cannot speed things up.

You seem to be on the right track with dropping one index. Two many similar indexes aren't really usefull either. I would try this index instead:

class Meta:
    index_together = (
        ('user', 'time','action'),
    )

The difference here is in the order of the fields. This is important:

MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

Upvotes: 11

Lena
Lena

Reputation: 125

I found the solution, it is not elegant, but it worked for me. Since I couldn't build any query, which will use 3-column index, I jush dropped a 2-column, so now both of my queries use 3-column. Have no idea, why it was ignored previously. Maybe, some complex mysql optimizations.

Upvotes: 4

Related Questions