The best query batching methods

MySQL & MariaDB Other MySQL® Questions
1

These are different request batching methods:

  1. make 2 concurrent requests with 2 tcp connections:
    session1 - “SELECT * FROM table WHERE key = 1;”
    session2 - “SELECT * FROM table WHERE key = 2;”

  2. concatenate SQL statements with semicolons;
    “SELECT * FROM table WHERE key = 1; SELECT * FROM table WHERE key = 2;”

  3. merge them with IN
    “SELECT * FROM table WHERE key IN (1, 2);”

Are 1, 2 slower than 3 because they open/lock the table twice? Or immediately accessing the same table one after another is just as fast as 3?

What’s the best practice for performance? WHERE … IN cannot be used with inequalities so 3 cannot be generalized.

2

You forgot #4 :slight_smile:

SELECT * FROM table WHERE key = 1
UNION
SELECT * FROM table WHERE key = 2

In this very specific example, #3 would be faster (by 0.001%, I’m joking here, I’m generalizing) because values 1 and 2 are extremely likely on the same page. #1 requires 2 different TCP connections, 2 different threads, 2 different txn views, 2 passes through the query optimizer/parser, etc. There’s no table locking on SELECTs in InnoDB.

IN() clauses become worse when you have 100s or 1000s of entries. In this case, better to create a temporary table populated with the IN() values and JOIN to the table.