How to select values matching a variable number of parameters in a MySQL stored procedure

Question

I have a requirement to write a MySQL stored procedure (called from .NET) that searches a table of stoppoints and allows me to specify a number of possible stopMode values to match against.

In other words:

CREATE PROCEDURE getActiveStoppoints(
IN NamePrefix VARCHAR(100),
IN StopModeMatch1 TINYINT(4),
IN StopModeMatch2 TINYINT(4),
IN StopModeMatch3 TINYINT(4),
IN StopModeMatch4 TINYINT(4),
IN StopModeMatch5 TINYINT(4)
)
BEGIN

-- Return all records matching
SELECT sp.* FROM stoppoints sp
WHERE (sp.name LIKE CONCAT(NamePrefix, '%')
AND
(
(sp.stopMode = StopModeMatch1) OR 
(sp.stopMode = StopModeMatch2) OR 
(sp.stopMode = StopModeMatch3) OR 
(sp.stopMode = StopModeMatch4) OR 
(sp.stopMode = StopModeMatch5) 
)
;

END

This approach seems horribly brittle - for example, what if I needed to pass in 6 possible stopMode values, or even 600? And what happens when I have two other columns I'd like to match against in a similar way?

What other possible ways are there to achieve this? Can I pass an array into the stored procedure, for example?

I initially tried this by passing in a comma-separated list of values in a VARCHAR. I ended up utterly frustrated with this approach because:

• Using FIND_IN_SET to match against comma-separated strings doesn't use any indexing, so performance is terrible and it's therefore not a valid solution.
• Creating a prepared SQL statement with PREPARE, EXECUTE, CONCAT, etc. felt brittle and not very performant either. Firstly, I'd need to deal with putting quotes around the values, if matching against strings. And also I assume that the query plan would have to be re-created each time the stored proc was run?
• Trying to split the CSV values into a temporary table, then using a subselect does work but feels very hacky. Plus when you try to separate this out into a stored procedure, you cannot return a table/rows from a stored procedure; instead you must remember the temporary table name and call the stored proc first. Which doesn't scale beyond use for just in one column.

Please believe me when I say I have spent several hours researching this problem to no avail. How does one achieve this in MySQL, or is it simply not designed for this kind of stored procedure?

Answer 1

I tried some approaches:

1. Using regular OR sentences (same as your stored procedure) I get some performance but write 600 comparisons would be a nightmare. (0.023s - 137K random records)

2. Using temporary table.

   CREATE PROCEDURE getActiveStoppoints(
       IN NamePrefix VARCHAR(100),
       IN StopModeMatch1 TINYINT(4),
       IN StopModeMatch2 TINYINT(4),
       IN StopModeMatch3 TINYINT(4),
       IN StopModeMatch4 TINYINT(4),
       IN StopModeMatch5 TINYINT(4)
   )
   BEGIN
   
       -- drop table tempValues;
       create temporary table if not exists tempValues(
           stoppoint int not null primary key
       ) engine=memory;
       truncate tempValues;
       insert ignore into  tempValues(stoppoint) values (StopModeMatch1);
       insert ignore into  tempValues(stoppoint) values (StopModeMatch2);
       insert ignore into  tempValues(stoppoint) values (StopModeMatch3);
       insert ignore into  tempValues(stoppoint) values (StopModeMatch4);
       insert ignore into  tempValues(stoppoint) values (StopModeMatch5);
   
       -- Return all records matching
       SELECT count(*) -- sp.* 
       FROM stoppoints sp
       WHERE sp.name LIKE CONCAT(NamePrefix, '%')
       and sp.stopMode in (select stoppoint from tempValues);
   
   END$$
   

That will work for 600+ values, but the procedure parameters are limited. Maybe using a regular table and insert each (600+) values outside of procedure and run. (0.24s - 137K random records)

3. Using prepared statement:

   CREATE PROCEDURE getActiveStoppoints(
       IN NamePrefix VARCHAR(100),
       in stopDelimited varchar(255)
   )
   BEGIN
       set @sql = concat("SELECT count(*) FROM stoppoints sp WHERE sp.name LIKE '",NamePrefix,"%'");
       set @sql = concat(@sql," and sp.stopMode in (", stopDelimited ,")" );
   
       PREPARE stmt1 FROM @sql;
       EXECUTE stmt1;
   END$$
   delimiter ;
   

I think this is the best solution because doesn't use a temporary table and the execution time is the same as the first approach, and the second parameter can be configured to receive 600+ csv.

Answer 2

If you have 600 stopMode values, you will be better off by passing the values using a temporary table. Create and populate a temporary table with possible stopMode values before calling the procedure and use the table in the procedure.

Answer 3

I usually think that when something becomes really complicated, messy and hard to deal with, it's a sign that the wrong tool is being used.

What impedes you from doing a simple query for each of the stopModes you're interested in?

In pseudo code, I mean:

rows = array()
forEach(selectedStopMode as $stopMode){
 sql = SELECT [...] WHERE stopMode = $stopMode;
 rows.append(sql.result)
}

There are various reasonings here: 1-MySQL is more likely to solve many small queries much faster than a single huge query.

2-The application would be easier to maintain.

3-The application would scale much better

4-MySQL would be more likely to be able to cache the result in the Query Cache, and more likely to not need to build temporary tables, which results in better performance.

Unless you tell me that it absolutely needs to be done in MySQL, I'd go with a solution like this.