I'm having a problem with one big SQL. We're migrating a MySQL 5.7 database to a MariaDB 11, and the majority of queries are running faster, but i've problems with this one, and I don't know how to solve it.
I can't show the very big query to you, but I've found the part of the query is running bad, maybe with this you have the enough information to help me with that.
I have a table (example name BigTable) with some indexes, the important one is the ix_user with the BigTable.UserId column. In MySQL 5.7 the engine is using it, but not with MariaDB. MariaDB uses another index that is not useful, and the query lasts 15 secs in comparison with the 9 secs in MySQL. First, this is the query:
SELECT bt.col FROM BigTable bt INNER JOIN ( SELECT UserId FROM Users WHERE Users.Category IN (many_ids_separated_by_commas) ) jt ON bt.UserId = jt.UserId WHERE ...
With EXPLAIN, MariaDB tell me that the ix_user is a possible key, but he doesn't like it. If I use
FORCE INDEX (ix_user), the EXPLAIN says that the index is not possible (it not appears in possible_key neither key).
I've tried to change the
INNER JOIN with a
WHERE bt.UserId IN (...) with the same (bad) result.
I tried to reduce the many_ids_separated_by_commas to 20, and the same happens. But wait! If I reduce it to 10, now the MariaDB engine likes the ix_user key and the query is fast!!
I don't understand the MariaDB engine in this case and how to tell it that uses that index :-(