# HG changeset patch
# User Brian Neal <bgneal@gmail.com>
# Date 1300499561 0
# Node ID b15726767ab820517035910c1b433ad085ce95d7
# Parent  9fcd366f22dc3cf138629fa1a2fff3f860de1426
Fixing #191; terrible performance on the combined forums RSS feed query. Use an .extra() clause to force the WHERE on a query to use the primary key.

diff -r 9fcd366f22dc -r b15726767ab8 gpp/forums/feeds.py
--- a/gpp/forums/feeds.py	Thu Mar 17 01:20:23 2011 +0000
+++ b/gpp/forums/feeds.py	Sat Mar 19 01:52:41 2011 +0000
@@ -20,7 +20,7 @@
         # only return public forums
         if forum_slug:
             forum = get_object_or_404(Forum, slug=forum_slug)
-            if forum.category.groups.count() > 0:
+            if forum.id not in Forum.objects.public_forum_ids():
                 raise ObjectDoesNotExist
             return forum
 
@@ -55,11 +55,27 @@
     def items(self, obj):
         if obj is None:
             # return a combined feed of public forum threads
-            items = Post.objects.filter(
-                    topic__forum__in=Forum.objects.public_forums())
+            #
+            # This didn't work real well on InnoDb:
+            # items = Post.objects.filter(
+            #         topic__forum__in=Forum.objects.public_forums())
+            #
+            # The where clause in the generated SQL looked like this:
+            #   WHERE `forums_topic`.`forum_id` IN ( ... )
+            # This was terrible for performance. This does much better:
+            #   WHERE `forums_topic`.`id` IN ( ... )
+            #
+            # So we use Django's .extra() to force the above.
+
+            public_forum_ids = Forum.objects.public_forum_ids()
+            ids = ','.join(str(pub) for pub in public_forum_ids)
+
+            items = Post.objects.extra(
+                where=['forums_topic.id in (%s)' % ids])
+
         else:
             items = Post.objects.filter(topic__forum__id=obj.id)
-        return items.order_by('-creation_date').select_related()[:30]
+        return items.order_by('-creation_date').select_related(depth=2)[:30]
 
     def item_title(self, item):
         return item.topic.name
diff -r 9fcd366f22dc -r b15726767ab8 gpp/forums/models.py
--- a/gpp/forums/models.py	Thu Mar 17 01:20:23 2011 +0000
+++ b/gpp/forums/models.py	Sat Mar 19 01:52:41 2011 +0000
@@ -6,6 +6,7 @@
 from django.db import models
 from django.db.models import Q
 from django.contrib.auth.models import User, Group
+from django.core.cache import cache
 
 from core.markup import site_markup
 from oembed.models import Oembed
@@ -71,6 +72,18 @@
         """Returns a queryset containing the public forums."""
         return self.filter(category__groups__isnull=True)
 
+    def public_forum_ids(self):
+        """
+        Returns a list of ids for the public forums; the list is cached for
+        performance.
+        """
+        public_forums = cache.get('public_forum_ids')
+        if public_forums is None:
+            public_forums = list(self.filter(
+                category__groups__isnull=True).values_list('id', flat=True))
+            cache.set('public_forum_ids', public_forums, 3600)
+        return public_forums
+
     def _for_user(self, user):
         """Common code for the xxx_for_user() methods."""
         if user.is_superuser:
diff -r 9fcd366f22dc -r b15726767ab8 gpp/forums/templatetags/forum_tags.py
--- a/gpp/forums/templatetags/forum_tags.py	Thu Mar 17 01:20:23 2011 +0000
+++ b/gpp/forums/templatetags/forum_tags.py	Sat Mar 19 01:52:41 2011 +0000
@@ -138,14 +138,9 @@
     This tag displays the topics that have the newest posts.
     Only the "public" forums are displayed.
     """
-    public_forums = cache.get('public_forum_ids')
-    if public_forums is None:
-        public_forums = list(Forum.objects.filter(
-            category__groups__isnull=True).values_list('id', flat=True))
-        cache.set('public_forum_ids', public_forums, 10 * 60)
-
-    topics = Topic.objects.filter(forum__in=public_forums).select_related(
-                    'user', 'last_post').order_by('-update_date')[:10]
+    public_forum_ids = Forum.objects.public_forum_ids()
+    topics = Topic.objects.filter(forum__in=public_forum_ids).select_related(
+                'last_post', 'last_post__user').order_by('-update_date')[:10]
 
     return {
         'topics': topics,