A client application receives record ids in a particular, and meaningful order. We need to fetch blobs out of MySQL in that same order using an IN clause. Problem is, order is not guaranteed, unless ORDER BY  is present, and our MySQL has no idea how the original order was concocted.

Previously, the code selected out the target data, and re-ordered the resultset in memory. This is very, very costly for large numbers of results, which all have to be returned at once, whereas I would like to “stream” the result set (in order thanks), using lazy enumerators (and here).  

What to do?

The non-obvious solution, after much googling, is that we can use the MySQL function find_in_set. It looks like this:

select x from y  where y.id in (a bunch of ids here) order by find_in_set(id, ‘all my ids.join(,)’)

What we’re doing with this, is ordering by a function, the input of which is the column name, and all the ids, concatenated, and comma delimited.  The function will find the position of each id in the string, and return an integer, which is used by the ORDER BY  clause for each row.

The net result, is that you wind up with an explicit ordering of results, without having to do anything in application memory, and the ability to stream the result set.  We do this with 1000 ids at a time (a MySQL limit), and it’s plenty fast for our needs.