[MDEV-24438] Primary KEY not used in range lookups - Jira

We noticed one issue on MariaDB 10.4.17 , the primary key isn't used on range lookups (full table scan is done):

# show create table jos_email_message\G

Table: jos_email_message

Create Table: CREATE TABLE `jos_email_message` (

  `id` int(11) NOT NULL,

  `email_message` longtext NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2

# explain format=json SELECT count(id) FROM jos_email_message WHERE `id` between 2114506 AND 2114624\G

EXPLAIN: {

  "query_block": {

    "select_id": 1,

    "table": {

      "table_name": "jos_email_message",

      "access_type": "index",

      "possible_keys": ["PRIMARY"],

      "key": "PRIMARY",

      "key_length": "4",

      "used_key_parts": ["id"],

      "rows": 20489158,

      "filtered": 5.8e-4,

      "attached_condition": "jos_email_message.`id` between 2114506 and 2114624",

      "using_index": true

# explain SELECT count(`id`) FROM jos_email_message WHERE `id` between 2114506 AND 2114624;

+------+-------------+--------------------------+-------+---------------+---------+---------+------+----------+--------------------------+

| id   | select_type | table                    | type  | possible_keys | key     | key_len | ref  | rows     | Extra                    |

+------+-------------+--------------------------+-------+---------------+---------+---------+------+----------+--------------------------+

|    1 | SIMPLE      | jos_email_message | index | PRIMARY       | PRIMARY | 4       | NULL | 20489158 | Using where; Using index |

+------+-------------+--------------------------+-------+---------------+---------+---------+------+----------+--------------------------+

1 row in set (0.001 sec)

# explain SELECT count(`id`) FROM jos_email_message WHERE `id` >= 2114506;

+------+-------------+--------------------------+-------+---------------+---------+---------+------+----------+--------------------------+

| id   | select_type | table                    | type  | possible_keys | key     | key_len | ref  | rows     | Extra                    |

+------+-------------+--------------------------+-------+---------------+---------+---------+------+----------+--------------------------+

|    1 | SIMPLE      | jos_email_message | index | PRIMARY       | PRIMARY | 4       | NULL | 20489158 | Using where; Using index |

+------+-------------+--------------------------+-------+---------------+---------+---------+------+----------+--------------------------+

1 row in set (0.006 sec)

# explain SELECT count(`id`) FROM jos_email_message WHERE `id` <= 2114506;

+------+-------------+--------------------------+-------+---------------+---------+---------+------+----------+--------------------------+

| id   | select_type | table                    | type  | possible_keys | key     | key_len | ref  | rows     | Extra                    |

+------+-------------+--------------------------+-------+---------------+---------+---------+------+----------+--------------------------+

|    1 | SIMPLE      | jos_email_message | index | PRIMARY       | PRIMARY | 4       | NULL | 20489158 | Using where; Using index |

+------+-------------+--------------------------+-------+---------------+---------+---------+------+----------+--------------------------+

1 row in set (0.004 sec)

# explain SELECT count(`id`) FROM jos_email_message WHERE `id` = 2114506;

+------+-------------+--------------------------+-------+---------------+---------+---------+-------+------+-------------+

| id   | select_type | table                    | type  | possible_keys | key     | key_len | ref   | rows | Extra       |

+------+-------------+--------------------------+-------+---------------+---------+---------+-------+------+-------------+

|    1 | SIMPLE      | jos_email_message | const | PRIMARY       | PRIMARY | 4       | const | 1    | Using index |

+------+-------------+--------------------------+-------+---------------+---------+---------+-------+------+-------------+

1 row in set (0.001 sec)

We didn't have this problem on 10.4.13, prior to upgrading to 10.4.17, and we don't have this problem on 10.3.27 (master data)

# explain SELECT count(`id`) FROM jos_email_message WHERE `id` between 2114506 AND 2114624;

+------+-------------+--------------------------+-------+---------------+---------+---------+------+------+--------------------------+

| id   | select_type | table                    | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |

+------+-------------+--------------------------+-------+---------------+---------+---------+------+------+--------------------------+

|    1 | SIMPLE      | jos_email_message | range | PRIMARY       | PRIMARY | 4       | NULL |  119 | Using where; Using index |

+------+-------------+--------------------------+-------+---------------+---------+---------+------+------+--------------------------+

We've tried re-creating the table, doing analyze and forcing the index, they didn't do any difference:

ANALYZE TABLE jos_email_message;
ALTER TABLE jos_email_message FORCE;
SELECT count(`id`) FROM jos_email_message FORCE INDEX(`PRIMARY`) WHERE `id` between 2114506 AND 2114624;

I've done a copy of the table, but using a simple string on email_message field, instead of the HTML text we usually store there:

and on the new table the index lookup works just fine:

# explain SELECT count(`id`) FROM `test_tbl` WHERE `id` between 2114506 AND 2114624;

+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |

+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

|    1 | SIMPLE      | test_tbl   | range | PRIMARY       | PRIMARY | 4       | NULL | 119  | Using where; Using index |

+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

The only difference between those two would be the size, the original one is around 64Gb and the test one was only around 500Mb.

Again, we didn't face this problem on 10.4.13 and don't have this problem on 10.3.27 with the exact same data.

Seems to be a regression introduced recently in MariaDB 10.4 branch.

Just let me know if you need any other info.

duplicates

MDEV-24275 InnoDB persistent stats analyze forces full scan forcing lock crash

is duplicated by

MDEV-24266 Possible optimizer regression on 10.4.17 with DELETE statements

MariaDB Server