Use of FInd_IN_SET vs IN clause MYSQL stored procedure
mysql, sql-optimization, sqlperformance, stored-procedures
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.
Problem
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?