MySQL is the most popular open-source database, powering millions of web applications. Real examples: (1) InnoDB: WordPress uses InnoDB for transactional data (posts, comments, users). (2) JSON: Shopify uses JSON columns for flexible product attributes (no schema changes). (3) Replication: Facebook uses MySQL replication for read scaling (1 master, 100+ slaves). (4) AUTO_INCREMENT: Twitter uses AUTO_INCREMENT for tweet IDs (simple, fast). (5) Full-Text Search: Wikipedia uses FULLTEXT indexes for article search (< 1M articles). Trade-offs: MySQL is simpler than PostgreSQL (easier to learn) but less powerful (no arrays, limited JSON). Good for web apps, OLTP workloads, read-heavy systems with replication.

Facebook: Master-Slave Replication for Billions of Queries

Facebook handles billions of queries/day with heavy read traffic (90% reads, 10% writes). Challenge: Single database cannot handle load. Solution: Master-slave replication with 1 master + 100+ slaves.

Facebook uses master-slave replication: (1) Master handles writes (10% of traffic), (2) 100+ slaves handle reads (90% of traffic), (3) Load balancer distributes reads across slaves, (4) GTID for easy failover. Architecture: 1 master (writes), 100+ slaves (reads), load balancer (routes reads). Benefits: Read scaling (add more slaves), write performance (master not overloaded), high availability (failover to slave).

-- Master configuration

server-id = 1

log-bin = mysql-bin

gtid-mode = ON

-- Slave configuration

server-id = 2

read-only = 1

gtid-mode = ON

-- Application routes queries

-- Writes to master

INSERT INTO posts (user_id, content) VALUES (123, 'Hello');

-- Reads from slaves (load balanced)

SELECT * FROM posts WHERE user_id = 123;

Billions of queries/day, 90% reads

1 master + 100+ slaves for read scaling

Load balancer distributes reads

GTID for easy failover

Lesson: Replication for read-heavy workloads

MySQL

Shopify: JSON Columns for Flexible Product Attributes

Shopify has millions of products with varying attributes per category. Challenge: Fixed schema requires ALTER TABLE for new attributes (slow, downtime). Solution: Use JSON columns for flexible attributes.

Shopify uses JSON columns for product attributes: (1) attributes JSON column stores flexible attributes, (2) Generated columns for frequently queried fields (brand, size), (3) Indexes on generated columns for fast queries. Architecture: products table with attributes JSON, generated columns (brand, size), indexes on generated columns. Benefits: No ALTER TABLE needed (add attributes anytime), fast queries with indexes.

CREATE TABLE products (

product_id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(200),

attributes JSON,

brand VARCHAR(50) AS (attributes->>'$.brand') STORED

);

CREATE INDEX idx_brand ON products(brand);

INSERT INTO products (name, attributes) VALUES

('Laptop', JSON_OBJECT('brand', 'Apple', 'ram', '16GB'));

SELECT * FROM products WHERE brand = 'Apple';

Millions of products, varying attributes

JSON: No ALTER TABLE needed

Generated columns + indexes: Fast queries

Flexible schema: Add attributes anytime