MySQL Performance FIND_IN_SET

MySQL Database is being used for various analytical queries. Some of the frequent query we have is get table data from list of values. It is prominent to use FIND_IN_SET function, as it does not require any query change.

Before MySQL 5.7 , It does not support function indexes. Hence whenever we query with function on column it will go for full table scan.

Below is dummy table example for showing the same thing. however, we need to add huge data set to prove same. Volumatric part will be done later. Let’s check sample query.

Sample Data generation

# Setup table & data
create table tbl(i int, b varchar(23));
insert into tbl values(1,'2'),(2,'sdf');
insert into tbl values(3,'new'),(4,'zxf');
alter table tbl add index idx_i(i);
alter table tbl add index idx_b(b);

Original procedure with FIND_IN_SET

DROP PROCEDURE IF EXISTS proc_find_in_set;
delimiter //
CREATE PROCEDURE proc_find_in_set(item varchar(245))
BEGIN
         select * from tbl where FIND_IN_SET(i,item);
END//
delimiter ;
call proc_find_in_set('1');
call proc_find_in_set('1,3');

Although above does good job for integer & string both, It is not using index & hence degrade the database performance overall. Let’s convert it in IN query, so that it uses index.

Convert FIND_IN_SET to IN query for int

DROP PROCEDURE IF EXISTS proc_in_int;
delimiter //
CREATE PROCEDURE proc_in_int(item varchar(245))
BEGIN
         SET @query1:=concat("select * from tbl where i in (",item,')');
         PREPARE stmt FROM @query1;
         EXECUTE stmt ;
         DEALLOCATE PREPARE stmt;
END//
delimiter ;
call proc_in_int('1');
call proc_in_int('1,3');

The quey works without any major change. We added only prepared statement for dynamic value resolution. However, for varchar same query may not work. We have to use string operation to convert value stream with quoted values.

Convert FIND_IN_SET to IN query for string

DROP PROCEDURE IF EXISTS proc_in_str;
delimiter //
CREATE PROCEDURE proc_in_str(item varchar(245))
BEGIN
       SET @Itemfilter := CONCAT("'",replace(item,",","','"),"'");
       SET @query1:=concat("select * from tbl where b in (",@Itemfilter,')');
         PREPARE stmt FROM @query1;
         EXECUTE stmt ;
         DEALLOCATE PREPARE stmt;
END//
delimiter ;
call proc_in_str('sdf,2');
call proc_in_str('sdf');

That’s it! Your application now 10x faster as it will use indexes.