How important is it to merge queries together?

Reading Ernie’s post today inspired me to write about something I’ve been wanting to write about for a while: how much of a performance impact you should expect from network latency.

Hypothetical Numbers

I’m going to throw out some web-application ‘back of the envelope’ numbers which I will then use for examples:

• The goal is to generate a page within 200-500ms.
• Network latency between the application server and DB server is 0.5-1ms.

There will be use-cases which have goals far more agressive than this, for example advertising server goals are closer to 50ms. I have also seen network latency well below 1ms. I encourage commenters to correct me where they disagree 😉

Analysis

So if we say that a typical web page requires 5-10 queries to render, you can see that the amount of time spent in network latency could actually be very low:

10 queries @ 1ms each = 10ms, or 5% of our 200ms goal.
10 queries @ 0.5ms each = 5ms, or 2.5% of our 200ms goal.

However, what I want to do is illustrate the effects of the N+1 query pattern. That is, some applications will naively repeat a query inside a looping structure. So using the numbers we have established, lets take a look at the difference between:

• SELECT * FROM Country – 239 rows in 1 query.
• SELECT * FROM Country WHERE code = '?' – 239 queries with one row each.

Using performance_schema + ps_helper, I can show the time that it takes to execute on the server:

mysql> select * from statement_analysis\G
*************************** 4. row ***************************
        query: SELECT * FROM `country`
    full_scan: *
   exec_count: 3
    err_count: 0
   warn_count: 0
total_latency: 3.41 ms
  max_latency: 1.17 ms
  avg_latency: 1.14 ms
    rows_sent: 717
rows_sent_avg: 239
 rows_scanned: 717
       digest: 53567ecd08977b34a4532202a10871f4
*************************** 6. row ***************************
        query: SELECT * FROM `country` WHERE CODE = ?
    full_scan:
   exec_count: 5
    err_count: 0
   warn_count: 0
total_latency: 1.19 ms
  max_latency: 249.08 us
  avg_latency: 238.78 us
    rows_sent: 5
rows_sent_avg: 1
 rows_scanned: 5
       digest: cc32c7d6ec9e2803dea1ff95f458520a

Because the SELECT * FROM Country retrieves every row, it is going to be a table scan (which is quite efficient in this case). We can see that the query time is 1.14ms to retrieve all 239 rows versus 0.239ms to retrieve just a single row.

Retrieving all 239 rows from MySQL as primary key lookups takes 0.239ms * 239 = 57ms.

However, once you account for 0.5ms network latency:
* The single step retrieval is 1.14 + 0.5 = 1.64ms.
* Row-at-a-time retrieval is 119.5ms + 57ms = 176.5ms.

And with 1ms network latency:
* The single step retrieval is 1.14 + 1 = 2.14ms.
* Row-at-a-time retrieval is 239ms + 57ms = 296ms.

This example is largely the “worst-case” situation, which I am using to illustrate an example. 2.14ms versus 296ms isn’t even comparable 🙂 To apply context – this difference will basically double my page generation goal of 200-500ms.

Merging Queries

My next example to run through, is what happens when we merge queries together. In this case retrieving multiple rows via primary key at once:

SELECT * FROM Country WHERE code IN ('AUS', 'USA', 'CAN');

instead of:

SELECT * FROM Country WHERE code = 'AUS';
SELECT * FROM Country WHERE code = 'USA';
SELECT * FROM Country WHERE code = 'CAN';

We have the time for the single row query above. Here is the timing information from ps_helper for the IN list query:

*************************** 8. row ***************************
        query: SELECT * FROM `Country` WHERE CODE IN (...)
    full_scan:
   exec_count: 4
    err_count: 0
   warn_count: 0
total_latency: 1.19 ms
  max_latency: 318.66 us
  avg_latency: 296.59 us
    rows_sent: 12
rows_sent_avg: 3
 rows_scanned: 12
       digest: b19ca11697506fac486dd35535c37c32

With 0.5ms network latency total time is:
* (0.29659ms + 1 roundtrip) = 0.8 ms total
* (0.23878ms x 3 + 3 roundtrips) = 2.2ms total

With 1ms network latency:
* (0.29659ms + 1 roundtrip) = 1.3 ms total
* (0.23878ms x 3 + 3 roundtrips) = 3.7ms total

So by merging 3 queries together, I very nearly received a 3x reduction in query time.

Another was to look at this, is that for simple queries the execution cost can be near-free on the MySQL side. Network latency really can matter.