Magento2 is using Entity-Attribute-Value model (EAV), also known as object-attribute-value.
with 315 tables. Here is the diagram:
As a result, there are multiple "IN " statements in queries. Here is one of the problematic queries:
SELECT `main_table`.* FROM `url_rewrite` AS `main_table` WHERE (`redirect_type` = '0') AND (`entity_type` = 'product') AND (`entity_id` IN('448', '503', '532', '547 ........... <------ if the number of products here exceeds 1000, then "optimizer" creates subqueries.
In MariaDB 10.1 and 10.2 and also MySQL 8 it's a SIMPLE execution plan that takes 6 seconds:
95210 rows in set (5.58 sec)
On MariaDB 10.3 and 10.4 on the same server with the same settings (including optimizer_switch) it's now 35 mins:
However, there is no way to disable subqueries in
MDEV-12176 (@@in_predicate_conversion_threshold doesn't work on production releases)
The database is about 7GB and contains sensitive data. That's why I didn't attach it to this issue. It is available on request.