1

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!

1
  • 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.
    – nbk
    Jun 20 '20 at 11:21
0

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.

1
  • 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.
    – Rick James
    Aug 31 '20 at 5:27
0
  • 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.