The most efficient way to select thousands of rows from a list of identifiers

Question

Are there scalable ways to select thousands of rows using mysql IN or something similar?

eg.

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

Is this possible, or am I just dreaming? Schema - InnoDB, subject to change if another provides a more scalable solution.

For reference, I get search results from a set of identifiers returned from Solr. I would like to use mysql for the final search, as this would facilitate sorting and final filtering of these results (I will not go into details of the reasons).

EDIT:

The query may use the LIMIT clause while IN still contains all 100,000 identifiers

eg.

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

Answer 1

I, who was me, and not knowing your infrastructure, should try to insert this ID into a temporary table and use this table with a simple internal join. It can be as fast as this IN.

Answer 2

I approached the same question by inserting mysql into tables. The possible request size depends on your mysql configuration.

My approach was to split a large collection of identifiers into some parts.

Using Ruby on Rails:

 all_ids.each_slice(10000) do |ids_part| query = "INSERT INTO ..." end 

Perhaps this could also be a solution for selecting strings by identifiers.