On Mar 23, 1:47 pm, Javier Guerra Giraldez <jav...@guerrag.com> wrote:
> On Wed, Mar 23, 2011 at 1:35 PM, Brian Neal <bgn...@gmail.com> wrote:
> > So you are suggesting I need to shrink the number of topics or
> > possibly link the posts directly to the forum?
>
> right. since you only want the 30 latest posts, scanning 12k topics
> is absurd. i guess just ordering by (topic.update_date DESC,
> post_creationdate DESC) would allow the server to stop searching as
> soon as it has the required 30 posts.
>
> just be sure to have an index on topic.update_date
>
Hello again Javier,
I put an index on Topic.update_date. I then tried to change my Django
query to this:
items = Post.objects(filter=forum__topic__in=forums).order_by('-
topic__update_date', '-update_date').select_related(# as before)[:30]
But this had the same result as before. I checked the resulting SQL,
and it looked right, so I did an explain and it was pretty much as
before. It decided not to use the index.
So I broke it up into two queries:
topic_ids = list(Topic.objects.filter(forum__in=forums).order_by(
'-update_date').values_list('id', flat=True)[:30])
items = Post.objects.filter(topic__in=topic_ids).select_related(# as
before)[:30]
This was 2 queries, but it was blazingly fast compared to the
original. The performance should be fine for my application, and my
Munin graph of IOstat activity fell off dramatically.
If you have any ideas on how to get it down to one query, that would
be extra credit. As it is though, it's 2 orders of magnitude faster
than what it was. Thanks again for your insights into the problem! I
owe you one. :)
Best,
BN
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
