Using the attached mysqldump, the following query is very slow in MariaDB compared to MySQL 5.5 or MySQL 5.6:
The schema and query are part of Magento, but the attached dump has had FK, some unique indexes, and most of the columns dropped.
The query plan in MySQL, which executes quickly:
5.6 has BNL and using Join buffer for the second table, and runs at a similar speed.
In MariaDB 5.5 and MariaDB 10, the following slow plan is used:
This can be made fast in MariaDB with ADD INDEX ix_foo (category_id, store_id) or IGNORE INDEX.
There also seems to be no optimizer_switch to turn off this strategy.