Significant query performance degradation over time for only certain conditions

UPDATE: All problems described in this question disappeared after upgrade to MariaDB 10.4.17.

I've got strange situation with queries becoming increasingly slow over time.

Table with data in question has following structure:

CREATE TABLE `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `location` char(1) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `owner` int(11) NOT NULL DEFAULT 0,
  `class` tinyint(4) NOT NULL DEFAULT 0,
  `price` mediumint(8) unsigned NOT NULL DEFAULT 0,
  `created` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `location` (`location`,`owner`),
  KEY `name` (`name`),
  KEY `class` (`class`)
) ENGINE=InnoDB;

Trouble shows up when running query like that:

UPDATE `items` SET `price`=`price`+1 WHERE `location`='d' AND `owner`=5851775 AND `class`=17;
Query OK, 1 row affected (1.158 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Additional info for query above:

EXPLAIN UPDATE items SET price=price+1 WHERE location='d' AND owner=5851775 AND class=17;
+------+-------------+-------+-------------+----------------+----------------+---------+------+------+----------------------------------------------+
| id   | select_type | table | type        | possible_keys  | key            | key_len | ref  | rows | Extra                                       |
+------+-------------+-------+-------------+----------------+----------------+---------+------+------+----------------------------------------------+
|    1 | SIMPLE      | items | index_merge | location,class | location,class | 7,1     | NULL | 1    | Using intersect(location,class); Using where |
+------+-------------+-------+-------------+----------------+----------------+---------+------+------+----------------------------------------------+
1 row in set (0.000 sec)

There are 67 items with owner=5851775

Running same query but with different owner value runs in alm

Because your 'owner' column has the best cardinality for the multimillion row table,
consider adding this multi column index

KEY `itemy_idx_own_loc_cla` (`owner`,`location`,`class`)

and in your update query change the sequence of the WHERE clauses to owner,location,class

for the optimizer to make a better choice.

If all indexes started with table name, you would minimize research time when digging.

2012-12-15 To deal with innodb_buffer_pool_dirty_pages,
to reduce dirty pages, consider these suggestions.

innodb_flush_neighbors=2 # for all changes in current EXTENT to be dealt with now 
innodb_max_dirty_pages_pct_lwm=.0001 # to expedite reducing dirty pages 
innodb_max_dirty_pages_pct=.0001 # to expedite reducing dirty pages
innodb_flushing_avg_loops=5  # from 50 to minimize loop delay

Let us know where your dirty pages count is in 24 hours, please. These are ALL dynamic variables. SET GLOBAL (variable_name)=new_value may be used. After 24 hours and you have proven they work for your system, change your my.cnf to stay in this position of lower dirty pages every day.

StackExchange Database Administrators Q#281444, answer score: 2