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

**URL:** <https://forums.percona.com/t/cant-get-select-where-in-huge-list-of-items-to-use-index-does-table-scan/27418>\
**Category:** Percona Server for MySQL 8.0\
**Created:** [December 26, 2023, 3:27pm UTC](https://forums.percona.com/t/cant-get-select-where-in-huge-list-of-items-to-use-index-does-table-scan/27418 "2023-12-26T15:27:56Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![peterdk](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@peterdk](https://forums.percona.com/u/peterdk)\
**Post date:** [December 26, 2023, 3:27pm UTC](https://forums.percona.com/t/cant-get-select-where-in-huge-list-of-items-to-use-index-does-table-scan/27418/1 "2023-12-26T15:27:56Z")

</div>

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.

```auto
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?

```auto
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

```

---

<div class="post-metadata">

**Author:** ![peterdk](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@peterdk](https://forums.percona.com/u/peterdk)\
**Post date:** [December 26, 2023, 3:43pm UTC](https://forums.percona.com/t/cant-get-select-where-in-huge-list-of-items-to-use-index-does-table-scan/27418/2 "2023-12-26T15:43:20Z")

</div>

FORMAT=TREE explains it as follows:

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

---

<div class="post-metadata">

**Author:** ![peterdk](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@peterdk](https://forums.percona.com/u/peterdk)\
**Post date:** [December 26, 2023, 7:06pm UTC](https://forums.percona.com/t/cant-get-select-where-in-huge-list-of-items-to-use-index-does-table-scan/27418/3 "2023-12-26T19:06:56Z")

</div>

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… ☹

---

<div class="post-metadata">

**Author:** ![peterdk](https://avatars.discourse-cdn.com/v4/letter/p/898d66/32.png) [@peterdk](https://forums.percona.com/u/peterdk)\
**Post date:** [December 29, 2023, 1:32pm UTC](https://forums.percona.com/t/cant-get-select-where-in-huge-list-of-items-to-use-index-does-table-scan/27418/4 "2023-12-29T13:32:31Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![jinyou.ma](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jinyou.ma/32/19714_2.png) [@jinyou.ma](https://forums.percona.com/u/jinyou.ma)\
**Post date:** [November 12, 2025, 3:41am UTC](https://forums.percona.com/t/cant-get-select-where-in-huge-list-of-items-to-use-index-does-table-scan/27418/5 "2025-11-12T03:41:42Z")

</div>

@peterdk can you try increasing the variable [`range_optimizer_max_mem_size`](https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_range_optimizer_max_mem_size)

> **[MySQL :: MySQL 8.4 Reference Manual :: 10.2.1.2 Range Optimization](https://dev.mysql.com/doc/refman/8.4/en/range-optimization.html#range-optimization-memory-use)**