Problem with complex queries after migrating from MySQL 5.1 to MariaDB 10.4 I completed a migration from MySQL 5.1 master-slave system to a MariaDB 10.4 Galera Cluster. All the databases where copied over since they were on InnoDB per-file and they are working fine. However some complex queries are VERY slow on the new system since they are completely useless and I'm a little bit loss... I fixed some of them by forcing an index but I don't know how to fix others like this one...
This is the query:
SELECT * FROM (
    SELECT * FROM abvalue WHERE deviceid='XXX'
) AS abvalue LEFT JOIN (
    SELECT * FROM abperson WHERE deviceid='XXX'
) AS abperson ON abvalue.person_id=abperson.person_id

(i did the query that way because it was the most efficient way back in the day)
this query is a little slow on the old system (about 2 seconds) but it completes. However in the new system I gave up after 60 seconds... I tried forcing indexes but I was unable to fix it.
This is the explain on MySQL 5.1
+----+-------------+------------+------+----------------+----------------+---------+------+-------+-------------+
| id | select_type | table      | type | possible_keys  | key            | key_len | ref  | rows  | Extra       |
+----+-------------+------------+------+----------------+----------------+---------+------+-------+-------------+
|  1 | PRIMARY     | &lt;derived2&gt; | ALL  | NULL           | NULL           | NULL    | NULL | 12428 |             |
|  1 | PRIMARY     | &lt;derived3&gt; | ALL  | NULL           | NULL           | NULL    | NULL |   694 |             |
|  3 | DERIVED     | abperson   | ref  | deviceid_index | deviceid_index | 36      |      |   693 | Using where |
|  2 | DERIVED     | abvalue    | ref  | deviceid_index | deviceid_index | 36      |      | 16484 | Using where |
+----+-------------+------------+------+----------------+----------------+---------+------+-------+-------------+

and this is the explain on MariaDB 10.4
+------+-------------+----------+------------+-----------------------------+-----------------------------+---------+--------------------------+---------+---------------------------------+
| id   | select_type | table    | type       | possible_keys               | key                         | key_len | ref                      | rows    | Extra                           |
+------+-------------+----------+------------+-----------------------------+-----------------------------+---------+--------------------------+---------+---------------------------------+
|    1 | SIMPLE      | abvalue  | ref        | deviceid_index              | deviceid_index              | 36      | const                    | 22780   | Using where                     |
|    1 | SIMPLE      | abperson | ref|filter | deviceid_index,person_index | person_index|deviceid_index | 5|36    | ownspy.abvalue.person_id | 39 (0%) | Using where; Using rowid filter |
+------+-------------+----------+------------+-----------------------------+-----------------------------+---------+--------------------------+---------+---------------------------------+

this is the structures of the tables:
CREATE TABLE `abvalue` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `deviceid` char(32) NOT NULL,
  `value` char(128) DEFAULT NULL,
  `type` int(11) DEFAULT NULL,
  `person_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `deviceid_index` (`deviceid`(12)) USING BTREE,
  KEY `person_index` (`person_id`) USING BTREE,
  KEY `value_index` (`value`(5)) USING BTREE
) ENGINE=InnoDB

CREATE TABLE `abperson` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `deviceid` char(32) NOT NULL,
  `first` text DEFAULT NULL,
  `last` text DEFAULT NULL,
  `person_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `deviceid_index` (`deviceid`(12)) USING BTREE,
  KEY `person_index` (`person_id`) USING BTREE
) ENGINE=InnoDB

Any help is really welcomed!
I would suggest getting rid of the single-column indexes for device and person, and replace them with:
INDEX device_person_index (deviceid, person_id)

If you have queries with predicates for person_id but not deviceid, you can add another index for that:
INDEX device_person_index (person_id, deviceid)

The above suggestion holds for both tables. I find KEY in this context very confusing, so I replaced it with INDEX which I beleive is a synonym.
I don't know whether MySQL/Mariadb can do index anding, but a composite index is anyhow more efficient than two separate indexes.