InnoDB for the fast. DuckDB for the heavy. Same connection for both.

Take an ordinary MySQL table. What is it? Most likely InnoDB, hopefully with a primary key, and full of the rows your application reads and writes all day.

Ask it for a report over every row, and wait. Minutes, several of them. You have seen this before, and suffered it.

Now send the same report to another port. Same SQL, same mysql client. It comes back in seconds, 10 times faster.

Then send a lookup by key to that same port. Your MySQL answers it. DBTrail chose, and you didn’t change a query.

You have just used Engine Routing. The table is still InnoDB. Your application still writes to it, on the connection it always used. Nothing was migrated.

DBTrail lets you use InnoDB and DuckDB for the same tables at the same time. You don’t have to pick a storage engine for each table: your tables are in both, and DBTrail keeps them in sync, minutes behind, like a replica.

Point your application’s reads at port 3309. Writes stay where they are.

The numbers

The database is the one HammerDB builds for its order-entry benchmark, TPROC-C: warehouses, districts, customers, orders, order lines and stock. Ours has 200 warehouses, with 10 districts in each, 3,000 customers in each district and 100,000 items. HammerDB keeps writing new orders to it the whole time.

On top of those tables we ask 14 questions a business would ask. Each one is a report with a name, and we ran every one of them on a MySQL replica and on DBTrail’s copy of the same database, read by DuckDB.

	Report	MySQL replica	DBTrail	DBTrail is
Q01	Warehouse leaderboardWhich warehouses take the most orders and collect the most money?	4.8 ms	57 ms	12× slower
Q02	Catalog price bandsHow is the product catalog priced?	121 ms	74 ms	1.5× faster
Q03	Order line fulfilment statusHow much ordered value is still waiting for delivery?	14.34 s	901 ms	15× faster
Q04	Sales trendHow are sales developing over time?	23.69 s	1.45 s	16× faster
Q05	Best sellersWhat sells best right now?	39.47 s	2.86 s	14× faster
Q06	Customer value segmentsHow is revenue spread over the customer base?	18.39 s	1.16 s	14× faster
Q07	Top customers per districtWho are the best customers of every district?	14.99 s	1.23 s	9.8× faster
Q08	Customer churnWhich customers stopped buying?	6.57 s	523 ms	8.7× faster
Q09	Order value decilesWhat does the distribution of order values look like?	22.85 s	1.03 s	15× faster
Q10	Stock-out riskWhich items will run out first?	54.44 s	3.48 s	16× faster
Q11	Market basket analysisWhich items are bought together?	94.95 s	1.80 s	54× faster
Q12	Company-wide supplier scorecardWhich warehouses carry the company’s fulfilment load?	177 s	4.63 s	48× faster
Q13	Company-wide order intake trendHow is order intake developing across the whole company?	13.83 s	415 ms	34× faster
Q14	Company-wide item demand vs stockWhich items sell fastest relative to what the company holds?	238 s	6.21 s	40× faster

Here are the lightest and the heaviest, as they run. The values in braces are drawn again for every run, and both sides get the same ones.

Q01, the warehouse leaderboard, reads a few rows of one small table through its primary key:

SELECT d_w_id                              AS w_id,
       SUM(d_next_o_id - 1)                AS orders_taken,
       SUM(d_ytd)                          AS ytd_payments,
       ROUND(SUM(d_ytd)
             / NULLIF(SUM(d_next_o_id - 1), 0), 2) AS payment_per_order,
       MAX(d_next_o_id) - MIN(d_next_o_id) AS district_spread,
       ROUND(100 * AVG(d_tax), 2)          AS avg_tax_pct
FROM   district
WHERE  d_w_id BETWEEN {wh_lo} AND {wh_hi}
GROUP  BY d_w_id
ORDER  BY {order_by} DESC, w_id
LIMIT  {top}

Q14, item demand against stock, adds up every order line and every stock row, and joins the two on the item:

WITH demand AS (
  SELECT ol_i_id AS i_id,
         SUM(ol_quantity) AS units,
         COUNT(*)         AS order_lines,
         SUM(CASE WHEN ol_delivery_d IS NULL
                  THEN ol_quantity ELSE 0 END) AS pending_units
  FROM   order_line
  WHERE  ol_quantity >= {min_qty}
  GROUP  BY ol_i_id
), supply AS (
  SELECT s_i_id AS i_id,
         SUM(s_quantity) AS on_hand,
         SUM(s_ytd)      AS shipped_ytd,
         SUM(CASE WHEN s_data LIKE '%ORIGINAL%'
                  THEN 1 ELSE 0 END) AS brand_warehouses
  FROM   stock
  GROUP  BY s_i_id
)
SELECT i.i_id, i.i_name, i.i_price,
       d.units, d.order_lines, d.pending_units,
       s.on_hand, s.shipped_ytd, s.brand_warehouses,
       ROUND(1.0 * s.on_hand / d.units, 3) AS cover_ratio
FROM   demand d
JOIN   supply s ON s.i_id = d.i_id
JOIN   item i   ON i.i_id = d.i_id
ORDER  BY cover_ratio, d.units DESC, i.i_id
LIMIT  {top}

