Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.4.17
-
None
-
CentOS Linux release 7.9.2009
Description
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:
- CREATE TABLE test_tbl LIKE jos_email_message;
- INSERT INTO test_tbl SELECT id,'test' FROM jos_email_message;
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.
Attachments
Issue Links
- duplicates
-
-
- Closed
-
- is duplicated by
-
-
- Closed
-