Attached file reproduces: a simple select using no where clause but using limit, with order-by on an unindexed attribute, against a table with a little over 500,000 rows, takes about 0.150s in 10.5.15, while in 10.6.8, 10.7.4, and 10.8.3 the same query consistently takes about 10s. When an index is added, the difference is much more dramatic. 10.5 drops from 0.150s to 0.004s, while 10.6, 10.7, and 10.8 all remain unchanged at 10s.
The explain plan shows that 10.6-10.8 always create a temporary table in addition to using filesort (or an index), while 10.5 just uses filesort in the first case and index in the second w/o needing to create a temporary table in either case. Increasing sort_buffer_size up to 512M makes no difference. One significant change is that the metadata (system) character set in 10.5 is utf8, whereas starting with 10.6 it's utf8mb3.
MariaDB [test]> show create table TEST_Table\G
|
*************************** 1. row ***************************
|
Table: TEST_Table
|
Create Table: CREATE TABLE `TEST_Table` (
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
`categoryLookupId` int(11) DEFAULT NULL,
|
`date` datetime DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
KEY `aap_category` (`categoryLookupId`),
|
KEY `TEST_Table_date_cat` (`date`,`categoryLookupId`)
|
) ENGINE=InnoDB AUTO_INCREMENT=599441 DEFAULT CHARSET=utf8mb3
|
1 row in set (0.001 sec)
|
select date, `TEST_RETURN_LOOKUP_VALUE`(`categoryLookupId`) AS `propertyCategory` from `TEST_Table` order by date desc limit 25;
|
Unindexed 10.5 explain:
+------+-------------+------------+------+---------------+------+---------+------+--------+----------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+------+---------------+------+---------+------+--------+----------------+
|
| 1 | SIMPLE | TEST_Table | ALL | NULL | NULL | NULL | NULL | 598694 | Using filesort |
|
+------+-------------+------------+------+---------------+------+---------+------+--------+----------------+
|
Unindexed 10.6, 10.7, 10.8 explain
+------+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
|
| 1 | SIMPLE | TEST_Table | ALL | NULL | NULL | NULL | NULL | 1 | Using temporary; Using filesort |
|
+------+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
|
Typical indexed 10.5 performance
MariaDB [test]> select date, `TEST_RETURN_LOOKUP_VALUE`(`categoryLookupId`) AS `propertyCategory` from `TEST_Table` order by date desc limit 5;
|
+---------------------+----------------------------------+
|
| date | propertyCategory |
|
+---------------------+----------------------------------+
|
| 2022-07-18 19:49:09 | 74968a188eb4f4dcc022669eff344f0f |
|
| 2022-07-18 19:49:09 | 1a110af0481e6221693ec1ac8f9ebee6 |
|
| 2022-07-18 19:49:04 | 74968a188eb4f4dcc022669eff344f0f |
|
| 2022-07-18 19:49:01 | b323588d8d57ba607870ccf1df312b5c |
|
| 2022-07-18 19:48:57 | 822b38cf00f26ad6cdc0c59f5b0f6016 |
|
+---------------------+----------------------------------+
|
5 rows in set (0.002 sec)
|
Typical unindexed 10.5 performance
MariaDB [test]> select date, `TEST_RETURN_LOOKUP_VALUE`(`categoryLookupId`) AS `propertyCategory` from `TEST_Table` order by date desc limit 5;
|
+---------------------+----------------------------------+
|
| date | propertyCategory |
|
+---------------------+----------------------------------+
|
| 2022-07-18 19:49:09 | 1a110af0481e6221693ec1ac8f9ebee6 |
|
| 2022-07-18 19:49:09 | 74968a188eb4f4dcc022669eff344f0f |
|
| 2022-07-18 19:49:04 | 74968a188eb4f4dcc022669eff344f0f |
|
| 2022-07-18 19:49:01 | b323588d8d57ba607870ccf1df312b5c |
|
| 2022-07-18 19:48:57 | 822b38cf00f26ad6cdc0c59f5b0f6016 |
|
+---------------------+----------------------------------+
|
5 rows in set (0.104 sec)
|
Typical indexed 10.6 through 10.8 performance
MariaDB [test]> select date, `TEST_RETURN_LOOKUP_VALUE`(`categoryLookupId`) AS `propertyCategory` from `TEST_Table` order by date desc limit 5;
|
+---------------------+----------------------------------+
|
| date | propertyCategory |
|
+---------------------+----------------------------------+
|
| 2022-07-18 19:49:05 | 47fddf54db605db008ba78df7ab6c02e |
|
| 2022-07-18 19:49:05 | 19736280721c57a798064fad686b684a |
|
| 2022-07-18 19:49:04 | 19736280721c57a798064fad686b684a |
|
| 2022-07-18 19:49:03 | 47fddf54db605db008ba78df7ab6c02e |
|
| 2022-07-18 19:49:00 | 5c884e67b699c62af82394735b4dfe1a |
|
+---------------------+----------------------------------+
|
5 rows in set (9.803 sec)
|
Typical unindexed 10.6 through 10.8 performance
MariaDB [test]> select date, `TEST_RETURN_LOOKUP_VALUE`(`categoryLookupId`) AS `propertyCategory` from `TEST_Table` order by date desc limit 5;
|
+---------------------+----------------------------------+
|
| date | propertyCategory |
|
+---------------------+----------------------------------+
|
| 2022-07-18 19:49:05 | 47fddf54db605db008ba78df7ab6c02e |
|
| 2022-07-18 19:49:05 | 19736280721c57a798064fad686b684a |
|
| 2022-07-18 19:49:04 | 19736280721c57a798064fad686b684a |
|
| 2022-07-18 19:49:03 | 47fddf54db605db008ba78df7ab6c02e |
|
| 2022-07-18 19:49:00 | 5c884e67b699c62af82394735b4dfe1a |
|
+---------------------+----------------------------------+
|
5 rows in set (9.681 sec)
|
Bug
Critical
MDEV-24089 support oracle syntax: rownum
