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

Question

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.

Answer 1

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.