Re: Slow query on MySQL

[Brian Neal]

On Mar 23, 9:55 pm, Javier Guerra Giraldez  wrote:
> On Wed, Mar 23, 2011 at 8:49 PM, Brian Neal  wrote:
> > 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.
>
> bummer :-(
>
> > 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]
>
> did you try:
>
> items = 
> Post.objects.filter(topic__in=Topic.objects.filter(forum__in=forums).order_ 
> by('-update_date')[:30]).select_related(#
> as before)[:30]
>
> IOW: use the first queryset for the '__in' clause, no need to flatten
> to a list of IDs.  that allows the ORM to weave a subselect 
> (seehttp://docs.djangoproject.com/en/1.3/ref/models/querysets/#in).
> unfortunately, the docs also warn about the less-than-ideal MySQL
> optimizer, so in many cases it turns to be faster to do just like you
> did.  (i guess that specially in these limited-size queries).

I did try that. In fact, just remove the list() around the Topic query
from the code I posted above and that was what I tried first. I got a
DatabaseError with an elaborate message about how this type of nested
query or something wasn't supported by the version of MySQL I had.
Sorry I didn't save the exact error message.

Again, many thanks.
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.

Re: Slow query on MySQL

[Javier Guerra Giraldez]

On Wed, Mar 23, 2011 at 8:49 PM, Brian Neal  wrote:
> 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.

bummer :-(


> 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]

did you try:

items = 
Post.objects.filter(topic__in=Topic.objects.filter(forum__in=forums).order_by('-update_date')[:30]).select_related(#
as before)[:30]


IOW: use the first queryset for the '__in' clause, no need to flatten
to a list of IDs.  that allows the ORM to weave a subselect (see
http://docs.djangoproject.com/en/1.3/ref/models/querysets/#in).
unfortunately, the docs also warn about the less-than-ideal MySQL
optimizer, so in many cases it turns to be faster to do just like you
did.  (i guess that specially in these limited-size queries).

-- 
Javier

-- 
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.

Re: Slow query on MySQL

[Brian Neal]

On Mar 23, 1:47 pm, Javier Guerra Giraldez  wrote:
> On Wed, Mar 23, 2011 at 1:35 PM, Brian Neal  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.

Re: Slow query on MySQL

[Javier Guerra Giraldez]

On Wed, Mar 23, 2011 at 1:35 PM, Brian Neal  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

the second choice, linking the post to the forum would let the server
to do the scan based on post, not on topic, likely eliminating the
need for the temporary table

-- 
Javier

-- 
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.

Re: Slow query on MySQL

[Brian Neal]

On Mar 23, 12:59 pm, Javier Guerra Giraldez 
wrote:
> On Wed, Mar 23, 2011 at 12:56 PM, Javier Guerra Giraldez
>
>  wrote:
> > in this case the problem arises because of the big mismatch between
> > the forums_forum table (just 15 records) and the forums_topic table
> > (12k records)  that, and the need to sort by a field on another
> > (bigger) table: forums_post.created_date
>
> PS:  i concur that 7secs for 12k records is pretty bad; definitely
> it's doing the sort on disk and seems to have be using a badly chosen
> set of memory parameters.  still, optimizing the RAM usage for a bad
> case is a last resort.  I'd first be sure that it's not possible to
> optimize the query and _only_then_ try to optimize RAM layout to
> soften the hit

Yes, I think it is spilling out onto the disk. In my Munin graphs I
can definitely see a lot more IOStat activity since this query went
live.

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.

Re: Slow query on MySQL

[Brian Neal]

On Mar 23, 11:45 am, Javier Guerra Giraldez 
wrote:
>
> >http://dpaste.com/524865/
>
> ok, now it's obvious.
>
> the second query (the one with topic__forum__in=forums, right?) is
> scanning the whole topic table (12Krows).  it seems to be guessing
> that picking a significant fraction of a very small space (5 forums
> out of 15, right?) wouldn't be so much better using the index.
>
> the first query is using the topic->forum key, narrowing the loop to
> the ~1388 topics of the given forum.
>
> now, how to improve this?  it seems a little denormalization is in
> order.  i can thing of two different options:
>
> A: add a 'last_post_date' field to the topics table, and select only
> the most recent 10 or so topics.
>
> B: add a new post->forum relation, and maybe a (forum,post_date) index
>

Thank you very much for your insight Javiar. I've learned a lot here.
I will try to denormalize. I might already have such a field in the
topic model I could use. I have an "update_date" field in the topic
model that I denormalized for display purposes; it is just the date of
the last post in the topic. It isn't quite clear to me how to proceed
yet, but you've given me some things to think about.

So you are suggesting I need to shrink the number of topics or
possibly link the posts directly to the forum?

Thanks again,
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.

Re: Slow query on MySQL

[Javier Guerra Giraldez]

On Wed, Mar 23, 2011 at 12:56 PM, Javier Guerra Giraldez
 wrote:
> in this case the problem arises because of the big mismatch between
> the forums_forum table (just 15 records) and the forums_topic table
> (12k records)  that, and the need to sort by a field on another
> (bigger) table: forums_post.created_date

PS:  i concur that 7secs for 12k records is pretty bad; definitely
it's doing the sort on disk and seems to have be using a badly chosen
set of memory parameters.  still, optimizing the RAM usage for a bad
case is a last resort.  I'd first be sure that it's not possible to
optimize the query and _only_then_ try to optimize RAM layout to
soften the hit

-- 
Javier

-- 
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.

Re: Slow query on MySQL

[Javier Guerra Giraldez]

On Wed, Mar 23, 2011 at 11:51 AM, Christophe Pettus  wrote:
> Looking at the SQL, it looks like the way MySQL executes the IN is to read in 
> and sort all of the Topic records, then probing for the matching ones once 
> they're sorted.


it only does a linear when the number of IN entries is a significant
part of the whole table.

>
> Generally, if you can avoid IN, especially in MySQL, it's a good idea.

not really, typically the number of IN entries is trivial compared to
the whole universe of choices.  in that case it creates a temporary
table with the entries and does a JOIN, with all the optimization
opportunities of a typical JOIN.

in this case the problem arises because of the big mismatch between
the forums_forum table (just 15 records) and the forums_topic table
(12k records)  that, and the need to sort by a field on another
(bigger) table: forums_post.created_date

-- 
Javier

-- 
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.

Re: Slow query on MySQL

[Christophe Pettus]

On Mar 23, 2011, at 8:45 AM, Brian Neal wrote:

> Sorry, my bad. I've been tweaking things in vain. Here are the correct
> EXPLAINS.
> 
> http://dpaste.com/524865/

In both cases, what it's doing is grabbing a set of records from Topic, sorting 
them, then using those to select Posts.  In the second one, it is grabbing 
*all* of the topics to sort, and my guess is that it is spilling to disk in 
doing the sort, which is why it takes so long (although 7 seconds to sort 12k 
records is pretty bad... have you considered using PostgreSQL instead? :) ).

Looking at the SQL, it looks like the way MySQL executes the IN is to read in 
and sort all of the Topic records, then probing for the matching ones once 
they're sorted.

Generally, if you can avoid IN, especially in MySQL, it's a good idea.

--
-- Christophe Pettus
   x...@thebuild.com
 

-- 
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.

Re: Slow query on MySQL

[Javier Guerra Giraldez]

On Wed, Mar 23, 2011 at 10:45 AM, Brian Neal  wrote:
> Sorry, my bad. I've been tweaking things in vain. Here are the correct
> EXPLAINS.
>
> http://dpaste.com/524865/

ok, now it's obvious.

the second query (the one with topic__forum__in=forums, right?) is
scanning the whole topic table (12Krows).  it seems to be guessing
that picking a significant fraction of a very small space (5 forums
out of 15, right?) wouldn't be so much better using the index.

the first query is using the topic->forum key, narrowing the loop to
the ~1388 topics of the given forum.

now, how to improve this?  it seems a little denormalization is in
order.  i can thing of two different options:

A: add a 'last_post_date' field to the topics table, and select only
the most recent 10 or so topics.

B: add a new post->forum relation, and maybe a (forum,post_date) index

-- 
Javier

-- 
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.

Re: Slow query on MySQL

[Brian Neal]

On Mar 23, 8:49 am, Javier Guerra Giraldez  wrote:
> are the EXPLAINs from these exact SQL queries?  i don't see why it
> cares about the forum_forum table, which isn't mentioned on the
> queries.

Sorry, my bad. I've been tweaking things in vain. Here are the correct
EXPLAINS.

http://dpaste.com/524865/

Thanks.

-- 
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.

Re: Slow query on MySQL

[Javier Guerra Giraldez]

On Wed, Mar 23, 2011 at 7:51 AM, Brian Neal  wrote:
> Any other thoughts? Thanks.
>

are the EXPLAINs from these exact SQL queries?  i don't see why it
cares about the forum_forum table, which isn't mentioned on the
queries.

other than that, i don't see why it would be so slow

-- 
Javier

-- 
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.

Re: Slow query on MySQL

[Brian Neal]

On Mar 22, 11:01 pm, Javier Guerra Giraldez 
wrote:
> On Tue, Mar 22, 2011 at 10:42 PM, Brian Neal  wrote:
> > This is what I came up with to reduce the long times I was
> > seeing (but it still is slow). This is probably going to get ugly in
> > email, maybe I should have dpasted it:
>
> ugliness is assumed in SQL :-)
>
> is there an index on Post.creation_date ?
>
> --
> Javier

