Re: Slow query on MySQL

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

Re: Slow query on MySQL

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

Re: Slow query on MySQL

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

Re: Slow query on MySQL

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

Re: Slow query on MySQL

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

Re: Slow query on MySQL

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

Re: Slow query on MySQL

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 >

Re: Slow query on MySQL

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

Re: Slow query on MySQL

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

Re: Slow query on MySQL

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

Re: Slow query on MySQL

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.

Re: Slow query on MySQL

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

Re: Slow query on MySQL

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

Re: Slow query on MySQL

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

Re: Slow query on MySQL

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

Re: Slow query on MySQL

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

Re: Slow query on MySQL

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

Re: Slow query on MySQL

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

Re: Slow query on MySQL

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

Re: Slow query on MySQL

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

Slow query on MySQL

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(