Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
What are x and y? User supplied values? Or columns in (other or this) table? –  ypercube Feb 11 '13 at 14:22
    
There are integers. Ids of rows to fetch. I have edited question. –  Michał Herman Feb 11 '13 at 14:25
    
How many integers are you matching up ??? –  RolandoMySQLDBA Feb 11 '13 at 17:45
    
@RolandoMySQLDBA Not so much, usually in range [1, 20]. In table I have obove 100'000 objects –  Michał Herman Feb 11 '13 at 22:03

2 Answers 2

up vote 2 down vote accepted

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 !!!

share|improve this answer
    
This is something I did not thought about. I will give it a try :-) –  Michał Herman Feb 12 '13 at 7:59

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.)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.