How to improve performance of the following query?
class Worker(models.Model):
name = models.CharField(max_length=32, db_index=True)
# This line is slow:
Worker.objects.filter(name__startswith='John')
I have already added an index to the model, but... it's simply not used. However, index does kick in when I do ordinary filtering without startswith
:
# This line is fast:
Worker.objects.filter(name='John')
Why index is not used with startswith
?
The problem is that startswith
expression translates to an SQL query containing the LIKE
operator, which doesn't take advantage of the default index.
Solution: add an additional index with a special operator class:
CREATE INDEX "appname_model_field_like_idx"
ON "appname_model" ("fieldname" varchar_pattern_ops);
Step by step:
First, create an empty migration:
python3 manage.py makemigrations appName --empty
Add a custom RunSQL command:
class Migration(migrations.Migration):
dependencies = [
('stats', '0002_auto_2010213_0159.py'),
]
operations = [
migrations.RunSQL(
sql=r'''CREATE INDEX "appname_model_field_like_idx"
ON "appname_model" ("fieldname" varchar_pattern_ops);''',
reverse_sql=r'DROP INDEX "appname_model_field_like_idx";'
),
]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With