Need help in optimising the MySql stored procedure
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!