0.07s query on MySQL takes 11.68s on MariaDB?

Question

There is different hardware involved (MySQL is on my laptop, MariaDB on the server) but usually the difference is at most 2x not 166x!

The tables contain the same data on each instance (18,000 rows in _cache_card and 157,000 rows in card_legality).

THE QUERY

SELECT * FROM _cache_card AS c 
WHERE c.id IN (SELECT card_id FROM card_legality WHERE format_id = 35);

EXPLAIN

MariaDB:

+------+--------------+---------------+------+---------------------------------+-----------+---------+-------+-------+-------------------------------------------------+
| id   | select_type  | table         | type | possible_keys                   | key       | key_len | ref   | rows  | Extra                                           |
+------+--------------+---------------+------+---------------------------------+-----------+---------+-------+-------+-------------------------------------------------+
|    1 | PRIMARY      | <subquery2>   | ALL  | distinct_key                    | NULL      | NULL    | NULL  |  9414 |                                                 |
|    1 | PRIMARY      | c             | ALL  | NULL                            | NULL      | NULL    | NULL  | 18567 | Using where; Using join buffer (flat, BNL join) |
|    2 | MATERIALIZED | card_legality | ref  | format_id,idx_card_id_format_id | format_id | 4       | const |  9414 |                                                 |
+------+--------------+---------------+------+---------------------------------+-----------+---------+-------+-------+-------------------------------------------------+

MySQL:

+----+--------------+---------------+------------+--------+---------------------------------+------------+---------+------------+-------+----------+-------------+
| id | select_type  | table         | partitions | type   | possible_keys                   | key        | key_len | ref        | rows  | filtered | Extra       |
+----+--------------+---------------+------------+--------+---------------------------------+------------+---------+------------+-------+----------+-------------+
|  1 | SIMPLE       | c             | NULL       | ALL    | NULL                            | NULL       | NULL    | NULL       | 18055 |   100.00 | Using where |
|  1 | SIMPLE       | <subquery2>   | NULL       | eq_ref | <auto_key>                      | <auto_key> | 4       | cards.c.id |     1 |   100.00 | NULL        |
|  2 | MATERIALIZED | card_legality | NULL       | ref    | format_id,idx_card_id_format_id | format_id  | 4       | const      | 37828 |   100.00 | NULL        |
+----+--------------+---------------+------------+--------+---------------------------------+------------+---------+------------+-------+----------+-------------+

CREATE TABLE

Both:

CREATE TABLE `card_legality` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `card_id` int(11) NOT NULL,
  `format_id` int(11) NOT NULL,
  `legality` varchar(190) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `format_id` (`format_id`),
  KEY `idx_card_id_format_id` (`card_id`,`format_id`,`legality`),
  CONSTRAINT `card_legality_ibfk_1` FOREIGN KEY (`card_id`) REFERENCES `card` (`id`),
  CONSTRAINT `card_legality_ibfk_2` FOREIGN KEY (`format_id`) REFERENCES `format` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1190863 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

(The output here is character-for-character identical.)

MariaDB:

