You can set up an index on multiple field, as well, so if you’re searching for As without a reference from B or C, using the index_together operative in the class Meta for that model. I’m not completely sure, but I think this may speed up you query time.
Thanks, Furbee On Saturday, February 3, 2018, Vijay Khemlani <[email protected]> wrote: > Well, you should've said that in the first post. > > First I would try with a saner DB (Postgres) > > Also I don't think 300 ms is particularly bad, but in that case start > looking into caching alternatives (e.g. memcached) or a search index (e.g. > ElasticSearch) > > On Sat, Feb 3, 2018 at 3:14 AM, Web Architect <[email protected]> wrote: > >> Hi Furbee, >> >> Thanks for your response. >> >> With my experience I have always noticed that a query within query kills >> the mysql and Mysqld CPU usage hits the ceiling. I would still check your >> alternate. >> >> I have mentioned the size of A and B in response to Vijay's reply. >> >> On Saturday, February 3, 2018 at 1:06:48 AM UTC+5:30, Furbee wrote: >>> >>> There are a couple options you could try to see which is the best fit >>> for your data. With DEBUG=True in settings.py you can check the actual >>> queries and process time. It depends on the sizes of A and B. Another query >>> you can run is: >>> >>> A.objects.exclude(id__in=B.objects.all().values_list('a_id', flat=True)) >>> >>> When I tried, it seemed to be about the same speed with my test data as >>> the one you had A.objects.filter(bs__isnull=True). >>> >>> To see what queries are generated and the query time with DEBUG=True: >>> Open your Django Python Shell >>> >>> A.objects.exclude(id__in=B.objects.all().values_list('a_id', >>> flat=True)) >>> >>> A.objects.filter(bs__isnull=True) >>> >>> from django.db import connection >>> >>> for q in connection.queries: >>> >>> print("{0}: {1}".format(q['sql'], q['time'])) >>> >>> This will show you both queries generated and how long it took to get a >>> response from your DB. >>> >>> You can also write raw SQL, if you can make one more efficiently. >>> >>> Furbee >>> >>> On Fri, Feb 2, 2018 at 10:56 AM, Vijay Khemlani <[email protected]> >>> wrote: >>> >>>> "with large of records in A and B, the above takes lot of time" >>>> >>>> How long? At first glance it doesn't look like a complex query or >>>> something particularly inefficient for a DB. >>>> >>>> On Fri, Feb 2, 2018 at 11:31 AM, Andy <[email protected]> wrote: >>>> >>>>> not that i know of >>>>> >>>>> >>>>> Am Freitag, 2. Februar 2018 15:28:26 UTC+1 schrieb Web Architect: >>>>>> >>>>>> Hi Andy, >>>>>> >>>>>> Thanks for your response. I was pondering on option a before posting >>>>>> this query thinking there could be better ways in django/SQL to handle >>>>>> this. But now I would probably go with a. >>>>>> >>>>>> Thanks. >>>>>> >>>>>> On Friday, February 2, 2018 at 7:50:53 PM UTC+5:30, Andy wrote: >>>>>>> >>>>>>> a) Maybe its an option to put the foreign key to the other model? >>>>>>> This way you dont need to make a join to find out if there is a >>>>>>> relation. >>>>>>> >>>>>>> b) Save the existing ralation status to model A >>>>>>> >>>>>>> c) cache the A.objects.filter(bs__isnull=False) query >>>>>>> >>>>>>> But apart from that i fear you cannot do much more, since this is >>>>>>> just a DB and not a Django ORM question. >>>>>>> >>>>>>> >>>>>>> Am Freitag, 2. Februar 2018 14:47:45 UTC+1 schrieb Web Architect: >>>>>>>> >>>>>>>> Hi, >>>>>>>> >>>>>>>> I am trying to optimise Django queries on my ecommerce website. One >>>>>>>> of the fundamental query is where I have no clue how to make >>>>>>>> efficient. It >>>>>>>> could be trivial and probably been known long time back. But I am new >>>>>>>> to >>>>>>>> Django and would appreciate any help. This is primarily for one to >>>>>>>> many or >>>>>>>> many to many relations. >>>>>>>> >>>>>>>> Following is an example scenario: >>>>>>>> (Please pardon my syntax as I want to put across the concept and >>>>>>>> not the exact django code unless it's really needed): >>>>>>>> >>>>>>>> Model A: >>>>>>>> >>>>>>>> class A(models.Model): >>>>>>>> # Fields of model A >>>>>>>> >>>>>>>> Model B (which is related to A with foreign key): >>>>>>>> >>>>>>>> class B(models.Model): >>>>>>>> a = models.ForeignKey('A', related_name='bs') >>>>>>>> >>>>>>>> Now I would like to find out all As for which there is atleast one >>>>>>>> b. The only way I know is as follows: >>>>>>>> >>>>>>>> A.objects.filter(bs__isnull=False) >>>>>>>> >>>>>>>> But the above isn't an optimal way as with large of records in A >>>>>>>> and B, the above takes lot of time. It gets more inefficient if it's a >>>>>>>> many >>>>>>>> to many relationship. >>>>>>>> >>>>>>>> Could anyone please let me know the most efficient way to use >>>>>>>> django queryset for the above scenario? >>>>>>>> >>>>>>>> Thanks. >>>>>>>> >>>>>>> -- >>>>> You received this message because you are subscribed to the Google >>>>> Groups "Django users" group. >>>>> To unsubscribe from this group and stop receiving emails from it, send >>>>> an email to [email protected]. >>>>> To post to this group, send email to [email protected]. >>>>> Visit this group at https://groups.google.com/group/django-users. >>>>> To view this discussion on the web visit >>>>> https://groups.google.com/d/msgid/django-users/73ed5ff7-d4db >>>>> -4057-a812-01c82bf08cf3%40googlegroups.com >>>>> <https://groups.google.com/d/msgid/django-users/73ed5ff7-d4db-4057-a812-01c82bf08cf3%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>> . >>>>> >>>>> For more options, visit https://groups.google.com/d/optout. >>>>> >>>> >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "Django users" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to [email protected]. >>>> To post to this group, send email to [email protected]. >>>> Visit this group at https://groups.google.com/group/django-users. >>>> To view this discussion on the web visit https://groups.google.com/d/ms >>>> gid/django-users/CALn3ei1Q2p31NrcdYC-2EPfH78kBxCXZPZKW1e6k%2 >>>> BqCTuUgYDw%40mail.gmail.com >>>> <https://groups.google.com/d/msgid/django-users/CALn3ei1Q2p31NrcdYC-2EPfH78kBxCXZPZKW1e6k%2BqCTuUgYDw%40mail.gmail.com?utm_medium=email&utm_source=footer> >>>> . >>>> For more options, visit https://groups.google.com/d/optout. >>>> >>> >>> -- >> You received this message because you are subscribed to the Google Groups >> "Django users" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> To post to this group, send email to [email protected]. >> Visit this group at https://groups.google.com/group/django-users. >> To view this discussion on the web visit https://groups.google.com/d/ms >> gid/django-users/07ef18a2-522f-4ae2-b9a6-1dc1293a413d%40googlegroups.com >> <https://groups.google.com/d/msgid/django-users/07ef18a2-522f-4ae2-b9a6-1dc1293a413d%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> >> For more options, visit https://groups.google.com/d/optout. >> > > -- > You received this message because you are subscribed to the Google Groups > "Django users" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/django-users. > To view this discussion on the web visit https://groups.google.com/d/ > msgid/django-users/CALn3ei0eF9yk0ehtibK0J%3DBLB4ozcSpvsDqq9X-t% > 3Dh4imqinRQ%40mail.gmail.com > <https://groups.google.com/d/msgid/django-users/CALn3ei0eF9yk0ehtibK0J%3DBLB4ozcSpvsDqq9X-t%3Dh4imqinRQ%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/django-users. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CACMDPqGu_jW-43%2BbypgkD8KO39mTFrynGZCrxCbnrpJOh%2BQiXQ%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
