MySQL 5.7 to MariaDB 11 migration index problems

Question

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 :-(

• danblack: Have you done an ANALYZE TABLE? If that didn't help, this sounds useful enough to make a [bug report](jira.mariadb.org/). Can you include an optimizer trace and SHOW CREATE TABLE even if limited to the fields used in the query.
• danblack: which exact MariaDB 11 version are you using?
• Dimas: Yes, I've done ANALYZE TABLE, it's where I can see the possible_key and the selected key used by the engine. I'm using MariaDB 11.0.2
• Akina: Asking about the query optimizing you'd provide complete tables definitions and query execution plan at least. Also you'd provide some data statistic.
• Akina: .. WHERE Users.Category IN (many_ids_separated_by_commas) .. Save your ids list to temptable, index it and use in query data source.

Answer 1

This is as naughty as FORCE INDEX, but it may help:

SELECT bt.col
FROM (
 SELECT UserId
 FROM Users
 WHERE Users.Category IN (many_ids_separated_by_commas)
 ) jt
 ON bt.UserId = jt.UserId
STRAIGHT_JOIN BigTable bt
WHERE
 ...