CODEWITHSUNDEEP
mysqlperformance
Jake Armstrong
Jake Armstrong

Reputation: 579

MySQL Multiple Queries or use the IN(value1,value2,value3)

Just a short introduction:

I have one table t1( id(mediumint), string(varchar(45) ) both belong to primary key.

I need to find in this table 5 strings, let's say. And this table is like 5M + rows.

What would be faster?

  1. One query using IN():

    SELECT id, string FROM t1 WHERE string IN (value1,value2,...,value5)

    or

  2. Five queries, one for each value:

    SELECT id, string FROM t1 WHERE string = value1
SELECT id, string FROM t1 WHERE string = value2
'
'
SELECT id, string FROM t1 WHERE string = value5

The application server and database server will be on the same network (100Mbit or 1Gbit, not sure yet), not on the same machine.

Upvotes: 1

Views: 3168

Answers (3)

rid
rid

Reputation: 63442

Most of the time, using a single query is likely to be faster than using multiple queries, since it avoids the overhead of parsing, sending the queries and receiving multiple separate responses, and it allows for better optimization. When in doubt, profile.

Also keep in mind the length of the query. Most database servers have a limit of how long the query string can be. So, if you have long lists of values, you should send them in batches.

Upvotes: 5

Germann Arlington
Germann Arlington

Reputation: 3353

The only time when having multiple queries will be faster and more efficient is when you need complex and possibly multiple JOINs in your queries, than it is better to get separate results from separate tables and JOIN them in memory in your application. Mainly as a way of saving transfer time/bandwidth.
Every other time it is best to run single query.

Upvotes: 1

bobwienholt
bobwienholt

Reputation: 17610

One query using the IN() should be faster. Make sure you have an index with "string" as the first column.

Upvotes: 2

Related Questions