Most efficient way of selecting thousands of rows from a list of ids

Question

Are there any scalable ways of selecting thousands of rows using mysql IN or something similar?

E.g.

SELECT * FROM awesomeTable WHERE id IN (1,2,3,4......100000)

Is this possible or am I just dreaming? Schema is InnoDB, can be changed if another would provide a more scalable solution.

For reference I am getting search results from a set of ids returned from Solr. I'd like to use mysql for the final retrieval as it would make sorting and final filtering of these results much easier (I won't get into the details of why).

EDIT:

The query could be use a LIMIT clause as long as the IN still contained all 100000 of the ids

E.g.

SELECT * FROM awesomeTable WHERE id IN (1,2,3,4......100000) LIMIT 10;

Answer 1

I that were me, and not really knowing your framework, you should try inserting those ID into a temporary table and using this table with a simple inner join. That could prove to be as fast as this IN.