Запрос MySQL медленный только при использовании поля ORDER BY DESC и LIMIT

Обзор

Я использую MySQL 5.7.30-33 и столкнулся с проблемой, похожей на то, что MySQL использует неправильный индекс при выполнении запроса. Я получаю 3-секундное время запроса, используя мой существующий запрос. Однако, просто изменив ORDER BY, удалив LIMIT или форсировав USE INDEX, я могу получить время запроса 0,01 секунды. К сожалению, мне нужно придерживаться моего исходного запроса (он встроен в приложение), поэтому было бы здорово, если бы это несоответствие можно было устранить в схеме/индексации.

Настройка/проблема

Моя структура таблицы выглядит следующим образом:

CREATE TABLE `referrals` (
  `__id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `systemcreated` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `referrerid` mediumtext COLLATE utf8mb4_unicode_ci,
  `referrersiteid` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  ... lots more mediumtext fields ...
  PRIMARY KEY (`__id`),
  KEY `systemcreated` (`systemcreated`,`referrersiteid`,`__id`)
) ENGINE=InnoDB AUTO_INCREMENT=53368 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED

В таблице всего около 55 тысяч строк, но она очень широкая, так как некоторые поля содержат огромные BLOB-объекты:

mysql> show table status like 'referrals'\G;
*************************** 1. row ***************************
           Name: referrals
         Engine: InnoDB
        Version: 10
     Row_format: Compressed
           Rows: 45641
 Avg_row_length: 767640
    Data_length: 35035897856
Max_data_length: 0
   Index_length: 3653632
      Data_free: 3670016
 Auto_increment: 54008
    Create_time: 2020-12-12 12:46:14
    Update_time: 2020-12-12 17:50:28
     Check_time: NULL
      Collation: utf8mb4_unicode_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED
        Comment: 
1 row in set (0.00 sec)

Приложение моего клиента запрашивает таблицу, используя это, и, к сожалению, это нельзя легко изменить:

SELECT  *
    FROM  referrals
    WHERE  `systemcreated` LIKE 'XXXXXX%'
      AND  `referrersiteid` LIKE 'XXXXXXXXXXXX%'
    order by  __id desc
    limit  16;

Это приводит к тому, что время запроса составляет около 3 секунд.

ОБЪЯСНЕНИЕ выглядит следующим образом:

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | referrals   | NULL       | index | systemcreated | PRIMARY | 4       | NULL |   32 |     5.56 | Using where |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

Обратите внимание, что для запроса используется ПЕРВИЧНЫЙ ключ, а не индекс systemcreated.

Эксперимент 1

Если я изменю запрос, чтобы использовать ASC, а не DESC:

SELECT  *
    FROM  referrals
    WHERE  `systemcreated` LIKE 'XXXXXX%'
      AND  `referrersiteid` LIKE 'XXXXXXXXXXXX%'
    order by  __id asc
    limit  16;

затем это занимает 0,01 секунды, и EXPLAIN выглядит так же:

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | referrals   | NULL       | index | systemcreated | PRIMARY | 4       | NULL |   32 |     5.56 | Using where |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

Эксперимент 2

Если я изменю запрос, чтобы придерживаться ORDER BY __id DESC, но удалю LIMIT:

SELECT  *
    FROM  referrals
    WHERE  `systemcreated` LIKE 'XXXXXX%'
      AND  `referrersiteid` LIKE 'XXXXXXXXXXXX%'
    order by  __id desc;

то это также занимает 0,01 секунды с EXPLAIN следующим образом:

+----+-------------+-------------+------------+-------+---------------+---------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table       | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------------+------------+-------+---------------+---------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | referrals   | NULL       | range | systemcreated | systemcreated | 406     | NULL | 2086 |    11.11 | Using index condition; Using filesort |
+----+-------------+-------------+------------+-------+---------------+---------------+---------+------+------+----------+---------------------------------------+

Эксперимент 3

В качестве альтернативы, если я заставлю исходный запрос использовать индекс systemcreated, он также даст время запроса 0,01 секунды. Вот ОБЪЯСНЕНИЕ:

mysql> explain     SELECT  *
    FROM  referrals USE INDEX (systemcreated)
    WHERE  `systemcreated` LIKE 'XXXXXX%'
      AND  `referrersiteid` LIKE 'XXXXXXXXXXXX%'
    order by  __id desc
    limit  16;

+----+-------------+--------------+------------+-------+---------------+---------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table        | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+--------------+------------+-------+---------------+---------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | referrals    | NULL       | range | systemcreated | systemcreated | 406     | NULL | 2086 |    11.11 | Using index condition; Using filesort |
+----+-------------+--------------+------------+-------+---------------+---------------+---------+------+------+----------+---------------------------------------+

Эксперимент 4

Наконец, если я использую исходный ORDER BY __id DESC LIMIT 16, но выбираю меньше полей, то он также возвращается через 0,01 секунды! Вот объяснение:

mysql> explain     SELECT  field1, field2, field3, field4, field5
    FROM  referrals
    WHERE  `systemcreated` LIKE 'XXXXXX%'
      AND  `referrersiteid` LIKE 'XXXXXXXXXXXX%'
    order by  __id desc
    limit  16;

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | referrals   | NULL       | index | systemcreated | PRIMARY | 4       | NULL |   32 |     5.56 | Using where |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

Краткое содержание

Таким образом, единственная комбинация, которая, кажется, работает плохо, — это ORDER BY __id DESC LIMIT 16.

Я думаю, что у меня правильно настроены индексы. Я запрашиваю через поля systemcreated и referrersiteid и упорядочиваю по __id, поэтому у меня есть индекс, определенный как (systemcreated, referrersiteid, __id), но MySQL все еще использует ПЕРВИЧНЫЙ ключ.

Какие-либо предложения?