CREATE TABLE `_cache_card` (
  `id` int(11) NOT NULL DEFAULT 0,
  `layout` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `face_id` int(11) NOT NULL DEFAULT 0,
  `name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mana_cost` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `cmc` double DEFAULT NULL,
  `power` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `toughness` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `loyalty` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `text` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `search_text` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `image_name` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `hand` mediumtext DEFAULT NULL,
  `life` mediumtext DEFAULT NULL,
  `starter` mediumtext DEFAULT NULL,
  `position` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name_ascii` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `card_id` mediumtext DEFAULT NULL,
  `names` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `legalities` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `pd_legal` int(1) DEFAULT NULL,
  `bugs` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  KEY `idx_name_name` (`name`(142))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

MySQL:

CREATE TABLE `_cache_card` (
  `id` int(11) NOT NULL DEFAULT '0',
  `layout` varchar(190) COLLATE utf8mb4_unicode_ci NOT NULL,
  `face_id` int(11) NOT NULL DEFAULT '0',
  `name` longtext COLLATE utf8mb4_unicode_ci,
  `mana_cost` mediumtext COLLATE utf8mb4_unicode_ci,
  `cmc` double DEFAULT NULL,
  `power` mediumtext COLLATE utf8mb4_unicode_ci,
  `toughness` mediumtext COLLATE utf8mb4_unicode_ci,
  `loyalty` mediumtext COLLATE utf8mb4_unicode_ci,
  `type` longtext COLLATE utf8mb4_unicode_ci,
  `text` mediumtext COLLATE utf8mb4_unicode_ci,
  `search_text` mediumtext COLLATE utf8mb4_unicode_ci,
  `image_name` mediumtext COLLATE utf8mb4_unicode_ci,
  `hand` mediumtext CHARACTER SET utf8mb4,
  `life` mediumtext CHARACTER SET utf8mb4,
  `starter` mediumtext CHARACTER SET utf8mb4,
  `position` mediumtext COLLATE utf8mb4_unicode_ci,
  `name_ascii` longtext COLLATE utf8mb4_unicode_ci,
  `card_id` mediumtext CHARACTER SET utf8mb4,
  `names` mediumtext COLLATE utf8mb4_unicode_ci,
  `legalities` mediumtext COLLATE utf8mb4_unicode_ci,
  `pd_legal` int(1) DEFAULT NULL,
  `bugs` mediumtext COLLATE utf8mb4_unicode_ci,
  KEY `idx_name_name` (`name`(142))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

OTHER DETAILS

MariaDB:

version=10.2.16-MariaDB
optimizer_switch=index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on

MySQL:

version=5.7.17
optimizer_switch=index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on

ADDING INDEX ON card_legality(format_id,card_id)

This somehow makes things slower.

 CREATE INDEX idx_format_id_card_id ON card_legality(format_id,card_id); 

Now above 15s.

EXPLAIN (on MariaDB) says:

+------+--------------+---------------+------+---------------------------------------------+-----------------------+---------+-------+-------+-------------------------------------------------+
| id   | select_type  | table         | type | possible_keys                               | key                   | key_len | ref   | rows  | Extra                                           |
+------+--------------+---------------+------+---------------------------------------------+-----------------------+---------+-------+-------+-------------------------------------------------+
|    1 | PRIMARY      | <subquery2>   | ALL  | distinct_key                                | NULL                  | NULL    | NULL  | 16942 |                                                 |
|    1 | PRIMARY      | c             | ALL  | NULL                                        | NULL                  | NULL    | NULL  | 17653 | Using where; Using join buffer (flat, BNL join) |
|    2 | MATERIALIZED | card_legality | ref  | idx_card_id_format_id,idx_format_id_card_id | idx_format_id_card_id | 4       | const | 16942 | Using index                                     |
+------+--------------+---------------+------+---------------------------------------------+-----------------------+---------+-------+-------+-------------------------------------------------+

Answer 1

The reason for the difference started when MySQL 5.6 and MariaDB 10.0 came out -- they had separately developed several improved optimizations. You hit a construct involving IN where one made significant improvements that the other did not (an perhaps has not yet) picked up.

Avoid IN ( SELECT ... ) whenever a JOIN is practical.

EXISTS( SELECT 1 ... ) is another construct to experiment with.

Indexes:

PRIMARY KEY on every table !
card_legality:  INDEX(format_id, card_id) -- in this order
_cache_card:  (id)  -- This seems like a serious omission !

Something that hurts performance: using *TEXT when a smallish VARCHAR would suffice.

When timing, run the query twice. The first gets things copied into RAM (buffer_pool); the second is realistic for comparison.

How much RAM? What is the value of innodb_buffer_pool_size for each?

Answer 2

It's unclear why MariaDB currently uses this suboptimal execution plan. It might be assuming something wrong about your data distribution (although I am not really sure in which case that would be the optimal plan). It might help to use optimize table card_legality, _cache_card; to fix the statistics.

If it doesn't, and since we established in the comments that (card_id,format_id) is unique, I would try to add the following index

CREATE UNIQUE INDEX uidx_card_legality ON card_legality(format_id, card_id)

and use

SELECT c.* 
FROM _cache_card AS c
JOIN card_legality l FORCE INDEX (uidx_card_legality)
ON l.card_id = c.id AND l.format_id = 35;

This is basically the way that MySQL currently executes your query (while creating this index on the fly), although you seem to have tried that index with Lukasz' answer and it didn't work.

You should remove force index (it is just to make absolutely sure MariaDB has no wiggle room to do something else) and check if MariaDB/MySQL still uses it. Also test what happens for other values of format_id, as 35 might be an outlier (e.g. you may just have a handful of entries for that format), and optimizing the execution plan for this might slow down the query for all other values. And of course make sure that you are comparing similar resultsets, as if MariaDB has e.g. 10k entries for format 35, while MySQL has none, that would not be a fair fight.

Answer 3

IN can be tricky from an optimization perspective. I wonder if this works better on the two systems:

SELECT c.* 
FROM _cache_card  c 
WHERE EXISTS (SELECT 1
              FROM card_legality cl
              WHERE cl.card_id = c.id AND format_id = 35
             );

Answer 4

I would add index:

CREATE INDEX idx_name ON card_legality(format_id, card_id);

DBFiddle Demo

---

SELECT * 
FROM _cache_card AS c 
WHERE c.id IN (SELECT card_id FROM card_legality WHERE format_id = 35);
               -- covering index created before