Sort by order of values in a select statement "in" clause in mysql

Question

I'm selecting a set of account records from a large table (millions of rows) with integer id values. As basic of a query as one gets, in a sense. What I'm doing us building a large comma separated list, and passing that into the query as an "in" clause. Right now the result is completely unordered. What I'd like to do is get the results back in the order of the values in the "in" clause.

I assume instead I'll have to build a temporary table and do a join instead, which I'd like to avoid, but may not be able to.

Thoughts? The size of the query right now is capped at about 60k each, as we're trying to limit the output size, but it could be arbitrarily large, which might rule out an "in" query anyway from a practical standpoint, if not a physical one.

Thanks in advance.

Answer 1

If your query is 60K, that's a sign that you're doing it the wrong way.

There is no other way to order the result set than by using an ORDER BY clause. You could have a complicated CASE clause in your order by listing all the elements in your IN clause again, but then your query would probably be 120K.

I know you don't want to, but you should put the values in the IN clause in a table or a temporary table and join with it. You can also include a SortOrder column in the temporary table, and order by that. Databases like joins. Doing it this way will help your query to perform well.

Answer 2

You're first query surely uses an order by clause. So, you could just do a join, and use the same order by clause.

For example, if this was your first query

SELECT customer_id
  FROM customer  
 WHERE customer_id BETWEEN 1 AND 100 
ORDER
    BY last_name

And this was your second query

SELECT inventory_id
  FROM rental
 WHERE customer_id in (...the ordered list...)

Combined would be

SELECT r.inventory_id
  FROM rental r
INNER
  JOIN customer c
    ON r.customer_id = c.customer_id    
 WHERE c.customer_id BETWEEN 1 AND 100 
ORDER
    BY c.last_name

Answer 3

A bit of a trick....

SELECT * FROM your_table
WHERE id IN (5,2,6,8,12,1)
ORDER BY FIND_IN_SET(id,'5,2,6,8,12,1') DESC;

note that the list of ID's in the find_in_set is a string, so its quoted. Also note that without DESC, they results are returned in REVERSE order to what the list specified.

Answer 4

Actually, this is better:

SELECT * FROM your_table
WHERE id IN (5,2,6,8,12,1)
ORDER BY FIELD(id,5,2,6,8,12,1);

heres the FIELD documentation:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field