Reducing database operation time Django/ Mysql
<p>So, the thing is I am having a moderately large list of emails ~ 250,000 entries.</p>

<p>I have another table containing list of invalid emails ~ 50,000 which i need to remove (mark inactive) from 1st table. For that I have ran a simple django function which is taking 3-4 seconds in each loop. The code is:</p>

<pre><code>def clean_list():
    id = 9
    while id&lt;40000:
        i = Invalid.objects.get(id=id)
        y = i.email.strip()
        f = IndiList.objects.get(email__contains=y)
        f.active = False
        f.save()
        id +=1
</code></pre>

<p>What would be a better way to do it? Either a SQL query or a better piece of django code or some other way.</p>

<p>Help!</p>

<p>Untested:</p>

<pre><code>IndiList.objects.filter(email__in=Invalid.objects.only('email').all()).update(active=False)
</code></pre>

<p>I am not sure if Django is smart enough to build a subquery from that, if not, then this should do:</p>

<pre><code>IndiList.objects.filter(email__in=Invalid.objects.all().values_list('email', flat=True)).update(active=False)
</code></pre>

<p>The problem with the second approach is that it will generate 2 queries instead of one, and inject 50,000 ids into the second sql query string, so I would much rather just use raw sql at this point:</p>

<pre><code>from django.db import connection

cursor = connection.cursor()
cursor.execute = 'UPDATE indilist SET active=false WHERE email IN (SELECT email FROM invalid)'
</code></pre>

python mysql django database