Bug #37564 FIND_IN_SET is not using indexes
Submitted: 21 Jun 2008 11:41 Modified: 22 Jun 2008 8:22
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
Triage: Triaged: D5 (Feature request)

[21 Jun 2008 11:41] Frederic Steinfels
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)
[21 Jun 2008 18:47] Valeriy Kravchuk
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.
[22 Jun 2008 8:22] Frederic Steinfels
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.
[21 Feb 2016 16:58] kapil patel
Are there any plans to let mysql use indexes for FIND_IN_SET?
[11 Mar 2016 14:21] Ricardo Cescon
We also like to know when this feature will come
[5 May 2018 0:03] Greg Woolsey
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.