DEV Community

Quayen
Quayen

Posted on Originally published at quayen.com

BigQuery vs MySQL: differences and when to use each

MySQL and BigQuery both speak SQL, but they are built for opposite jobs. MySQL runs your application: many small reads and writes, each in milliseconds. BigQuery answers questions about all of your data at once: fewer, larger queries over millions or billions of rows.

The main differences

  • Workload: MySQL is transactional (OLTP); BigQuery is an analytical (OLAP) warehouse.
  • Storage: InnoDB stores whole rows and uses indexes to find a few quickly. BigQuery stores each column separately and compressed, so a query reads only the columns it needs, across every row.
  • Speed: a primary-key lookup in MySQL takes well under a millisecond, but a GROUP BY over a big table can take minutes and slow the app. A BigQuery query rarely takes less than a second, but aggregates billions of rows in seconds.
  • Transactions and updates: MySQL handles thousands of concurrent single-row writes with ACID transactions. BigQuery supports UPDATE, DELETE and MERGE, but is designed for bulk changes.
  • Cost: MySQL costs what the server costs, busy or idle. BigQuery charges for storage plus bytes scanned (or reserved compute), so partitioning and clustering matter.
  • Operations: MySQL scales up and out with replicas; BigQuery is serverless.
  • Dialect: DATE_FORMAT becomes FORMAT_DATETIME, TIMESTAMPDIFF becomes TIMESTAMP_DIFF, and BigQuery adds STRUCT and ARRAY.

When to use each

  • MySQL: the database behind your application (orders, users, payments).
  • BigQuery: reporting, dashboards, ad-hoc analysis, ML, joining app data with other sources.

Why most teams use both

Heavy reports on production MySQL slow the app, and a read replica only moves the problem. The usual setup keeps MySQL for the app and copies its data into BigQuery: nightly exports at first, then change data capture from the binary log when people need fresh numbers and deletes to match.

Disclosure: I work on Quayen (MySQL to BigQuery CDC). Original article: quayen.com/docs/bigquery-vs-mysql.

Top comments (0)