Can't get SELECT WHERE IN (huge list of items) to use index, does table scan

MySQL & MariaDB Percona Server for MySQL 8.0
1

So I recently reimported my ROCKSDB database from a backup, with a fresh bulk_load and all inserted in primary key order. Took some time, but now it’s fresh and shiny ;).

However, without having changed anything in the application that calls the database, unfortunately a query that is used a lot in background processing workers, now takes 50s instead of 1s.

I debugged it and it turns out that the following query always uses a table scan. Even though I force the proper index, explain shows me that no index is used.

SELECT name, id, FROM foo_names FORCE INDEX (name) where name in (10.000 items);

id	select_type	table	    partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	    foo_names	NULL        ALL     name	        NULL	NULL	NULL	301584577	50.00	Using where

I guess somehow it decides that > 10.000 items or so, the table scan is faster. But it’s certainly not.
I find it weird that even though I force a index, it still doesn’t use it.

I did also set the max_seeks_for_key=1000 to debug, but this does not change anything.

How can I force force to use the index?

CREATE TABLE `foo_names` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(768) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=ROCKSDB AUTO_INCREMENT=303679574 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=FIXED |

Server version: 8.0.34-26 Percona Server (GPL), Release 26, Revision 0fe62c85
2

FORMAT=TREE explains it as follows:

(cost=332e+6 rows=151e+6)\ -> Table scan on foo_names (cost=332e+6 rows=302e+6)

3

So, I did a bit more research and experimenting, and the whole issue is solved when I convert the data to utf8_bin instead of utf8mb4_bin. Then it immediately uses the index.

I am lucky, since from the 300 million rows I only have 400 rows with actual utf8mb4 content. And I can drop those. But still, this used to work and for future proofing I would very much like to support utf8mb4.

Hmm: The first few queries run ok, but after that it’s going back to table scan again… :frowning:

4

I copied the table and modified it to use Compressed InnoDB, but also there it does not use the index. So it seems more a MySQL issue then RocksDB.

update
When using a temporary table with only the where in() strings inserted into it, and then doing a join on the actual table, it uses the index properly. Also on RocksDB. So that’s now my workaround. But it’s ugly.