Question: What are the differences between DynamoDB and MySQL?

Answer

DynamoDB is a serverless NoSQL key-value and document database, while MySQL is a relational SQL database. The main decision is how your application queries and relates data, not whether one can scale or support transactions.

Structure and querying

DynamoDB requires a declared primary key but allows other attributes to vary. A document-client item might be:

{"id":"123","name":"John Doe","email":"john.doe@example.com"}

MySQL uses table schemas, columns and SQL. For example:

CREATE TABLE Users (
  id VARCHAR(100) PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(255)
) ENGINE=InnoDB;

MySQL supports joins and relational constraints; DynamoDB typically uses denormalization and indexes designed around access patterns. DynamoDB's PartiQL syntax does not add relational joins.

Scaling and consistency

DynamoDB automatically partitions data and offers on-demand or provisioned throughput. A poor partition key can still create throttling. MySQL can scale through larger instances, read replicas and architectures that distribute data, but the deployment and query design determine the operational work involved.

Both provide ACID capabilities. MySQL's InnoDB engine supports transactions and isolation levels. DynamoDB offers atomic item operations and multi-item transaction APIs. DynamoDB base-table reads can be strong or eventual; GSI reads are eventual. MySQL reads from an asynchronously replicated server can lag, so "MySQL is always strongly consistent everywhere" is misleading.

Cost and fit

DynamoDB bills requests or provisioned capacity, storage and optional features. Self-managed MySQL has infrastructure and operator costs; managed MySQL services add their own resource pricing. Neither is universally cheaper for small or predictable workloads.

Choose DynamoDB when known key lookups and updates suit a serverless operational application. Choose MySQL when SQL joins, relational integrity and changing query requirements simplify the system. Heavy analytics may still justify a separate reporting store for either one.

See DynamoDB vs RDS, transactions and read consistency.

Source: MySQL InnoDB documentation.