Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Django — improve `startswith` performance

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?

like image 810
Max Malysh Avatar asked Mar 07 '23 12:03

Max Malysh


1 Answers

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:

  1. First, create an empty migration:

    python3 manage.py makemigrations appName --empty
    
  2. 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";'
            ),
        ]
    
like image 118
Max Malysh Avatar answered Mar 15 '23 13:03

Max Malysh