title: MySQL query is slow only when using ORDER BY field DESC and LIMIT
body: <h2>Overview</h2>
  <p>I'm running MySQL 5.7.30-33, and I'm hitting an issue that seems like MySQL is using the wrong index when running a query. I'm getting a 3 second query time using my existing query. However, just by changing the ORDER BY, removing the LIMIT, or forcing a USE INDEX I can get a 0.01 second query time. Unfortunately I need to stick with my original query (it's baked into an application), so it'd be great if this disparity could be resolved in the schema/indexing.</p>
  <h2>Setup / problem</h2>
  <p>My table structure is as follows:</p>
  <pre><code>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
  </code></pre>
  <p>The table only has ~55k rows, but is very wide, as some of the fields contain huge BLOBs:</p>
  <pre><code>mysql&gt; 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)
  </code></pre>
  <p>My customer's application queries the table using this, and unfortunately that can't easily be changed:</p>
  <pre><code>SELECT  *
      FROM  referrals
      WHERE  `systemcreated` LIKE 'XXXXXX%'
        AND  `referrersiteid` LIKE 'XXXXXXXXXXXX%'
      order by  __id desc
      limit  16;
  </code></pre>
  <p>This results in a query time around 3 seconds.</p>
  <p>The EXPLAIN looks like this:</p>
  <pre><code>+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  | 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 |
  +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  </code></pre>
  <p>Note that it is using the PRIMARY key for the query rather than the <code>systemcreated</code> index.</p>
  <h2>Experimentation 1</h2>
  <p>If I change the query to use ASC rather than DESC:</p>
  <pre><code>SELECT  *
      FROM  referrals
      WHERE  `systemcreated` LIKE 'XXXXXX%'
        AND  `referrersiteid` LIKE 'XXXXXXXXXXXX%'
      order by  __id asc
      limit  16;
  </code></pre>
  <p>then it takes 0.01 seconds, and the EXPLAIN looks to be the same:</p>
  <pre><code>+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  | 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 |
  +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  </code></pre>
  <h2>Experimentation 2</h2>
  <p>If I change the query to stick with ORDER BY __id DESC, but remove the LIMIT:</p>
  <pre><code>SELECT  *
      FROM  referrals
      WHERE  `systemcreated` LIKE 'XXXXXX%'
        AND  `referrersiteid` LIKE 'XXXXXXXXXXXX%'
      order by  __id desc;
  </code></pre>
  <p>then it also takes 0.01 seconds, with an EXPLAIN like this:</p>
  <pre><code>+----+-------------+-------------+------------+-------+---------------+---------------+---------+------+------+----------+---------------------------------------+
  | 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 |
  +----+-------------+-------------+------------+-------+---------------+---------------+---------+------+------+----------+---------------------------------------+
  </code></pre>
  <h2>Experimentation 3</h2>
  <p>Alternatively, if I force the original query to use the <code>systemcreated</code> index then it also gives a 0.01 sec query time. Here's the EXPLAIN:</p>
  <pre><code>mysql&gt; 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 |
  +----+-------------+--------------+------------+-------+---------------+---------------+---------+------+------+----------+---------------------------------------+
  </code></pre>
  <h2>Experimentation 4</h2>
  <p>Lastly, if I use the original ORDER BY __id DESC LIMIT 16 but select fewer fields, then it also returns in 0.01 seconds! Here's the explain:</p>
  <pre><code>mysql&gt; 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 |
  +----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  </code></pre>
  <h2>Summary</h2>
  <p>So the only combination that seems to be performing poorly is <code>ORDER BY __id DESC LIMIT 16</code>.</p>
  <p>I <em>think</em> I have the indexes setup correctly. I'm querying via the <code>systemcreated</code> and <code>referrersiteid</code> fields, and ordering by __id, so I have an index defined as (systemcreated, referrersiteid, __id), but MySQL still seems to be using the PRIMARY key.</p>
  <p>Any suggestions?</p>