Find_in_set的替代方案，它可以使用索引

Question

我有一个sql存储过程，其中我正在传递一个数字列表作为逗号分隔的mediumtext字段

为了检查我的参数是否匹配，我在我的存储过程中使用Find_in_set，如下所示

SELECT SQL_CALC_FOUND_ROWS f1,f2,f3,f4 FROM `mytable`
WHERE FIND_IN_SET(f2,'1,2,4,5,6,6,7,8,8,4,9,7.......................') > 0
ORDER BY f1 DESC        
LIMIT 25 OFFSET 0
;
SELECT FOUND_ROWS();

现在的问题与Find_in_set我后来发现的是，它不使用索引，由于我的查询需要太长时间才能完成。请对查询提出任何改进建议

公立小学

以下是我完整的存储过程(包括以下答案中建议的更改)

    DECLARE _calculated_offset INT;
    SET _calculated_offset = _limit * (_pageNumber -1);
    IF _calculated_offset < 0 THEN  
        SET _calculated_offset = 0;
    END IF;         
    /*SELECT SQL_CALC_FOUND_ROWS f1,f2,f3,f4  FROM `mytable`
    WHERE FIND_IN_SET(f1,_telcoIdList) > 0  
    AND FIND_IN_SET(f2,_msisdnList) > 0 
    ORDER BY f3 DESC        
    LIMIT _limit OFFSET _calculated_offset
    ;
    SELECT FOUND_ROWS();*/

SET @query = CONCAT('SELECT SQL_CALC_FOUND_ROWS f1,f2,f3,f4
    FROM `mytable`
    WHERE f1 in (',_telcoIdList,')
    AND f2 in (',_msisdnList,') 
    ORDER BY f3 DESC        
    LIMIT ', _limit,' OFFSET ',_calculated_offset,' ;
    SELECT FOUND_ROWS();
    '); 
    PREPARE stmt FROM @query; 
    EXECUTE stmt; 
    DEALLOCATE PREPARE stmt; 

Answer 1

使用预处理语句，修改为in子句

SET @query = CONCAT('SELECT SQL_CALC_FOUND_ROWS f1,f2,f3,f4 FROM mytable
WHERE f2 in (', myinputstr, ') ORDER BY f1 DESC LIMIT 25 OFFSET 0'); 
    PREPARE stmt FROM @query; 
    EXECUTE stmt; 
    DEALLOCATE PREPARE stmt; 

允许使用f2索引。

编辑:要包含foundrows语句，请使用以下样式

SET @query = CONCAT('SELECT SQL_CALC_FOUND_ROWS f1,f2,f3,f4
    FROM `mytable`
    WHERE f1 in (',_telcoIdList,')
    AND f2 in (',_msisdnList,') 
    ORDER BY f3 DESC        
    LIMIT ', _limit,' OFFSET ',_calculated_offset); 
    PREPARE stmt FROM @query; 
    EXECUTE stmt; 
    DEALLOCATE PREPARE stmt; 
    SELECT FOUND_ROWS();

BTW，在查询