MySQL database being hit too many times with django query

Question

I'm using django-favorites to pull the list of objects faved by a user. The app has a model and a manager

class FavoriteManager(models.Manager):
    """ A Manager for Favorites
    """
    def favorites_for_user(self, user):
        """ Returns Favorites for a specific user
        """
        return self.get_query_set().filter(user=user)

class Favorite(models.Model):
    user = models.ForeignKey(User)
    content_type = models.ForeignKey(ContentType)
    object_id = models.PositiveIntegerField()
    content_object = generic.GenericForeignKey('content_type', 'object_id')

    created_on = models.DateTimeField(auto_now_add=True)

    objects = FavoriteManager()

    class Meta:
        verbose_name = _('favorite')
        verbose_name_plural = _('favorites')
        unique_together = (('user', 'content_type', 'object_id'),)

    def __unicode__(self):
        return "%s added %s as a favorite" % (self.user, self.content_object)

In my view, I'm pulling the faves for a user

 from favorites.models import Favorite
 def myfavorites(request):

      item = Favorite.objects.favorites_for_user(user=request.user)

      return render_to_response('myfavorites.html', {
           'favorites':item
      },
      context_instance=RequestContext(request))

In my template, I try to get a list of the favorites and their model types:

{% for fave in favorites %}
    {{ fave.content_type }}<br>
    {{ fave.content_object }}<br><br>
{% endfor %}

But using the django debug toolbar, I see that I'm hitting the database twice for each loop. The code is querying the database for the content_type and the content_object for every fave in the favorites object.

How do I optimize the code so that the data is pulled one time without need for several hits to the database to retrieve information?

jpic · Accepted Answer · 2012-03-14T19:20:07.350

You can decide between several solutions:

Generic relation pre-fetching, you can use this snippet like this example app.
Identity map/caching, use the content type and object id to make the key to cache the model, here is an example you can take the code inside __getstate__ and __setstate__ and make a template tag, this is the fastest solution you can find
Fetching relations directly, at some point you will probably need one of the solutions detailed in this article about generic relation performance later when you work with foreign keys.
Use JonhyCache, JohnyCache is a caching framework for querysets which works with the django caching abstraction. I did not personally try it yet, as the three solutions were sufficient in my projects.
Cache the template fragment, less hacky, it looks like official way to deal with this kind of issue that spawns a lot of queries, and when the user changes his favorites you could invalidate the cached version.

Note that select_related() doesn't work on generic relations. This is because the related object can be in any table so it's impossible to make proper joins in pure sql. Generic relations are a feature from Django, not from MySQL.

score 2 · Answer 2 · edited May 23 '17 at 12:33

Try using the select_related queryset method.

Something like this should do the trick:

item = Favorite.objects.favorites_for_user(user=request.user).select_related('content_type', 'content_object')

EDIT: jpic has rightly pointed out that select_related won't work for 'content_object' because it's a generic foreign key. Unfortunately there's no nice built in way to handle this, and there won't be until Django 1.4 rolls around. In the meantime, check out this SO question for some pointers. jpic has also posted some good solutions in his answer.

MySQL database being hit too many times with django query

2 Answers2