Is Fast SQL Cheap SQL? Testing It with TiDB Cloud

Introduction

The cost of TiDB Cloud Starter is determined mainly by the amount of Request Units (RU) consumed by SQL. So it is tempting to think that making SQL faster will also lower the cost.

In this article, I speed up SQL that returns the same result using three different methods, measure the execution time and RU in the same trials, and check the relationship between speed and cost.

Only when the SQL was made faster by reading less data were both the execution time and the RU significantly reduced. When I made it faster by increasing parallelism, the RU did not change, and when I eliminated lock waits, the execution time was reduced by 98.5% while the RU was reduced by only 50.0%.

What is TiDB Cloud

TiDB Cloud is a managed service of TiDB, a MySQL-compatible distributed database. Starter is a pay-as-you-go plan, and instances that meet the conditions get a free tier of 50 million RU per month.

Test environment

• TiDB Cloud Starter (AWS Tokyo region)
• TiDB v8.5.3
• macOS
• Python 3.13
• PyMySQL 2.2.8
• Measurement date: October 9, 2026

Target audience

• Developers using TiDB Cloud Starter
• Those who want to know the relationship between SQL tuning and cost

References

• TiDB Cloud Starter Pricing Details (PingCAP)
• Use Resource Control to Achieve Resource Group Limitation and Flow Control (PingCAP)
• System Variables (PingCAP)

Background

The Starter pricing page shows how RU is calculated as follows.

• Reads: 1 RU for each 2 storage read batches, 8 read requests, or 64 KiB of read data
• Writes: 1 RU for each 2 write batches, 2 write requests, or 2 KiB of written data
• SQL CPU time: 1 RU per 3 ms
• Network transmission: 1 RU per 1 KiB on the public endpoint

What appears in this formula is the amount of processing. On the other hand, the time saved by processing in parallel and the time spent waiting for locks to be released do not appear in the formula. If so, RU should show different results depending on the method of speeding up. Does making SQL faster lower the RU and cost on Starter? If so, what kind of speedup does it? I will check this with three kinds of speedups.

Test method

The subject is the order history screen of an e-commerce site.

It returns 100 paid orders of a certain customer, newest first, with the total amount for each order. I speed up this process in the following three sets, and compare the execution time and RU before and after.

Tables and data

I create orders for orders and order_items for order line items.

There are 10,000 orders, of which 5,000 are paid orders of customer 42. Each order has 10 line items, for a total of 100,000.

orders has a composite index ordered by customer, status, and order date/time.

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  customer_id INT NOT NULL,
  status TINYINT NOT NULL,
  ordered_at DATETIME NOT NULL,
  note VARCHAR(128) NOT NULL,
  KEY idx_history (customer_id, status, ordered_at DESC, id DESC)
);

CREATE TABLE order_items (
  order_id BIGINT NOT NULL,
  line_no TINYINT NOT NULL,
  qty TINYINT NOT NULL,
  unit_price INT NOT NULL,
  PRIMARY KEY (order_id, line_no)
);

The data is loaded with setup in the script described later.

After loading, I confirm with stats that the statistics have been reflected before measuring. This is because, before the statistics are reflected, the execution plan is built on an estimate without statistics (stats:pseudo). (*In this environment, they were reflected automatically about 2 minutes after loading.)

$ python ru_bench.py setup
orders, order_items: (10000, 100000)
$ python ru_bench.py stats
A stats:pseudo False
B stats:pseudo False

SQL to compare

A joins all 5,000 paid orders of customer 42 with their line items and aggregates them, and then narrows down to 100, newest first.

SELECT o.id, o.ordered_at, o.note,
       COALESCE(SUM(i.qty * i.unit_price), 0) AS total_yen
FROM orders AS o
LEFT JOIN order_items AS i ON i.order_id = o.id
WHERE o.customer_id = 42 AND o.status = 1
GROUP BY o.id, o.ordered_at, o.note
ORDER BY o.ordered_at DESC, o.id DESC
LIMIT 100;

B first selects the newest 100 using the index, and then joins and aggregates only those 100 with the line items.

SELECT p.id, p.ordered_at, p.note,
       COALESCE(SUM(i.qty * i.unit_price), 0) AS total_yen
FROM (
  SELECT id, ordered_at, note FROM orders
  WHERE customer_id = 42 AND status = 1
  ORDER BY ordered_at DESC, id DESC
  LIMIT 100
) AS p
LEFT JOIN order_items AS i ON i.order_id = p.id
GROUP BY p.id, p.ordered_at, p.note
ORDER BY p.ordered_at DESC, p.id DESC;

