Why Django admin search field taking too much time to response?

Question

It's my first time to handle project with large data. I have 16millions of data. When i query in django shell it give me result in few seconds. But on django admin site when i register my model and apply search field . It takes too much time to query. I tried django debug toolbar and here is the result it's showing me Django debug toolbar result

• models.py:

   class TrademarkAvailability(models.Model):
       serial_number = models.IntegerField(unique=True)
       filing_date = models.IntegerField(null=True)
       status_code = models.IntegerField(null=True)
       status_date = models.IntegerField(null=True)
       mark_identification = models.CharField(
           max_length=5000, null=True, blank=True, db_index=True)
       mark_drawing_code = models.CharField(
           max_length=5000, null=True, blank=True)
       attorney_name = models.CharField(max_length=5000, null=True, blank=True)
       current_location = models.CharField(max_length=5000, null=True, blank=True)
       employee_name = models.CharField(max_length=5000, null=True, blank=True)
       correspondent = models.JSONField()
       classifications = models.JSONField(null=True)
       case_file_owners = models.JSONField(null=True)
       transaction_date = models.JSONField(null=True)
       registration_number = models.JSONField(null=True)
       case_file_statements = models.JSONField(null=True)
       case_file_event_statements = models.JSONField(null=True)
  

• admin.py

   @admin.register(TrademarkAvailability)
   class TrademarkAdmin(admin.ModelAdmin):
       list_display = ("mark_identification",)
       search_fields = ["=mark_identification"]
       paginator = LargeTablePaginator
       show_full_result_count = False
  

Answer 1

There might be a few problems but a good place to start would be an index with UPPER, so a case insensitive index.

If you are running Django < 3.2, that's how you create a case insensitive index on mark_identification.

CREATE INDEX mark_identification_insensitive_idx ON app_trademarkavailability (upper(mark_identification));

If Django >= 3.2

 class TrademarkAvailability(models.Model):
     serial_number = models.IntegerField(unique=True)
     filing_date = models.IntegerField(null=True)
     status_code = models.IntegerField(null=True)
     status_date = models.IntegerField(null=True)
     mark_identification = models.CharField(
         max_length=5000, null=True, blank=True, db_index=True)
     mark_drawing_code = models.CharField(
         max_length=5000, null=True, blank=True)
     attorney_name = models.CharField(max_length=5000, null=True, blank=True)
     current_location = models.CharField(max_length=5000, null=True, blank=True)
     employee_name = models.CharField(max_length=5000, null=True, blank=True)
     correspondent = models.JSONField()
     classifications = models.JSONField(null=True)
     case_file_owners = models.JSONField(null=True)
     transaction_date = models.JSONField(null=True)
     registration_number = models.JSONField(null=True)
     case_file_statements = models.JSONField(null=True)
     case_file_event_statements = models.JSONField(null=True)

    class Meta:
        indexes = [
            Index(
                Upper('mark_identification'),
                name='mark_identification_insensitive_idx',
            ),
        ]

Remember you can always use SQL explain.

EDIT:

class Migration(migrations.Migration):

    dependencies = [
        ('app', 'previous_mig'),
    ]

    operations = [
        migrations.RunSQL(
            sql=r'CREATE INDEX "mark_identification_insensitive_idx" ON "app_trademarkavailability" (upper("mark_identification"));',
            reverse_sql=r'DROP INDEX "mark_identification_insensitive_idx";'
        ),
    ]