Re: 4.1.8 summary: C-API/ODBC remote performance slow, Prepared Statements stall server, subselects ???, OR clauses in SELECTS

[Peter Zaitsev]

"Remote" Performance should not be 25% of Local performance on fast network.
First question is how many connections are you testing ? If it is single connection latency can be the problem. With 0.5ms latency you can get 2000 round trips per second which would
limit you to some 1000 queries/sec if you have 2 round trips per query (depending on various factors it can be much more)

I would also check connection via TCP/IP vs Unix Socket (on Linux) - TCP/IP can be 20% faster on Local host especially for small queries.

Subselects for some cases internally use temporary table. In such case they are mainly for convenience. It also allows one to run less statements which can be significant part of the load for simple queries.

In your case of splitting the query the thing is for full query only key prefix on (a) column can be used while for several query (or one query with UNION instead of OR) longer prefix could be used.