Django：如何提升startswith查询性能？为何索引未生效？

Hey there! Let's tackle your two questions one by one—first why the index isn't working for startswith, then how to fix the performance.

Why isn't the index being used for startswith queries? #

The most common culprit here is database collation (sorting rules). If your name field uses a case-insensitive collation (like utf8mb4_general_ci in MySQL, which is often the default), the database has to transform the name column to match the case of your query.

When you run Worker.objects.filter(name__startswith='John'), Django generates SQL like:

SELECT * FROM worker WHERE name LIKE 'John%';

But if your collation is case-insensitive, the database implicitly converts this to something equivalent to:

SELECT * FROM worker WHERE LOWER(name) LIKE LOWER('John%');

Since it's applying a function (LOWER()) to the indexed column, the regular B-tree index can't be used—indexes only work on the raw column values, not transformed ones.

On the other hand, Worker.objects.filter(name='John') works because the case-insensitive match can still use the index (the database can find all exact matches regardless of case using the B-tree index without modifying the column value).

Another rare scenario: if your table has very few rows, the database might choose a full table scan over using the index because it's faster to just read all rows instead of traversing the index. But since your exact query is fast, this is unlikely to be the issue here.

How to improve startswith query performance? #

Here are three reliable solutions depending on your needs:

1. Use a case-sensitive collation for the field #

If case sensitivity is acceptable for your startswith queries (or you can adjust your application to handle case appropriately), you can specify a case-sensitive collation directly in your Django model:

class Worker(models.Model):
    name = models.CharField(
        max_length=32, 
        db_index=True, 
        db_collation='utf8mb4_bin'  # For MySQL; use 'C' for PostgreSQL
    )

This makes the startswith query use the existing B-tree index because the database no longer needs to transform the column value to match case. Just note that exact matches will now be case-sensitive too (e.g., name='john' won't match Name='John').

2. Create a functional index for case-insensitive matches #

If you need to keep case-insensitive queries, create an index on the transformed (lowercase) version of the name column. Django 3.2+ supports functional indexes natively:

from django.db.models import Func, Index

class Worker(models.Model):
    name = models.CharField(max_length=32)

    class Meta:
        indexes = [
            Index(Func('name', function='LOWER'), name='idx_worker_lower_name'),
        ]

Now when you use name__istartswith='John' (which explicitly does a case-insensitive prefix match), Django generates SQL that uses the LOWER(name) index, making the query fast.

3. Use database-specific full-text indexes (for more flexible fuzzy searches) #

If you eventually need more than just prefix matches, consider using your database's full-text search capabilities. For example:

• In MySQL, add a FullTextIndex to the name field and use Worker.objects.filter(name__search='John')
• In PostgreSQL, enable the pg_trgm extension and create a GIN/GIST index for trigram-based searches, which work great for prefix, suffix, and partial matches.

For your specific startswith use case, options 1 or 2 are the most straightforward and efficient.

内容的提问来源于stack exchange，提问作者Max Malysh