CODEWITHSUNDEEP
mysqldjangodjango-queryset
rolling stone
rolling stone

Reputation: 13016

Django: What's the fastest way to order a QuerySet based on the count of a related field?

I've got an Item model in my Django app with a ManyToMany field that's handled through an intermediate Favorite model. Here are simplified versions of the models in question:

class Item(models.Model):
    name = models.CharField(max_length=200)

class Favorite(models.Model):
    user = models.ForeignKey(User)
    item = models.ForeignKey(Item)

I'm trying to get a list of items ordered by the number of favorites. The query below works, however there are thousands of records in the Item table, and the query is taking up to a couple of minutes to complete. 

items = Item.objects.annotate(num_favorites=Count('favorite')).order_by('-num_favorites')

Not sure if this is relevant to any potential answers, but I'm paginating the results using Django's built-in Pagintor:

paginator = Paginator(items, 100)

I know I can add a favorites field to my Item model and increment that every time an item is favorited, but I'm wondering if there's another cleaner, more efficient way to retrieve this data in a reasonable time.

Below is the output of the MySQL EXPLAIN function:

+----+-------------+--------------------+------+-----------------------------+-----------------------------+---------+-------------------------------+------+---------------------------------+
| id | select_type | table              | type | possible_keys               | key                         | key_len | ref                           | rows | Extra                           |
+----+-------------+--------------------+------+-----------------------------+-----------------------------+---------+-------------------------------+------+---------------------------------+
|  1 | SIMPLE      | appname_item       | ALL  | NULL                        | NULL                        | NULL    | NULL                          |  566 | Using temporary; Using filesort |
|  1 | SIMPLE      | appname_favorite   | ref  | appname_favorite_67b70d25   | appname_favorite_67b70d25   | 4       | appname.appname_item.id       |    1 |                                 |
+----+-------------+--------------------+------+-----------------------------+-----------------------------+---------+-------------------------------+------+---------------------------------+

Upvotes: 2

Views: 295

Answers (2)

Aamir Rind
Aamir Rind

Reputation: 39689

when ever you try to use order_by clause whose index is not defined, try to defined index on it, it makes the ordering process way faster, in you case index on 'num_favorites' is what you need to speed up the query execution time. also the technique which you are using is fine there is nothing bad in it.

please see the related question which was asked by me on same issue, and saverio gave an excellent answer to tackle the problem. Hope this helps.

Upvotes: 1

Daniel Roseman
Daniel Roseman

Reputation: 599866

That is already the best way. If it's slow, the problem is probably with your database - specifically, you don't have the right indexes for that query so the db is having to do too much sorting.

The Django debug toolbar is great for diagnosing this sort of thing - it will show you how long each query is taking, and allow you to run the db's EXPLAIN function on each one. The MySQL docs will tell you what the output from EXPLAIN means. However, after that it's up to you to optimise the db.

Upvotes: 1

Related Questions