Re: Complex query reduction

2013-11-08 Thread Javier Guerra Giraldez
On Fri, Nov 8, 2013 at 1:44 AM, Robin St.Clair wrote: > The last time I checked the use of IN, all the records from the database in > the query were brought back to the workstation, rather than being processed > on the backend and only the results returned to the workstation.

Re: Complex query reduction

2013-11-08 Thread François Schiettecatte
I am pretty sure the IN() performance issue in MySQL was fixed 5.5, and 5.6/5.7 certainly don't have an issue with IN() whether you use a bunch of values or a subquery. Cheers François On Nov 8, 2013, at 2:15 AM, akaariai wrote: > On Friday, November 8, 2013 8:44:09 AM

Re: Complex query reduction

2013-11-07 Thread akaariai
On Friday, November 8, 2013 8:44:09 AM UTC+2, Robin St.Clair wrote: > > Anssi > > The last time I checked the use of IN, all the records from the database > in the query were brought back to the workstation, rather than being > processed on the backend and only the results returned to the

Re: Complex query reduction

2013-11-07 Thread Robin St . Clair
Anssi The last time I checked the use of IN, all the records from the database in the query were brought back to the workstation, rather than being processed on the backend and only the results returned to the workstation. Have there been changes that carry out the entire query on the

Re: Complex query reduction

2013-11-07 Thread akaariai
On Sunday, November 3, 2013 1:48:07 PM UTC+2, Robin St.Clair wrote: > > *IN* > >- if using Django avoid the IN operation at all costs > > > If there are potentially more than 15 items in the list, rework the IN as > a JOIN against whatever the source of the keys is > I don't necessarily

Re: Complex query reduction

2013-11-04 Thread Apostolos Bessas
On Sat, Nov 2, 2013 at 4:50 PM, Daniele Procida wrote: > > But, the real killer is the combination of ordering (in the queryset or on > the model, it doesn't matter) with the distinct() - as soon as one is removed > from the equation, the execution time drops to around 250ms.

RE: Complex query reduction

2013-11-03 Thread Robin St . Clair
end coders didn't consider the DBMS to be importantDate: Sun, 3 Nov 2013 02:37:09 -0800 From: akaar...@gmail.com To: django-users@googlegroups.com Subject: Re: Complex query reduction You should rewrite the query into a form that doesn't require distinct. In general, when you see a query that has

Re: Complex query reduction

2013-11-03 Thread akaariai
You should rewrite the query into a form that doesn't require distinct. In general, when you see a query that has joins and DISTINCT, that should be an alarm bell that something isn't written correctly in the query. Unfortunately Django's ORM generates such queries, and that isn't easy to fix

Re: Complex query reduction

2013-11-02 Thread Daniele Procida
On Fri, Nov 1, 2013, Javier Guerra Giraldez wrote: >have you tried eliminating the second IN relationship? something like > >entities = entity.get_descendants() > >items = BibliographicRecord.objects.filter

Re: Complex query reduction

2013-11-01 Thread Javier Guerra Giraldez
On Fri, Nov 1, 2013 at 12:45 PM, Daniele Procida wrote: > In practice I use some tweaks (such as values_list) to speed this up, and > caching, but the fundamental pattern is the same. It's slow, because there > are 30 thousand BibliographicRecords. the total number of

Complex query reduction

2013-11-01 Thread Daniele Procida
I have been exploring a rather complex query: # get all the MPTT descendants of entity entities = entity.get_descendants() # get all the Researchers in these Entities researchers = Researcher.objects.filter(person__member_of__entity__in=entities) # get all the BibliographicRecords for these