MySQL Bugs: #37564: FIND_IN_SET is not using indexes

Bug #37564	FIND_IN_SET is not using indexes
Submitted:	21 Jun 2008 11:41	Modified:	6 Aug 13:12
Reporter:	Frederic Steinfels	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	all	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	FIND_IN_SET, INDEX

Description:
FIND_IN_SET is not using indexes although it seems to me rather trivial. Most FIND_IN_SET's can be replaced by a chain of OR's and those OR's are using indexes.

of course I could avoid using FIND_IN_SET in favour of OR's but FIND_IN_SET is so much nicer.

How to repeat:
mysql> select count(code) from orderline where FIND_IN_SET(backorder,'5,6');
+-------------+
| count(code) |
+-------------+
|       14178 |
+-------------+
1 row in set (0.40 sec)

mysql> select count(code) from orderline where backorder=5 or backorder=6;
+-------------+
| count(code) |
+-------------+
|       14178 |
+-------------+
1 row in set (0.20 sec)

mysql> explain select count(code) from orderline where FIND_IN_SET(backorder,'5,6');
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | orderline | ALL  | NULL          | NULL | NULL    | NULL | 56582 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.01 sec)

mysql> explain select count(code) from orderline where backorder=5 or backorder=6;
+----+-------------+-----------+-------+---------------+-----------+---------+------+-------+-------------+
| id | select_type | table     | type  | possible_keys | key       | key_len | ref  | rows  | Extra       |
+----+-------------+-----------+-------+---------------+-----------+---------+------+-------+-------------+
|  1 | SIMPLE      | orderline | range | backorder     | backorder | 4       | NULL | 13871 | Using where |
+----+-------------+-----------+-------+---------------+-----------+---------+------+-------+-------------+
1 row in set (0.00 sec)

Thank you for a reasonable feature request. It is not a bug though. The only possible optimization for this function is clearly described in the manual, http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_find-in-set.

For all other cases, if you use WHERE f(x) in your query index on x column can not be used.

Not using indexes for FIND_IN_SET can be considered both a feature request or an error. I think noone will assume that indexes are not used with FIND_IN_SET unless having made some benchmarks. Anyway it seems rather cheap to fix to me so please do it for 5.1.

Are there any plans to let mysql use indexes for FIND_IN_SET?

We also like to know when this feature will come

FIND_IN_SET() is just a way to use a string as the list value for a logical IN() condition.

Since IN can use indexes, it seems logical that FIND_IN_SET, FIELD, and other similar functions should be able to utilize similar optimizations, even if it is in the context of a two-step process: first convert the expression to an IN() and evaluate that, then determine the list position if there is a match.  It can still shortcut, as only the first match is needed.  The only difference with IN() is that these functions need to know which the matching value so the index can be calculated.

If this isn't done, then have a way to explode a comma string list in to a set of values.

Currently the only way to do this is:

set ids = "[1,2,3,4,5,6]";

....
where `ID` in (select `id` from JSON_TABLE(`ids`, "$[*]" COLUMNS(`id` BIGINT PATH "$"))`jt`)

would be nice to have:

set ids = "1,2,3,4,5,6";
....
where `ID` in (explode(",", ids))

Thanks. Fixed after 16 years it seems in mysql 9.0.0. I have not tested it yet though but you might consider closing my bug.
It was not possible to add a functional index on FIND_IN_SET(). (Bug #35352161)