We are experiencing what appears to be a regression in the query planner in the process of testing an upgrade from MariaDB 10.2.39 to 10.4.20.
The table definition we are running the query against is as follows:
The query that I am running is as follows:
The query returns 49 rows in 0.00 sec on MariaDB 10.2, but consistently takes over 8-9 seconds returning the same 49 rows on MariaDB 10.4. We have been running similar queries for many years on MySQL 5.5 and MariaDB 10.2 against even much longer sets of unsigned BIGINT values, and the results have always been nearly instant.
Here is the EXPLAIN EXTENDED output on the query on MariaDB 10.2:
Here is the EXPLAIN EXTENDED output on the query on MariaDB 10.4:
As you see in the schema above, the value for 'token' is an unsigned BIGINT.
In the query we are running, 24 of the values are below the max size of a signed BIGINT (9223372036854775807), 25 of the values are above that size.
Here is where it gets interesting...
When we run the same query on MariaDB 10.4 but only include the 24 values below the signed BIGINT max size, the results return in 0.002 seconds, with the following EXPLAIN EXTENDED output:
Running the same query again, but this time against the 25 values that are above the signed BIGINT max size, the results return in 0.001 seconds, with the following EXPLAIN EXTENDED output:
Finally, if when running the same query against 24 values that are both above and below the unsigned BIGINT limit, the query takes 3.305 seconds and the EXPLAIN EXTENDED reports:
We have tried similar tests against table structures that use a simple primary key on 'token' instead of the composite key above, but the performance characteristics are similar: fast when values do not straddle the max signed BIGINT limit, very slow when they do.
The only workaround we have found is by using OR rather than IN, but that is not a solution which will work for us in all use cases.