Search code examples
mysqlstored-proceduressql-optimizationsqlperformance

Use of FInd_IN_SET vs IN clause MYSQL stored procedure

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?

Solution

  • 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.