Use of FInd_IN_SET vs IN clause MYSQL stored procedure

Question

I have a stored procedure that is similar to below

SELECT * 
FROM Table1
WHERE Tag IN (ids)

here Tag is an Integer column.

I tired to pass in comma separated values as string into the stored procedure but it does not work. Then I used stored procedure like below

SELECT * 
FROM Table1
WHERE FIND_IN_SET(Tag, ids)

This works very well, the only problem is my table is very big - millions of rows and using FIND_IN_SET takes too long compared to IN when running a direct SQL statement.

What would be the best performance optimized option to use?

Is there a split function that can convert the ids into integer and parse it ready for IN clause? I think that would be the best option. Any suggestions or ideas?

http://stackoverflow.com/questions/6152137/mysql-string-split — SQL.injection, Jul 14 '13 at 17:17

score 4 · Accepted Answer · answered Jul 14 '13 at 17:18

4

You can prepare a statement and then execute it:

set @sql = concat('select * from table1 where tag in (', ids, ')');

PREPARE q FROM @sql;

execute q;

This constructs the string for each execution, so you can use in. The resulting execute should be able to use an index on tag, which should speed things up considerably.

answered Jul 14 '13 at 17:18

Gordon Linoff

1,242,037
58
646
786

that actually works in one test Find_in_Set :3.978sec 94rows return IN :1.732sec 94rows return concat(yourexample): 1.747 sec 94 rows – Justin Homes Jul 14 '13 at 17:31

Use of FInd_IN_SET vs IN clause MYSQL stored procedure

1 Answers1