[#MDEV-22054] Very bad performance MariaDB vs MySQL

Hello,
after upgrading from MySQL 5.7.12 to MariaDB 10.4.12 we see a nasty drop in performances which render our software unusable. I experimented with MySQL 5.7.29 and the result is the same.
I tracked down the issue to a x6 drop in speed for some cached queries in MariaDB.
Here are the initial queries, with no caching involved :

SELECT COUNT(*) FROM `products_views` INNER JOIN `users` ON `users`.`id` = `products_views`.`user_id` WHERE (products_views.created_at between '2020-03-09 00:00:00.000000' and '2020-03-20');

+----------+

| COUNT(*) |

+----------+

|   185202 |

+----------+

1 row in set (11.26 sec)

while with MySQL 5.7.29 I get :

SELECT COUNT(*) FROM `products_views` INNER JOIN `users` ON `users`.`id` = `products_views`.`user_id` WHERE (products_views.created_at between '2020-03-09 00:00:00.000000' and '2020-03-20');

+----------+

| COUNT(*) |

+----------+

|   165805 |

+----------+

1 row in set (7.96 sec)

Afterward the cache is primed and things gets ugly for MariaBD when I repeat the same query :

MariaDB (8.92 sec with some variations)
MySQL the time is constant, always 2.05 sec.

The EXPLAIN shows both software have a very different way of running this very same query :

MariaDB

+------+-------------+----------------+-------+---------------------------------+---------------------------------+---------+--------------------------------+--------+-------------+

| id   | select_type | table          | type  | possible_keys                   | key                             | key_len | ref                            | rows   | Extra       |

+------+-------------+----------------+-------+---------------------------------+---------------------------------+---------+--------------------------------+--------+-------------+

|    1 | SIMPLE      | users          | index | PRIMARY                         | index_users_on_followers_count  | 4       | NULL                           | 226050 | Using index |

|    1 | SIMPLE      | products_views | ref   | index_products_views_on_user_id | index_products_views_on_user_id | 5       | pf_analytics_int_prod.users.id | 15     | Using where |

+------+-------------+----------------+-------+---------------------------------+---------------------------------+---------+--------------------------------+--------+-------------+

MySQL

+----+-------------+----------------+------------+--------+---------------------------------+---------+---------+-----------------------------------+---------+----------+-------------+

| id | select_type | table          | partitions | type   | possible_keys                   | key     | key_len | ref                               | rows    | filtered | Extra       |

+----+-------------+----------------+------------+--------+---------------------------------+---------+---------+-----------------------------------+---------+----------+-------------+

|  1 | SIMPLE      | products_views | NULL       | ALL    | index_products_views_on_user_id | NULL    | NULL    | NULL                              | 6159146 |    11.11 | Using where |

|  1 | SIMPLE      | users          | NULL       | eq_ref | PRIMARY                         | PRIMARY | 4       | pf_app_pub.products_views.user_id |       1 |   100.00 | Using index |

+----+-------------+----------------+------------+--------+---------------------------------+---------+---------+-----------------------------------+---------+----------+-------------+

I experimented a bit with some optimizations but to not avail. MariaDB optimizer is not up to par with MySQL but it's mostly the cache that is way better in MySQL.
Profiling shows all the time is spent in "Sending data" phase, both for MariaDB and MySQL.

[MDEV-22054] Very bad performance MariaDB vs MySQL Created: 2020-03-26 Updated: 2023-09-19
Status:	Open
Project:	MariaDB Server
Component/s:	Optimizer, Query Cache
Affects Version/s:	10.4.12
Fix Version/s:	10.4, 10.5

[MDEV-22054] Very bad performance MariaDB vs MySQL Created: 2020-03-26 Updated: 2023-09-19