Django Admin search causes full table scan

[OscarVanL]

Overview

I run a MySQL database and use django-auditlog. I have around 4 million audit log entries, and recently when doing a search I found the performance to be very poor, and cause a high level of load on my database. This caused ~90% CPU usage on our production database. Oops 😅

Analysis

I used Django Silk to find the queries produced by making a search:

GET /admin/auditlog/logentry/
{
    "q": "z"
}

This results in 9 SQL queries, of which 3 are quite expensive and result in a full table scan (no indexing):

SELECT DISTINCT CAST(DATE_FORMAT(`auditlog_logentry`.`timestamp`, %Y-01-01 00:00:00) AS DATETIME) AS `datetimefield`
FROM `auditlog_logentry`
LEFT OUTER JOIN `auth_user` ON (`auditlog_logentry`.`actor_id` = `auth_user`.`id`)
WHERE ((`auditlog_logentry`.`timestamp` LIKE %z%
        OR `auditlog_logentry`.`object_repr` LIKE %z%
        OR LOWER(JSON_UNQUOTE(`auditlog_logentry`.`changes`)) LIKE LOWER(%z%)
        OR `auth_user`.`first_name` LIKE %z%
        OR `auth_user`.`last_name` LIKE %z%
        OR `auth_user`.`username` LIKE %z%)
       AND `auditlog_logentry`.`timestamp` IS NOT NULL)
ORDER BY 1 ASC

and

SELECT MIN(`auditlog_logentry`.`timestamp`) AS `first`,
       MAX(`auditlog_logentry`.`timestamp`) AS `last`
FROM `auditlog_logentry`
LEFT OUTER JOIN `auth_user` ON (`auditlog_logentry`.`actor_id` = `auth_user`.`id`)
WHERE (`auditlog_logentry`.`timestamp` LIKE %z%
       OR `auditlog_logentry`.`object_repr` LIKE %z%
       OR LOWER(JSON_UNQUOTE(`auditlog_logentry`.`changes`)) LIKE LOWER(%z%)
       OR `auth_user`.`first_name` LIKE %z%
       OR `auth_user`.`last_name` LIKE %z%
       OR `auth_user`.`username` LIKE %z%)

and

SELECT COUNT(*) AS `__count`
FROM `auditlog_logentry`
LEFT OUTER JOIN `auth_user` ON (`auditlog_logentry`.`actor_id` = `auth_user`.`id`)
WHERE (`auditlog_logentry`.`timestamp` LIKE %z%
       OR `auditlog_logentry`.`object_repr` LIKE %z%
       OR LOWER(JSON_UNQUOTE(`auditlog_logentry`.`changes`)) LIKE LOWER(%z%)
       OR `auth_user`.`first_name` LIKE %z%
       OR `auth_user`.`last_name` LIKE %z%
       OR `auth_user`.`username` LIKE %z%)

I tested this query with EXPLAIN ANALYZE and any one of the WHERE conditions will cause a full table scan, I guess there are no indexes that can satisfy this LIKE condition.

Desired Solution

Indexing Changes

I understand this project is not magic and exists within the confines of databases... 😅 If there were some magic index we could turn on that would make these searches not do a full table scan that would be ideal, but I understand there are complexities about adding indexes (adding them retrospectively means a very expensive database migration), and also there is a cost to storing these indexes in terms of increased storage usage. Insertions also become more expensive.

A LIKE '%' query cannot use a B-Tree index, so it is kind of inherent that this causes a full scan. However, in MySQL 8.4 there is now a FULLTEXT index type that adds some interesting query functionality.

Search Changes

If I am realistic with my preferred solution / feature request, it is that there is an approach to configure the search functionality more granularly.

For example, if I could restrict the search so that it only works once someone has selected a Resource Type or filtered down to a specific timeframe, this would at constrain the size of the full table scan by an order of magnitude in my case.

Or probably more realistically, having a way to disable search would be best.