Django QuerySet update performance

Question

Which one would be better for performance?

We take a slice of products. which make us impossible to bulk update.

products = Product.objects.filter(featured=True).order_by("-modified_on")[3:]

for product in products:
  product.featured = False
  product.save()

or (invalid)

for product in products.iterator():
  product.update(featured=False)

I have tried QuerySet's in statement too as following.

Product.objects.filter(pk__in=products).update(featured=False)

This line works fine on SQLite. But, it rises following exception on MySQL. So, I couldn't use that.

DatabaseError: (1235, "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'")

Edit: Also iterator() method causes re-evaluate the query. So, it is bad for performance.

Answer 1

As @Chris Pratt pointed out in comments, the second example is invalid because the objects don't have update methods. Your first example will require queries equal to results+1 since it has to update each object. That might really be costly if you have 1000 products. Ideally you do want to reduce this to a more fixed expense if possible.

This is a similar situation to another question:
Django: Cannot update a query once a slice has been taken

That being said, you would have to do it in at least 2 queries, but you have to be a bit sneaky on how to construct the LIMIT...

Using Q objects for complex queries:

# get the IDs we want to exclude
products = Product.objects.filter(featured=True).order_by("-modified_on")[:3]
# flatten them into just a list of ids
ids = products.values_list('id', flat=True)

# Now use the Q object to construct a complex query
from django.db.models import Q
# This builds a list of "AND id NOT EQUAL TO i"
limits = [~Q(id=i) for i in ids]
Product.objects.filter(featured=True, *limits).update(featured=False)