What's the best way to get a bunch of rows from MySQL if you have an array of integer primary keys?
- by Evan P.
I have a MySQL table with an auto-incremented integer primary key. I want to get a bunch of rows from the table based on an array of integers I have in memory in my program. The array ranges from a handful to about 1000 items.
What's the most efficient query syntax to get the rows?
I can think of a few:
"SELECT * FROM thetable WHERE id IN (1, 2, 3, 4, 5)" (this is what I do now)
"SELECT * FROM thetable where id = 1 OR id = 2 OR id = 3"
Multiple queries of the form "SELECT * FROM thetable WHERE id = 1". Probably the most friendly to the query cache, but expensive due to having lots of query parsing.
A union, like "SELECT * FROM thetable WHERE id = 1 UNION SELECT * FROM thetable WHERE id = 2 ..." I'm not sure if MySQL caches the results of each query; it's also the most verbose format.
I think using the NoSQL interface in MySQL 5.6+ would be the most efficient way to do this, but I'm not yet up to MySQL 5.6.
