Need help in optimising the MySql stored procedure I have a stored procedure which is used to delete table entries. I have the input to the procedure as an array so that I can pass them as is for the delete queries [For ex: Use the IN clause to submit the array]. Even though the array items are integers, I had read that I need to pass them as string arrays. What is happening with this is, I'm not able to pass the array entries entirely to the Delete query and need to loop through them and submit individual entry to the Delete query. Could I get any suggestions/help to achieve this? Below mentioned is the procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `Delete_Proc`(IN entity_id_array varchar(100), IN mgmt_module varchar(10))
BEGIN

    -- Declare variables to hold diagnostics area information
    DECLARE errCode CHAR(5) DEFAULT '00000';
    DECLARE msgTxt TEXT;
    DECLARE rowCount INT;
    DECLARE resVal TEXT;


    DECLARE _sRemainder TEXT; 
    DECLARE _Delimiter CHAR(1); 
    DECLARE _nPos INT DEFAULT 1;
    DECLARE _sStr VARCHAR(100); 
    DECLARE _entitId INT;


    DECLARE EXIT HANDLER FOR SQLEXCEPTION

    GET DIAGNOSTICS CONDITION 1
        errCode = RETURNED_SQLSTATE, msgTxt = MESSAGE_TEXT;


    SET _sRemainder = entity_id_array; 

        WHILE CHAR_LENGTH(_sRemainder) &gt; 0 AND _nPos &gt; 0 DO 

            SET _nPos = INSTR(_sRemainder, ',');

            IF _nPos = 0 THEN 
                SET _sStr = _sRemainder; 
            ELSE 
                SET _sStr = LEFT(_sRemainder, _nPos - 1); 
            END IF;

            IF TRIM(_sStr) != '' THEN

                Set _entitId = CAST(_sStr AS UNSIGNED);

                        DELETE user_management, user_endpoint_association, user_group_association 
                                FROM user_management
                                    INNER JOIN
                                        user_endpoint_association ON user_endpoint_association.user_id = user_management.user_id
                                    INNER JOIN
                                        user_group_association ON user_group_association.user_id = user_management.user_id
                            WHERE
                                user_management.user_id = _entitId;

            END IF; 

            SET _sRemainder = SUBSTRING(_sRemainder, _nPos + 1); 
        END WHILE;



  -- Check whether the delete was successful
  IF errCode = '00000' THEN
    GET DIAGNOSTICS rowCount = ROW_COUNT;
    SET resVal = CONCAT('Delete succeeded, row count = ', rowCount);
  ELSE
    SET resVal = CONCAT('Delete failed, error = ', errCode,', message = ', msgTxt);
  END IF;
  -- Say what happened
  SELECT resVal;

END


=======================================================================

Here is the output of Explain Delete:



=========================================================================

Here is the output after adding the indexes:
So, your problem is, that you want to give a string like 1,3,5,7 as parameter to the procedure to actually do a query like delete from foo where id in (proc_parameter), right? 

The problem with this is, that the query becomes this: delete from foo where id in ('1,3,5,7'). Then an implicit conversion from string to integer happens and the only entry that gets deleted is the one with id 1. 

The most convenient solution would be, that the query with the replaced parameter wouldn't have those single-quotes. You can do this with prepared statements. The procedure would then look something like this:

DELIMITER $$
CREATE PROCEDURE sp_del(IN my_array VARCHAR(100))
BEGIN 
    SET @sql := CONCAT('DELETE FROM foo WHERE id IN (', my_array, ');');
    SELECT @sql AS the_fancy_query_I_will_execute; -- debugging, not needed ;)
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;


and called with 

CALL sp_del('1,3,5,7');


But that all being said, why don't you just solve this on application level instead? That's what I would do!