0

I'm constructing a query using the Q object but it's hanging.

When I "AND" the filters together, the query works fine. Here is the example:

School.objects.filter( Q(city__search='"orlando"'), Q(schoolattribute__attribute__name__search='"subjects"') )

But when I "OR" the filters together, the query just hangs because I'm assuming there's too much to process:

School.objects.filter( Q(city__search='"orlando"') | Q(schoolattribute__attribute__name__search='"subjects"')

I'm wondering what's going on here exactly and what can I do to mitigate it. Why does the query work when "AND" is used, but not when "OR" is used?

EDIT: Good tip @psagers. So it turns out that the AND query gets two INNER JOINs whereas the OR query gets two LEFT OUTER JOINs.

Tomasz Jakub Rup
  • 10,502
  • 7
  • 48
  • 49
Abid A
  • 7,588
  • 4
  • 32
  • 32
  • 5
    When in doubt, go to the SQL. I would suggest constructing the queryset and then getting the raw SQL from the `query` property. If it's still not clear, you can probably plug this into your database engine and ask it to analyze the performance for you. See http://stackoverflow.com/questions/3748295/getting-the-sql-from-a-django-queryset. – psagers Mar 06 '12 at 18:16

1 Answers1

1

Given your situation, I'll assume the following:

  1. You have a really big data set
  2. You don't want to fetch too many entries

To optimize your code, you'd probably be better off using two queries:

schools_by_city = School.objects.filter(city__search='"orlando"')
schools_by_attribute_city = School.objects.filter(schoolattribute__attribute__name__search='"subjects"')
result = set(schools_by_city).union(set(schools_by_attribute_city))

This will probably be better than your original query (because you can use the INNER join), but you should test it out. If my assumptions are wrong, you should probably rethink your db structure (i.e. use a specialized tool for searching instead of mysql fulltext, rethinking SchoolAttribute, whatever floats your boat).

Gabi Purcaru
  • 30,940
  • 9
  • 79
  • 95
  • Your assumptions are correct. This is the first I've dealt with sets and Python apparently doesn't like the `+` operator. What should I use instead? – Abid A Mar 06 '12 at 20:42
  • Thanks for that. Unfortunately, bringing those sets together takes quite a bit of processing time. – Abid A Mar 08 '12 at 15:07
  • Well, I want to display 400 entries at a time, but the problem is that those initial sets could hold thousands. – Abid A Mar 08 '12 at 16:17