CODEWITHSUNDEEP
mysql
masterofweb
masterofweb

Reputation: 3

mysql procedure parameter and FIND_IN_SET performance

I have a table with over 1 MIO rows and I like to create a stored procedure instead of calling a direct sql statement, but I have performance issues with the FIND_IN_SET function.

Start from the scratch: My existing query is: 

SELECT id 
FROM `tablex`
WHERE id=1 OR id=2 OR id=3;

The WHERE clause is built by PHP form a previous result. Its much more complex as here in this example .-).

This query works fine for me and I´m happy with the performance.

Now want to create a stored procedure like this:

CREATE DEFINER=`root`@`localhost` PROCEDURE `FOO`(**IN `p_ids` VARCHAR(255)**)

The p_ids are comma separated and this is running but very slow, because FIND_IN_SET is not using the index on the ids.

SELECT id
FROM `tablex`
WHERE FIND_IN_SET(id,p_ids)

I like to find a way to get the complete WHERE clause from my first query into the stored procedure, or to find a faster solution as the Find_in_set thing.

Upvotes: 0

Views: 927

Answers (2)

masterofweb
masterofweb

Reputation: 3

no code in comments :-) Here are the syntax corrected version.

SQL CALL:

CALL `db`.`FOO`("2,3,4");

Procedure CODE:

CREATE DEFINER=`root`@`localhost` PROCEDURE `FOO`(IN `p_ids` VARCHAR(255))
BEGIN
    SET @sql = CONCAT('SELECT id FROM tablex WHERE id in (', p_ids, ')');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

Upvotes: 0

Barmar
Barmar

Reputation: 782166

Use PREPARE to create dynamic SQL.

SET @sql = CONCAT('SELECT id FROM tablex WHERE id in (', p_ids, ')');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Upvotes: 1

Related Questions