CODEWITHSUNDEEP
mysqlquery-optimization
Kamran Shahid
Kamran Shahid

Reputation: 4124

Alternative for Find_in_set which can use index

I have a my sql stored procedure where i am passing a list of numbers as a comma separated mediumtext field

For checking if my parameter matched i am using Find_in_set in my stored procedure as follows

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();

Now problems with Find_in_set i found out later is that it doesn't used index due to which My query is taking too long to complete. Please suggest any improvement in the query

P.S.

Following is my complete stored procedure (including the changes as suggested in below answer)

    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;

Upvotes: 3

Views: 3862

Answers (1)

Akhil
Akhil

Reputation: 2602

Use prepared statement and change to in clause

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;

This will allow the f2 index to be used.

EDIT : To include foundrows statement, use the following style 

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, added SQL_CALC_FOUND_ROWS too in the query

Upvotes: 1

Related Questions