1T1: LOCK A → wait for B
2T2: LOCK B → wait for A

1SELECT * FROM orders WHERE order_id = 1 FOR UPDATE;

1-- Read: version = 5
2UPDATE orders SET status = 2, version = 6
3WHERE order_id = 1 AND version = 5;
4-- If 0 rows updated → conflict, retry

1BEGIN;
2  INSERT INTO orders ...;
3  SAVEPOINT after_order;
4  INSERT INTO payments ...;    -- fails
5ROLLBACK TO SAVEPOINT after_order;  -- only payment step is undone
6  -- retry payment with different logic
7COMMIT;

1CREATE INDEX idx ON orders(status, created_at, customer_id);
2
3-- ✅ Uses index
4WHERE status = 1 AND created_at > '2024-01-01'
5
6-- ✅ Uses index (partial — first column only)
7WHERE status = 1
8
9-- ❌ Does NOT use index — skips status
10WHERE created_at > '2024-01-01'

1Clustered:     leaf node = actual row data
2Non-Clustered: leaf node = index key + pointer to row (extra lookup)

1-- Query needs: customer_id, status, total_amount
2CREATE INDEX idx_covering ON orders(customer_id, status, total_amount);
3
4SELECT status, total_amount FROM orders WHERE customer_id = 45;
5-- Entire answer lives in the index. Table never touched.

1❌ orders: order_id | items = "Pizza, Burger, Coke"
2✅ order_items: order_id | item_name  (one row per item)

1❌ order_items: (order_id, item_id) | item_name | customer_name
2   customer_name depends only on order_id, not on item_id
3✅ Split: order_items(order_id, item_id, quantity) + orders(order_id, customer_name)

1❌ orders: order_id | customer_id | customer_city
2   customer_city depends on customer_id, not order_id
3✅ Split: orders(order_id, customer_id) + customers(customer_id, customer_city)

1-- Normalized: JOIN orders + restaurants on every read
2-- Denormalized: store restaurant_name directly in orders table
3ALTER TABLE orders ADD COLUMN restaurant_name VARCHAR(100);

1-- Query A: filter in ON clause
2SELECT * FROM orders o
3LEFT JOIN customers c ON o.customer_id = c.customer_id AND c.city = 'Mumbai';
4
5-- Query B: filter in WHERE clause
6SELECT * FROM orders o
7LEFT JOIN customers c ON o.customer_id = c.customer_id
8WHERE c.city = 'Mumbai';

1-- Find all employees and their managers (both in the same table)
2SELECT e.name AS employee, m.name AS manager
3FROM employees e
4LEFT JOIN employees m ON e.manager_id = m.employee_id;

1CALL GetOrdersByCustomer(45);

1-- State table: O(1) current state lookup
2CREATE TABLE orders (
3  order_id       INT PRIMARY KEY AUTO_INCREMENT,
4  customer_id    INT NOT NULL,
5  restaurant_id  INT NOT NULL,
6  current_status TINYINT NOT NULL DEFAULT 1,
7  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
8  updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
9);
10
11-- Event log: append-only, never updated
12CREATE TABLE order_events (
13  event_id    INT PRIMARY KEY AUTO_INCREMENT,
14  order_id    INT NOT NULL,
15  from_status TINYINT,
16  to_status   TINYINT NOT NULL,
17  changed_by  INT,
18  changed_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
19  FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE RESTRICT
20);

1Step 1: Verify indexes exist and are actually being used (EXPLAIN)
2Step 2: Check query — missing WHERE clause, functions on indexed columns, wrong column order in composite index
3Step 3: Add or fix indexes
4Step 4: Partition the table by created_at (range partition — most queries filter recent data)
5Step 5: Add read replicas — offload read traffic from primary
6Step 6: Introduce caching (Redis) for hot read paths
7Step 7: Archive old data to a separate table or OLAP system
8Step 8: Shard (last resort — only when a single server cannot handle load)

1EXPLAIN SELECT * FROM orders WHERE customer_id = 45 AND status = 2;

1-- N+1: 1 query for 100 orders + 100 queries for each order's customer = 101 queries
2-- Fix: JOIN or eager load in one query
3SELECT o.*, c.name FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

1Immediate: Kill the query if it's currently running
2Short-term: Add a read replica. Route all analyst queries to the replica.
3Medium-term: Set up an ETL pipeline to load data into a dedicated OLAP system (BigQuery / Redshift)
4Long-term: All analytics run on OLAP. Production DB is never touched for analytical workloads.

1-- ❌ Instant downtime
2ALTER TABLE orders RENAME COLUMN user_id TO customer_id;

1-- Step 1 EXPAND: Add new column as nullable
2ALTER TABLE orders ADD COLUMN customer_id INT NULL;
3
4-- Step 2 BACKFILL: Copy data in batches (with SLEEP to reduce I/O pressure)
5UPDATE orders SET customer_id = user_id LIMIT 10000;
6
7-- Step 3 MIGRATE: Deploy new app code that writes to customer_id
8
9-- Step 4 CONTRACT: Drop old column only after confirming zero reads
10ALTER TABLE orders DROP COLUMN user_id;

1-- Step 1: Add as NULL first — instant, no table rewrite
2ALTER TABLE orders ADD COLUMN delivery_fee DECIMAL(8,2) NULL;
3
4-- Step 2: Backfill in small batches with sleep (avoids I/O spike)
5UPDATE orders SET delivery_fee = 0.00
6WHERE delivery_fee IS NULL LIMIT 10000;
7-- Repeat until complete
8
9-- Step 3: Deploy app code that always writes delivery_fee
10
11-- Step 4: Add NOT NULL constraint after all rows are populated
12ALTER TABLE orders MODIFY COLUMN delivery_fee DECIMAL(8,2) NOT NULL;

1Free/small tier   → Shared table
2Mid-tier          → Separate schema
3Enterprise        → Separate database