Efficient way to query set of rows from MySQL

Question

I am using MySQL database engine. My Java application uses simple JDBC to retrieve data from database.
I have following database schema: main table with two columns: (INTEGER id, BLOB data). id is primary key.
From user actions I have set of few ids (x, y, ... all integers) for which I want to fetch data.
What is most efficient way to build query? Is there any performance difference if I use

SELECT data FROM table WHERE id IN (x, y, ...)

or

SELECT data FROM table WHERE id = x OR id = y OR ...

or maybe there is another way to build that query?

Answer 1

First, try putting together the ids in a query (a tableless query)

SELECT 1 id UNION
SELECT 2    UNION
SELECT 3    UNION
SELECT 4    UNION
SELECT 5    UNION
SELECT 6    UNION
SELECT 7;

Watch it work...

mysql> SELECT 1 id UNION
    -> SELECT 2    UNION
    -> SELECT 3    UNION
    -> SELECT 4    UNION
    -> SELECT 5    UNION
    -> SELECT 6    UNION
    -> SELECT 7;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
+----+
7 rows in set (0.00 sec)

mysql>

Now, JOIN that tableless query to the data table

SELECT
    B.mydata
FROM
    (SELECT 1 id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) A
    INNER JOIN mytable B ON A.id = B.id
;

or

SELECT
    B.mydata
FROM
    (SELECT 1 id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) A
    INNER JOIN mytable B USING (id)
;

Give it a Try !!!

Answer 2

I'm not sure about MySQL but in SQL Server the most efficient way is to set up a temporary table (such as with a table-valued parameter as input to a stored procedure) and then do a table join. Doing a "WHERE ... IN..." is typically going to be a table or index scan for every result, which can be one order of magnitude slower. You can experiment with the EXPLAIN command to compare-- I hear the latest version of MySQL has a much improved EXPLAIN. (SQL Server's diagramming of analyzing query performance is MUCH MUCH better though.)