toastdriven/django-haystack — Issue #477: Slow update_index with MySQL due to lack of primary key use
Author: dvogel
Created: 2012-01-04 17:19:30+00:00
State: open

do_update in the update_index command generates sub-optimal SQL for MySQL. Here is the method for reference:

def do_update(backend, index, qs, start, end, total, verbosity=1):
# Get a clone of the QuerySet so that the cache doesn't bloat up
# in memory. Useful when reindexing large amounts of data.
small_cache_qs = qs.all()
current_qs = small_cache_qs[start:end]

if verbosity >= 2:
if os.getpid() == os.getppid():
print " indexed %s - %d of %d." % (start+1, end, total)
else:
print " indexed %s - %d of %d (by %s)." % (start+1, end, total, os.getpid())

# FIXME: Get the right backend.
backend.update(index, current_qs)

# Clear out the DB connections queries because it bloats up RAM.
reset_queries()

The ORM generates a query of the form:

SELECT * FROM model_table ORDER BY pk ASC LIMIT end-start OFFSET start

MySQL will not use the primary key to optimize this query due to the other columns being selected. It will use the primary key to optimize this query:

SELECT * FROM model_table WHERE pk >= start ORDER BY pk ASC LIMIT end-start

This is achievable by adding a filter() call:

current_qs = small_cache_qs.filter(pk__gte=start)[:(end-start)]

This should be safe since Django doesn't support negative indexing and the ordering is already ascending in all cases (right?). If this approach causes issues for other databases, perhaps a command line option would be agreeable.