I confirmed that A and B return the same 100 rows by comparing the entire results before measuring. (*I also tried writing it with a correlated subquery that takes the total per order, but TiDB rewrote it into the same execution plan as B, so I excluded it from the comparison.)

tidb_executor_concurrency in Set 2 is a system variable that determines the parallelism used by operators when executing SQL, and its default value is 5. That A's execution plan is the same at parallelism 1 and 5 is confirmed inside the script by comparing the EXPLAIN results.

In Set 3, the following SQL is run inside an explicit transaction.

SELECT id, note FROM orders WHERE id = 1 FOR UPDATE;

The default transaction in Starter is a pessimistic transaction. If another session has locked the same row with FOR UPDATE, it waits until the lock is released. When locked, the other session locks the row and releases it with ROLLBACK 1 second later.

Measurement procedure

For each run, I record the elapsed time measured on the client and the RU. The elapsed time is the time from sending the SQL to receiving all rows. The RU is obtained by running SELECT @@tidb_last_query_info in the same session immediately after execution, and reading ru_consumption in the returned JSON.

$ python ru_bench.py bench ab
{"i": 0, "cond": "A", "sec": 0.106, "ru": 53.035}
{"i": 0, "cond": "B", "sec": 0.0287, "ru": 3.752}
(中略)

For each set, after running the two conditions 3 times each as a warm-up, I run the two conditions alternately 20 times each. The results are shown as the median of the 20 runs and the quartiles (the lower 25% value and the upper 25% value).

The whole script used for measurement is as follows. The connection information is passed via environment variables, using the values confirmed on the Connect screen of the TiDB Cloud console.

"""TiDB Cloud Starter で、SQL の実行時間と RU を対にして計測する。

接続情報は環境変数 TIDB_HOST, TIDB_PORT, TIDB_USER, TIDB_PASSWORD から読む。

    python ru_bench.py setup             # テーブル作成とデータ投入
    python ru_bench.py stats             # 統計情報の反映を確認 (stats:pseudo が False なら反映済み)
    python ru_bench.py bench ab|conc|lock  # 計測
"""

import json
import os
import ssl
import statistics
import sys
import threading
import time

import pymysql

DB = "ru_lab"

SETUP = [
    f"CREATE DATABASE IF NOT EXISTS {DB}",
    f"USE {DB}",
    "CREATE TABLE IF NOT EXISTS digit (n INT PRIMARY KEY)",
    "INSERT IGNORE INTO digit VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)",
    """CREATE TABLE IF NOT EXISTS orders (
      id BIGINT PRIMARY KEY,
      customer_id INT NOT NULL,
      status TINYINT NOT NULL,
      ordered_at DATETIME NOT NULL,
      note VARCHAR(128) NOT NULL,
      KEY idx_history (customer_id, status, ordered_at DESC, id DESC)
    )""",
    """CREATE TABLE IF NOT EXISTS order_items (
      order_id BIGINT NOT NULL,
      line_no TINYINT NOT NULL,
      qty TINYINT NOT NULL,
      unit_price INT NOT NULL,
      PRIMARY KEY (order_id, line_no)
    )""",
]

# 注文 10,000 件 (うち 1〜5,000 は顧客 42 の支払い済み注文) と、注文ごとに 10 件の明細
LOAD = [
    """INSERT INTO orders
    SELECT x.id,
           IF(x.id <= 5000, 42, IF(MOD(x.id, 100) = 42, 43, MOD(x.id, 100))),
           IF(x.id <= 5000, 1, IF(MOD(x.id, 7) = 0, 0, 1)),
           DATE_ADD('2026-09-01', INTERVAL MOD(x.id, 30 * 24 * 60) MINUTE),
           RPAD(CONCAT('memo-', x.id), 128, 'x')
    FROM (
      SELECT 1 + a.n + 10*b.n + 100*c.n + 1000*d.n AS id
      FROM digit a CROSS JOIN digit b CROSS JOIN digit c CROSS JOIN digit d
    ) AS x""",
    """INSERT INTO order_items
    SELECT o.id, e.n + 1, 1 + MOD(e.n, 3), 100 + MOD(o.id + e.n, 500)
    FROM orders AS o CROSS JOIN digit AS e""",
]

