Re: performance of delete

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.martell@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+unsubscribe@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+unsubscribe@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.