Problem with complex queries after migrating from MySQL 5.1 to MariaDB 10.4

Question

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 | <derived2> | ALL | NULL | NULL | NULL | NULL | 12428 | |
| 1 | PRIMARY | <derived3> | 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!

• nbk: try not to use SELECT * for your queries and see that the join buffer is big enough to fit both resultset. a composite index could help.

Answer 1

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.

• Rick James: Yes, KEY and INDEX are synonyms in MySQL/MariaDB. KEY is what SHOW generates (for whatever reason). "Index merge" was added to MySQL in 5.0 (2005); the 'intersect' variant is very rarely used, and (as you point out) is almost always not as efficient as a composite index.

Answer 2

• Don't use index prefixing; it is often less efficient than indexing the entire column.

• Don't use subqueries when you don't need to. The Optimizer has to stand on its head, and does not have good techniques for such. Probably the Optimizer made an improvement that falls apart with your particular query.

• Use CHAR only for truly fixed length data. Use VARCHAR instead.

   SELECT *
   FROM abvalue WHERE deviceid='XXX'
   LEFT JOIN abperson WHERE deviceid='XXX'
   ON abvalue.person_id =
   abperson.person_id
  

• On each table: INDEX(deviceid, person_id)

and get rid of

 KEY `deviceid_index` (`deviceid`(12)) USING BTREE,

• If first and last are names, TEXT is probably overkill. And it slows down queries. Switch to a VARCHAR with a realistic max length.

• From the 36 versus deviceid(12), I deduce that both versions were using CHARSET utf8. I'll bet your device ids have only ascii characters. If so, change the charset of that, and any other columns, where appropriate. This will improve both speed and space.

My suggestions probably apply to both 5.1 and 10.4.