# 顧客 42 の支払い済み注文を新しい順に 100 件、合計金額つきで返す
QUERY_A = """SELECT o.id, o.ordered_at, o.note,
       COALESCE(SUM(i.qty * i.unit_price), 0) AS total_yen
FROM orders AS o
LEFT JOIN order_items AS i ON i.order_id = o.id
WHERE o.customer_id = 42 AND o.status = 1
GROUP BY o.id, o.ordered_at, o.note
ORDER BY o.ordered_at DESC, o.id DESC
LIMIT 100"""

QUERY_B = """SELECT p.id, p.ordered_at, p.note,
       COALESCE(SUM(i.qty * i.unit_price), 0) AS total_yen
FROM (
  SELECT id, ordered_at, note FROM orders
  WHERE customer_id = 42 AND status = 1
  ORDER BY ordered_at DESC, id DESC
  LIMIT 100
) AS p
LEFT JOIN order_items AS i ON i.order_id = p.id
GROUP BY p.id, p.ordered_at, p.note
ORDER BY p.ordered_at DESC, p.id DESC"""

LOCK_SQL = "SELECT id, note FROM orders WHERE id = 1 FOR UPDATE"
LOCK_HOLD_SEC = 1.0

def connect(database=DB):
    return pymysql.connect(
        host=os.environ["TIDB_HOST"],
        port=int(os.environ.get("TIDB_PORT", "4000")),
        user=os.environ["TIDB_USER"],
        password=os.environ["TIDB_PASSWORD"],
        database=database,
        ssl=ssl.create_default_context(),
        autocommit=True,
    )

def measure(cur, sql):
    """SQL を実行して全行を受け取るまでの秒数と、その SQL の RU を返す。"""
    start = time.perf_counter()
    cur.execute(sql)
    cur.fetchall()
    sec = time.perf_counter() - start
    cur.execute("SELECT @@tidb_last_query_info")
    return sec, json.loads(cur.fetchone()[0])["ru_consumption"]

def setup():
    cur = connect(None).cursor()
    for sql in SETUP:
        cur.execute(sql)
    cur.execute("SELECT COUNT(*) FROM orders")
    if cur.fetchone()[0] == 0:
        for sql in LOAD:
            cur.execute(sql)
    cur.execute("SELECT (SELECT COUNT(*) FROM orders), (SELECT COUNT(*) FROM order_items)")
    print("orders, order_items:", cur.fetchone())

def stats():
    cur = connect().cursor()
    for name, sql in (("A", QUERY_A), ("B", QUERY_B)):
        cur.execute("EXPLAIN " + sql)
        print(name, "stats:pseudo", "stats:pseudo" in str(cur.fetchall()))

def plan(cur, sql):
    """推定行数を除いた実行計画を返す。"""
    cur.execute("EXPLAIN " + sql)
    return [row[:1] + row[2:] for row in cur.fetchall()]

def pair_ab():
    cur = connect().cursor()
    queries = {"A": QUERY_A, "B": QUERY_B}
    return list(queries), lambda cond: measure(cur, queries[cond])

def pair_conc():
    cur = connect().cursor()

    def run(concurrency):
        cur.execute(f"SET SESSION tidb_executor_concurrency = {concurrency}")
        return measure(cur, QUERY_A)

    plans = []
    for concurrency in (1, 5):
        cur.execute(f"SET SESSION tidb_executor_concurrency = {concurrency}")
        plans.append(plan(cur, QUERY_A))
    print("same plan:", plans[0] == plans[1])
    return [1, 5], run

def pair_lock():
    holder = connect().cursor()
    waiter = connect().cursor()

    def run(cond):
        if cond == "wait":
            # 別のセッションが同じ行をロックし、LOCK_HOLD_SEC 秒後に解放する
            holder.execute("BEGIN")
            holder.execute(LOCK_SQL)
            holder.fetchall()
            timer = threading.Timer(LOCK_HOLD_SEC, lambda: holder.execute("ROLLBACK"))
            timer.start()
        waiter.execute("BEGIN")
        result = measure(waiter, LOCK_SQL)
        waiter.execute("ROLLBACK")
        if cond == "wait":
            timer.join()
        return result

    return ["no_wait", "wait"], run

def bench(pair, warmup=3, repeat=20):
    conds, run = {"ab": pair_ab, "conc": pair_conc, "lock": pair_lock}[pair]()
    for _ in range(warmup):
        for cond in conds:
            run(cond)
    results = {cond: [] for cond in conds}
    for i in range(repeat):
        for cond in conds:
            sec, ru = run(cond)
            results[cond].append((sec, ru))
            print(json.dumps({"i": i, "cond": cond, "sec": round(sec, 4), "ru": round(ru, 3)}))
    for cond, rows in results.items():
        for label, values in (("sec", [r[0] for r in rows]), ("ru", [r[1] for r in rows])):
            q1, median, q3 = statistics.quantiles(values, n=4, method="inclusive")
            print(f"{cond} {label}: median {median:.4g} (q1 {q1:.4g}, q3 {q3:.4g})")

