Related to the use of IN, but not an answer, I've found this series of article from ExplainExtended to be super helpful in optimizing some of my DB queries. These two are for MySQL, but the author has versions for Postgres and SQL Server as well. I know this help is limited with an ORM, but maybe with some clever django-ing you can utilize the core principles of this.
IN vs JOIN vs EXISTS: http://explainextended.com/2009/06/16/in-vs-join-vs-exists/ NOT IN vs NOT EXISTS vs LEFT JOIN / IS NULL: http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ Nick Santos Executive Director, The Environmental Consumer You sent this email to ultraa...@gmail.com - please note that my current address is n...@enviroconsumer.org On Wed, Apr 24, 2013 at 7:22 PM, Matt Long <m...@crocodoc.com> wrote: > I'm curious how long manually running a single delete query with the > appropriate where clause would take. Surely there's a way to do that > through the Django ORM without having to drop into raw SQL? > > That being said, there are limits to how long a given query can be, so the > ORM would have to do some intelligent batching of your IN clause, so maybe > it does make sense to leave that hairy logic out of the ORM :-) > > -- > Matt Long > Sent with Sparrow <http://www.sparrowmailapp.com/?sig> > > On Wednesday, April 24, 2013 at 18:31, Larry Martell wrote: > > I changed it to delete one row at a time and it's taking 3 minutes. > > On Wed, Apr 24, 2013 at 6:38 PM, Setiaman Lee <setiaman....@gmail.com> > wrote: > > Larry, > > > Avoid to use IN clause. I'd say to insert the events you want to delete > into > the temp table. You can put the session id or user id in temp table to > differentiate set of data from other users. Then do the delete by joining > the temp table to the Event_Message_ldx table. your sql might look like > this: > > delete event_message_ldx > from temp > where event_message_ldx.event_id = temp.event_id > and temp.userid = %s > > above SQL can be executed in Django using RAW SQL. See the link below. > https://docs.djangoproject.com/en/dev/topics/db/sql/ > > Test the SQL in the MySQL workspace when it works then move the code to the > Django. > > Rgrds, > Setiaman Lee > > > > > > On Thu, Apr 25, 2013 at 8:12 AM, Larry Martell <larry.mart...@gmail.com> > wrote: > > > I have a table that has 2.5 million rows and 9 columns that are all > int except for 2 varchar(255) - i.e. not that big of a table. I am > executing this statement: > > Event_Message_Idx.objects.filter(event_id__in=event_ids).delete() > > Where event_ids has around 1,500 items in it. There is an index on > event_id. This statement is taking 1 hour and 5 minutes to run. There > is nothing else hitting the database at that time, and the machine > it's running on is 97% idle and has plenty of free memory. This seems > extremely excessive to me. I would guess it's because of the in > clause. Perhaps this is more of a MySQL question when a django one, > but is there some better way to do a delete like this in django? > > -- > 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 django-users+unsubscr...@googlegroups.com. > To post to this group, send email to django-users@googlegroups.com. > Visit this group at http://groups.google.com/group/django-users?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- > 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 django-users+unsubscr...@googlegroups.com. > To post to this group, send email to django-users@googlegroups.com. > Visit this group at http://groups.google.com/group/django-users?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- > 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 django-users+unsubscr...@googlegroups.com. > To post to this group, send email to django-users@googlegroups.com. > Visit this group at http://groups.google.com/group/django-users?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- > 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 django-users+unsubscr...@googlegroups.com. > To post to this group, send email to django-users@googlegroups.com. > Visit this group at http://groups.google.com/group/django-users?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- 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 django-users+unsubscr...@googlegroups.com. To post to this group, send email to django-users@googlegroups.com. Visit this group at http://groups.google.com/group/django-users?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