Thirteen were faster on DuckDB. The slowest report on MySQL, item demand against stock over every order line, went from 238 seconds to 6.21. The market basket analysis went from 94.95 seconds to 1.80, which is 54 times faster.

One was slower, and it is the lightest. The warehouse leaderboard takes 4.8 milliseconds on MySQL and 57 on the copy. A lookup is what InnoDB is best at.

So neither engine is the fast one. It depends on the query. Pick one and you lose on some queries. That is the reason to keep both, and to have something pick for you.

How we got the numbers. Three machines:

• A primary with 2 CPUs and 3.8 GB of RAM. HammerDB runs TPROC-C on it, on the 200 warehouses above, at about 35 transactions a second.
• A MySQL 9.7 replica of that primary, with 4 CPUs and 7.8 GB of RAM and no replication lag.
• DBTrail 0.104.1, on a machine of the same size, updating its copy every five minutes. When we took the picture the copy was 3 minutes behind the primary. In the 15 minutes before, it was 9 minutes behind at most.

Each round sends one report, with the same parameters, to the replica and to DBTrail’s MySQL port. There is a round every 10 seconds at most, and a report that takes over 300 seconds counts as failed. None did, and none returned an error.

Each time in the table is the average of 13 or 14 runs of that report. For every round we divided the time on the replica by the time on DBTrail. The last column is the mean of those ratios for one report, which is why it is not exactly one average divided by the other. Over all 190 rounds, the geometric mean of the ratios is 11. Over the run it moves between 10 and 11, so we say 10.

Three things to know when you read it:

• Engine Routing was off, so the copy answered every report, the lookup too. This measures each engine by itself, not the routing. With routing on, that lookup goes to your MySQL.
• Twelve of the 14 reports run the same SQL text on both sides. Two of them, the sales trend and the order intake trend, group time into buckets, and MySQL and DuckDB spell that differently, so each side runs its own spelling.
• DBTrail allowed up to 4 GB of memory for a statement. However, it never used more than 800 MB.

It picks the engine, and you can see how

Your application sends its reads to port 3309 on the DBTrail machine.

For each SELECT, DBTrail asks your MySQL one question: how would you run this? MySQL answers with its plan and runs nothing.

If the plan finds a few rows with an index, your MySQL answers the query, live to the last write. If the plan reads a whole big table, the copy answers it, and your MySQL does no work for it.

Today “heavy” means a plan cost of 10,000 or more, or a table of 100,000 rows or more read from end to end.

You don’t keep a list of queries. One that is light today and heavy next year moves over by itself.

Being right comes before saving work. Inside a transaction, everything goes to your MySQL, so a read sees your own write. A query the copy would answer differently also goes to your MySQL: one that calls NOW() or GROUP_CONCAT, for example. So how much moves off your MySQL depends on how your reports are written.

Try one light query and one heavy one, on a table with many rows:

SELECT * FROM orders WHERE id = 42;

SELECT status, COUNT(*) FROM orders GROUP BY status;

Both come back in the same format, with the column names your MySQL would give. The first is live. The second is as old as the copy. To see who answered, ask the port:

mysql> SHOW ROUTER STATUS;
copy_age_seconds      574   how old the copy is
statements_copy       1     the GROUP BY, off your MySQL
statements_mysql      1     the lookup by id
statements_refused    0     a write would count here

You keep your MySQL

There is no migration. Your MySQL stays where it is, with InnoDB, your triggers and your stored procedures.

DBTrail reads its binlog with a replication account, as a replica would. It keeps a copy of your tables as Parquet files, in a folder or an S3 bucket you own, and updates the copy on the schedule you set. DuckDB reads those files.

You don’t rewrite a query. Reads get a new address. Writes keep the one they have.

It is a process between your application and its MySQL, so say the bad day out loud: if the port stops answering, the application gets errors on that connection. The way back is to point it at your MySQL again.

You don’t have to learn DuckDB

You connect with the mysql client you already use, and you send MySQL’s SQL.

What DuckDB cannot answer as your MySQL would, your MySQL answers. Your application keeps writing MySQL’s SQL and gets its answer.

You write DuckDB’s SQL only if you ask for it. One connection setting sends everything to the copy, for the day you want that.

Limits

The copy is asynchronous, like a MySQL replica. It follows your tables, minutes behind. A heavy query reads the copy, and the copy is as current as its last update. You set how often it updates. A report that must see a write made a second ago is not for this port.

DBTrail sits outside your MySQL and keeps the copy in files. That is why it asks nothing of your database, and it is why the copy is minutes behind.

By default the port refuses writes. Your application keeps its connection to your MySQL for writes and uses this port for reads. If it has one connection for everything, it needs a second one.

A transaction keeps everything on your MySQL. A driver that starts with autocommit off is always inside one, and nothing reaches the copy until you turn autocommit on for that connection.

Try it

DBTrail is open source, and the routing is in the Apache 2.0 core: github.com/dbtrail/dbtrail. You need version 0.104.0 or newer, capturing your MySQL.

Start without touching your application. bintrail-console sql-compare runs your own queries on both sides and lists the ones that would answer differently.

Then the guide takes you to your first routed query:

Run MySQL and DuckDB on the same connection

Keep the engine you have. Add the other.