if __name__ == "__main__":
    {"setup": setup, "stats": stats, "bench": lambda: bench(sys.argv[2])}[sys.argv[1]]()

Results

When reading less data

The results of running A and B alternately 20 times each are as follows.

$ python ru_bench.py bench ab
(中略)
A sec: median 0.1055 (q1 0.09584, q3 0.1099)
A ru: median 52.67 (q1 52.27, q3 53.08)
B sec: median 0.02899 (q1 0.0275, q3 0.03186)
B ru: median 3.742 (q1 3.727, q3 3.753)

With B, the execution time was reduced by 72.5% compared to A, and B was shorter than A in all 20 runs. The RU was reduced by 92.9%, dropping more than the execution time.

When increasing parallelism

The results of running A at parallelism 1 and 5 alternately 20 times each are as follows. same plan: True indicates that the execution plan was the same at the two parallelism levels.

$ python ru_bench.py bench conc
same plan: True
(中略)
1 sec: median 0.1371 (q1 0.13, q3 0.1429)
1 ru: median 52.2 (q1 51.96, q3 52.47)
5 sec: median 0.106 (q1 0.1003, q3 0.109)
5 ru: median 52.8 (q1 52.31, q3 53)

With parallelism 5, the execution time was reduced by 22.7% compared to parallelism 1, and parallelism 5 was shorter in 18 of the 20 runs. On the other hand, the RU was about 52 in both cases, and increasing parallelism did not change the RU.

When eliminating lock waits

The results of running the cases of waiting and not waiting for a lock on the same row alternately 20 times each are as follows.

$ python ru_bench.py bench lock
{"i": 0, "cond": "no_wait", "sec": 0.0147, "ru": 2.55}
{"i": 0, "cond": "wait", "sec": 1.0085, "ru": 5.1}
(中略)
no_wait sec: median 0.01509 (q1 0.01459, q3 0.01573)
no_wait ru: median 2.55 (q1 2.55, q3 2.55)
wait sec: median 1.007 (q1 1.004, q3 1.008)
wait ru: median 5.1 (q1 5.1, q3 5.1)

Eliminating the lock wait reduced the execution time by 98.5%. The RU reduction was 50.0%, from 5.10 to 2.55, which was not as large as the execution time. The RU was the same value in all 20 runs.

Conversion to cost

I multiplied the measured median RU by the unit price of $0.12 per million RU in the AWS Tokyo region to obtain the equivalent cost of running each SQL 1 million times. I also show the number of executions that fit within the free tier of 50 million RU per month per instance.

Rewriting A into B changes the equivalent cost per 1 million runs from about $6.32 to about $0.45. The difference due to parallelism is almost nil.

Findings and discussion

Summarizing the results of the three sets as reduction rates from the values before the change gives the following. (*Negative values represent an increase.)

Only in Set 1, where less data was read, did the RU drop as much as the execution time. A aggregates 5,000 orders and their line items before narrowing down to 100. B aggregates only 100 orders and their line items. Considering that the RU formula is determined by the amount of reads and CPU time, it is likely that the reduction in the amount of data processed directly became the difference in RU.

Parallelism merely splits the same amount of processing across multiple threads and does not change the amount of processing. So the RU probably did not change even though the execution time became shorter. As for lock waits, the waiting time itself is not included in the amount of processing, which is probably why the RU did not move as much as the execution time.

From this, when tuning SQL on Starter, it seems good to separate the metrics you look at depending on your goal. If you want to lower the cost, the shortcut is to check the RU, using @@tidb_last_query_info for example, rather than the execution time, and look for places where the amount of data read can be reduced. On the other hand, adjusting parallelism and resolving lock contention are valuable as improvements in response time, but their effect as a means of lowering cost seems small.

Summary

On TiDB Cloud Starter, I measured the execution time and RU of SQL made faster by three methods. Only when reading less data were both the execution time and the RU significantly reduced; when increasing parallelism, the RU did not change even though the execution time was reduced by 22.7%, and when eliminating lock waits, the execution time was reduced by 98.5% while the RU was reduced by 50.0%. In this test, the cost dropped significantly along with the speed only when the speedup came from reducing the amount of data processed. I hope this article helps you make decisions when thinking about SQL performance and cost on TiDB Cloud.