mysqlmulti-query

Which one is faster single big query or few small queries?


So I want to grab data from database, which method is faster, create several queries or one multi-query?


Solution

  • Each "round trip" to the database will have some overhead. So the fewer round-trips, the less overhead. Consider also that fewer requests means fewer packets from client to server. If the result of the consolidated query gives you just what you want, then single query is the way to go. If your single query is returning extra or redundant data (perhaps because of de-normalization) then the overhead savings of a single round trip may be lost in the extra data transferred.

    Another consideration is latency. If the queries have to be completed in sequence because some part of the output of one is needed in the input of the next, consolidating into one query will cut out all the network latencies in between all the individual smaller queries, so a final result can be delivered faster. However, if the smaller queries are independent of each other, launching them in parallel can get all the results delivered faster, albeit less efficiently.

    Bottom line: the answer depends on the specifics of your situation. The best way to get an answer will probably be to implement both ways, test, and compare the resource usage of each implementation.