Hi Javier. I have db_index=True on the creation_date field in Post.
Any other thoughts? Thanks.

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.

Re: Slow query on MySQL

[Javier Guerra Giraldez]

On Tue, Mar 22, 2011 at 10:42 PM, Brian Neal  wrote:
> This is what I came up with to reduce the long times I was
> seeing (but it still is slow). This is probably going to get ugly in
> email, maybe I should have dpasted it:

ugliness is assumed in SQL :-)

is there an index on Post.creation_date ?

-- 
Javier

-- 
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.

Re: Slow query on MySQL

[Brian Neal]

On Mar 22, 10:15 pm, Javier Guerra Giraldez 
wrote:
> On Tue, Mar 22, 2011 at 10:06 PM, Brian Neal  wrote:
> > I see. There are in fact only 15 forums. But why does it take 40
> > seconds? I can get much better results if I do a select on each forum
> > individually and combine them together in Python code. So in this case
> > 15 selects is far better than 1. Maybe that's an expected result in
> > some cases, but it sure surprised me.
>
> can you post the full SQL query?
>

Sure. This is what I came up with to reduce the long times I was
seeing (but it still is slow). This is probably going to get ugly in
email, maybe I should have dpasted it:

items = Post.objects.filter(topic__forum=8).order_by('-
creation_date').select_related('topic', 'user', 'topic_forum')[:30]

produces (time 0.289 seconds):

SELECT `forums_post`.`id`, `forums_post`.`topic_id`,
`forums_post`.`user_id`, `forums_post`.`creation_date`,
`forums_post`.`update_date`, `forums_post`.`body`,
`forums_post`.`html`, `forums_post`.`user_ip`, `forums_topic`.`id`,
`forums_topic`.`forum_id`, `forums_topic`.`name`,
`forums_topic`.`creation_date`, `forums_topic`.`user_id`,
`forums_topic`.`view_count`, `forums_topic`.`sticky`,
`forums_topic`.`locked`, `forums_topic`.`post_count`,
`forums_topic`.`update_date`, `forums_topic`.`last_post_id`,
`auth_user`.`id`, `auth_user`.`username`, `auth_user`.`first_name`,
`auth_user`.`last_name`, `auth_user`.`email`, `auth_user`.`password`,
`auth_user`.`is_staff`, `auth_user`.`is_active`,
`auth_user`.`is_superuser`, `auth_user`.`last_login`,
`auth_user`.`date_joined` FROM `forums_post` INNER JOIN `forums_topic`
ON (`forums_post`.`topic_id` = `forums_topic`.`id`) INNER JOIN
`auth_user` ON (`forums_post`.`user_id` = `auth_user`.`id`) WHERE
`forums_topic`.`forum_id` = 8  ORDER BY `forums_post`.`creation_date`
DESC LIMIT 30

This:
items = Post.objects.filter(topic__forum__in=forums).order_by('-
creation_date').select_related('topic', 'user', 'topic_forum')[:30]

produces (in 7.357 seconds)

SELECT `forums_post`.`id`, `forums_post`.`topic_id`,
`forums_post`.`user_id`, `forums_post`.`creation_date`,
`forums_post`.`update_date`, `forums_post`.`body`,
`forums_post`.`html`, `forums_post`.`user_ip`, `forums_topic`.`id`,
`forums_topic`.`forum_id`, `forums_topic`.`name`,
`forums_topic`.`creation_date`, `forums_topic`.`user_id`,
`forums_topic`.`view_count`, `forums_topic`.`sticky`,
`forums_topic`.`locked`, `forums_topic`.`post_count`,
`forums_topic`.`update_date`, `forums_topic`.`last_post_id`,
`auth_user`.`id`, `auth_user`.`username`, `auth_user`.`first_name`,
`auth_user`.`last_name`, `auth_user`.`email`, `auth_user`.`password`,
`auth_user`.`is_staff`, `auth_user`.`is_active`,
`auth_user`.`is_superuser`, `auth_user`.`last_login`,
`auth_user`.`date_joined` FROM `forums_post` INNER JOIN `forums_topic`
ON (`forums_post`.`topic_id` = `forums_topic`.`id`) INNER JOIN
`auth_user` ON (`forums_post`.`user_id` = `auth_user`.`id`) WHERE
`forums_topic`.`forum_id` IN (3, 4, 14, 2, 6, 9, 8, 10, 5, 7, 11, 12,
13, 16) ORDER BY `forums_post`.`creation_date` DESC LIMIT 30

Each Post has a foreign key to a Topic and each Topic has a foreign
key to a Forum.

I guessing all those INNER JOIN's are killing me here.

Thanks.

-- 
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.

Re: Slow query on MySQL

[Javier Guerra Giraldez]

On Tue, Mar 22, 2011 at 10:06 PM, Brian Neal  wrote:
> I see. There are in fact only 15 forums. But why does it take 40
> seconds? I can get much better results if I do a select on each forum
> individually and combine them together in Python code. So in this case
> 15 selects is far better than 1. Maybe that's an expected result in
> some cases, but it sure surprised me.

can you post the full SQL query?

-- 
Javier

-- 
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.

Re: Slow query on MySQL

[Brian Neal]

On Mar 22, 9:56 pm, Javier Guerra Giraldez  wrote:
> On Tue, Mar 22, 2011 at 9:49 PM, Brian Neal  wrote:
> > I studied the SQL that Django generated and it seemed fine to me.
> > That's why I wonder if it is a MySQL issue since the EXPLAIN said it
> > had a possible key (PRIMARY) but then ended up not using it (bottom
> > one):
>
> that's because it saw so few rows (just 15) that it was cheaper to
> scan the whole thing instead of using the index
>

I see. There are in fact only 15 forums. But why does it take 40
seconds? I can get much better results if I do a select on each forum
individually and combine them together in Python code. So in this case
15 selects is far better than 1. Maybe that's an expected result in
some cases, but it sure surprised me.

Thank you for that, that helps a bit.

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.

Re: Slow query on MySQL

[Javier Guerra Giraldez]

On Tue, Mar 22, 2011 at 9:49 PM, Brian Neal  wrote:
> I studied the SQL that Django generated and it seemed fine to me.
> That's why I wonder if it is a MySQL issue since the EXPLAIN said it
> had a possible key (PRIMARY) but then ended up not using it (bottom
> one):

that's because it saw so few rows (just 15) that it was cheaper to
scan the whole thing instead of using the index

-- 
Javier

-- 
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.

Re: Slow query on MySQL

[Brian Neal]

On Mar 22, 8:17 pm, smallfish xy  wrote:
> hi, you can try split the in statement, with less with "in".
>
> forums = [1, 2, 3, 4, 5]
> r = []
> for f in forums :
>     r.append(Post.objects.filter(topic__forum=f.id)
> return r

That's what I did to work around it. I'd still like to know why it was
so excruciatingly slow.

>
> perhaps try to use the pure sql statement.

I studied the SQL that Django generated and it seemed fine to me.
That's why I wonder if it is a MySQL issue since the EXPLAIN said it
had a possible key (PRIMARY) but then ended up not using it (bottom
one):

http://dpaste.com/524697/

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.

Re: Slow query on MySQL

[smallfish xy]

hi, you can try split the in statement, with less with "in".

forums = [1, 2, 3, 4, 5]
r = []
for f in forums :
r.append(Post.objects.filter(topic__forum=f.id)
return r

perhaps try to use the pure sql statement.
--
blog: http://chenxiaoyu.org



On Wed, Mar 23, 2011 at 8:22 AM, Brian Neal  wrote:

> I apologize in advance as this is probably only tangentially related
> to Django.
>
> I have a forums type Django application. In my RSS feed class I had
> this Django model query:
>
> return Post.objects.filter(topic__forum__id=obj.id).order_by(
>'-creation_date').select_related(depth=2)[:30]
>
> It seemed to work okay, but was slow (2 seconds). I was able to reduce
> the time it took by more carefully extracting only the fields I needed
> with values_list().
>
> But then I changed it to use an "in" operation:
>
> forums = [1, 2, 3, 4, 5]
> return Post.objects.filter(topic__forum__in=forums).order_by(
>'-creation_date').select_related(depth=2)[:30]
>
> This query took over 40 seconds! Again, I can reduce this time using
> values_list and a smaller list of fields, but even then it is 4
> seconds or so.
>
> I did an EXPLAIN on each of the queries, see the link below. The top
> one is the fast one, and the bottom one is the slow one:
>
> http://dpaste.com/524697/
>
> I am not a MySQL expert and I need help interpreting the results. It
> looks like for whatever reason, on the slow one, MySQL identified a
> possible index, but then decided not to use it?
>
> I ended up working around it by doing the work in Python: I looped
> over all the forum ids in the "in" list, making the first query for
> each, and then had to combine and sort the results myself.
>
> I'm using InnoDB if that matters. Did I do something wrong or is this
> a MySQL issue?
>
> Thanks for any insights.
>
> --
> 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.
>
>

-- 
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.

Slow query on MySQL

[Brian Neal]

I apologize in advance as this is probably only tangentially related
to Django.

I have a forums type Django application. In my RSS feed class I had
this Django model query:

return Post.objects.filter(topic__forum__id=obj.id).order_by(
'-creation_date').select_related(depth=2)[:30]

It seemed to work okay, but was slow (2 seconds). I was able to reduce
the time it took by more carefully extracting only the fields I needed
with values_list().

But then I changed it to use an "in" operation:

forums = [1, 2, 3, 4, 5]
return Post.objects.filter(topic__forum__in=forums).order_by(
'-creation_date').select_related(depth=2)[:30]

This query took over 40 seconds! Again, I can reduce this time using
values_list and a smaller list of fields, but even then it is 4
seconds or so.

I did an EXPLAIN on each of the queries, see the link below. The top
one is the fast one, and the bottom one is the slow one:

http://dpaste.com/524697/

I am not a MySQL expert and I need help interpreting the results. It
looks like for whatever reason, on the slow one, MySQL identified a
possible index, but then decided not to use it?

I ended up working around it by doing the work in Python: I looped
over all the forum ids in the "in" list, making the first query for
each, and then had to combine and sort the results myself.

I'm using InnoDB if that matters. Did I do something wrong or is this
a MySQL issue?

Thanks for any insights.

-- 
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.