★ marked topics are high-priority for senior Java / Solution Architect interviews — explained in depth with diagrams. Other topics give concise, practical summaries.
Introduction to MySQL
🗄️ MySQL · Foundations · 5 min read
MySQL is the world's most popular open-source relational database management system (RDBMS). It stores data in tables (rows & columns), uses SQL to query it, and powers a huge share of web applications.
Why MySQL
Open source & free (Community Edition), with commercial support available.
ACID-compliant via the InnoDB storage engine — reliable transactions.
Mature & fast — battle-tested, great read performance, rich tooling.
Cross-platform and integrates with every major language/framework (e.g. Spring Data JPA).
-- connect & basic usage
mysql -u root -p
CREATE DATABASE shop;
USE shop;
SHOW TABLES;
💡 MySQL vs PostgreSQL: both are excellent RDBMS. MySQL is famed for read speed and ubiquity; PostgreSQL for advanced features and strict standards compliance. Concepts here (SQL, joins, transactions, indexing) transfer to both.
RDBMS Concepts
🗄️ MySQL · Foundations · 5 min read
A Relational Database Management System organizes data into relations (tables) with rows (records) and columns (attributes), linked by keys, and queried with SQL.
Term
Meaning
Table (relation)
A collection of rows for one entity
Row (tuple/record)
One instance of the entity
Column (attribute)
A field with a data type
Primary key
Uniquely identifies each row
Foreign key
References another table's PK (relationship)
Schema
The structure/blueprint of the database
💡 The "relational" part = tables related to each other via keys. This enforces integrity (foreign keys) and avoids duplication (normalization) — the core advantages over flat files or simple key-value stores.
Database Design & Best Practices
🗄️ MySQL · Foundations · ★ Interview Topic · 10 min read
Database design is the process of modeling data into well-structured tables and relationships that are correct, efficient, and maintainable. Good design prevents anomalies, redundancy, and performance pain later.
The Design Process — Visual
Best Practices
Model entities clearly — one table per entity; meaningful names (snake_case, plural tables).
Normalize to 3NF to eliminate redundancy, then denormalize selectively for read performance.
Choose right keys — surrogate BIGINT AUTO_INCREMENT PKs; FKs for integrity.
Pick correct data types — smallest that fits; DECIMAL for money (never FLOAT); DATETIME/TIMESTAMP for time.
Index for your queries — index FK and WHERE/JOIN columns; don't over-index.
Add constraints — NOT NULL, UNIQUE, CHECK, FK to enforce integrity at the DB.
Plan for growth — consider partitioning/archiving for large tables.
💡 Interview framing: design = ER model → normalize → choose keys/types → index for access patterns → enforce with constraints. The art is balancing normalization (integrity) against denormalization (read speed) for your workload.
Database Schema
🗄️ MySQL · Foundations · 4 min read
A schema is the structure of a database — its tables, columns, data types, keys, indexes, and relationships. In MySQL, "schema" and "database" are effectively synonyms.
CREATE TABLE users (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(120) NOT NULL UNIQUE,
name VARCHAR(80) NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
SHOW CREATE TABLE users; -- inspect the schema
DESCRIBE users; -- columns + types
💡 Keep schema changes in version control via migration tools (Flyway/Liquibase) so every environment evolves consistently and changes are auditable.
Data Types
🗄️ MySQL · Foundations · 5 min read
Choosing correct column types affects storage, performance, and correctness.
Category
Types
Integer
TINYINT, SMALLINT, INT, BIGINT
Decimal
DECIMAL(p,s) (money), FLOAT, DOUBLE
String
CHAR, VARCHAR, TEXT
Date/Time
DATE, DATETIME, TIMESTAMP, TIME
Other
BOOLEAN (TINYINT), ENUM, JSON, BLOB
⚠️ Use DECIMAL for money — FLOAT/DOUBLE are approximate and cause rounding errors. VARCHAR(n) over CHAR(n) for variable text. Pick the smallest integer type that fits to save space and improve index performance.
MySQL Architecture
🗄️ MySQL · Foundations · 6 min read
MySQL has a layered architecture: a connection/SQL layer on top, pluggable storage engines underneath.
💡 The query optimizer in the SQL layer decides how to run your query (which indexes, join order) — EXPLAIN shows its plan. The pluggable storage engine is MySQL's signature feature; InnoDB is the right default.
DDL Commands
🗄️ MySQL · Command Categories · 4 min read
Data Definition Language defines and modifies the database structure — tables, schemas, indexes. DDL statements auto-commit (can't be rolled back).
Command
Does
CREATE
Create a table/database/index
ALTER
Modify an existing structure
DROP
Delete a structure entirely
TRUNCATE
Remove all rows (reset table)
💡 The 4 SQL command families: DDL (structure), DML (data), DCL (permissions), TCL (transactions). DDL is auto-committing — there's no rollback for a dropped table.
DML Commands
🗄️ MySQL · Command Categories · 4 min read
Data Manipulation Language works with the data inside tables. DML runs inside transactions and can be rolled back.
Command
Does
SELECT
Read rows
INSERT
Add rows
UPDATE
Modify rows
DELETE
Remove rows
💡 Unlike DDL, DML changes are transactional — wrap them in BEGIN ... COMMIT/ROLLBACK for safety. (Some classifications put SELECT under DQL.)
DCL Commands
🗄️ MySQL · Command Categories · 3 min read
Data Control Language manages access rights and permissions.
GRANT SELECT, INSERT ON shop.* TO 'appuser'@'%';
REVOKE INSERT ON shop.* FROM 'appuser'@'%';
FLUSH PRIVILEGES;
💡 GRANT gives privileges; REVOKE takes them away. Follow least privilege — give an app user only the rights it needs (see Privileges and Roles).
TCL Commands
🗄️ MySQL · Command Categories · 3 min read
Transaction Control Language manages transactions — grouping DML into atomic units.
START TRANSACTION; -- or BEGIN
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK to undo
SAVEPOINT sp1; -- partial rollback point
💡 COMMIT makes changes permanent; ROLLBACK undoes them; SAVEPOINT marks a point to partially roll back to. See the Transactions section for depth.
CREATE Statement
🗄️ MySQL · DDL · 4 min read
CREATE defines new databases, tables, indexes, views, etc.
CREATE DATABASE shop;
CREATE TABLE products (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(120) NOT NULL,
price DECIMAL(10,2) NOT NULL,
category_id BIGINT,
FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB;
CREATE INDEX idx_category ON products(category_id);
💡 Specify ENGINE=InnoDB (the default) for transactions & foreign keys. Define keys/constraints at creation time where possible.
ALTER Statement
🗄️ MySQL · DDL · 4 min read
ALTER modifies an existing table — add/drop/modify columns, keys, and indexes.
ALTER TABLE products ADD COLUMN sku VARCHAR(40);
ALTER TABLE products MODIFY COLUMN price DECIMAL(12,2);
ALTER TABLE products DROP COLUMN sku;
ALTER TABLE products ADD CONSTRAINT uq_name UNIQUE (name);
ALTER TABLE products ADD INDEX idx_price (price);
⚠️ On large tables, ALTER can lock the table and run for a long time. Use online DDL (ALGORITHM=INPLACE, LOCK=NONE) or tools like pt-online-schema-change / gh-ost in production.
DROP Statement
🗄️ MySQL · DDL · 3 min read
DROP permanently deletes a database object and all its data/structure.
DROP TABLE products;
DROP DATABASE shop;
DROP INDEX idx_price ON products;
DROP TABLE IF EXISTS temp; -- safe guard
⚠️ DROP is irreversible (DDL auto-commits — no rollback) and removes both data and structure. Always have a backup before dropping in production. Use IF EXISTS to avoid errors in scripts.
TRUNCATE Statement
🗄️ MySQL · DDL · 4 min read
TRUNCATE removes all rows from a table quickly and resets it (incl. AUTO_INCREMENT), keeping the structure.
TRUNCATE
DELETE (no WHERE)
Type
DDL (auto-commit)
DML (rollback-able)
Speed
Very fast (drops & recreates)
Slower (row by row)
AUTO_INCREMENT
Reset to 1
Not reset
WHERE filter
No
Yes
Triggers
Not fired
Fired
💡 Classic interview question — DELETE vs TRUNCATE vs DROP: DELETE removes selected rows (DML, rollback); TRUNCATE empties the whole table fast (DDL, resets); DROP removes the table entirely.
INSERT Statement
🗄️ MySQL · DML · 3 min read
INSERT INTO users (name, email) VALUES ('Aftab', 'a@x.com');
INSERT INTO users (name, email) VALUES -- bulk insert
('Sam','s@x.com'), ('Ria','r@x.com');
INSERT INTO users SET name='Joe', email='j@x.com';
INSERT INTO archive SELECT * FROM users WHERE created_at < '2020-01-01';
INSERT INTO users (...) VALUES (...)
ON DUPLICATE KEY UPDATE name = VALUES(name); -- upsert
💡 Bulk insert (one statement, many rows) is far faster than many single inserts. ON DUPLICATE KEY UPDATE is MySQL's upsert.
UPDATE Statement
🗄️ MySQL · DML · 3 min read
UPDATE users SET status = 'active' WHERE id = 5;
UPDATE products SET price = price * 1.1 WHERE category_id = 3;
UPDATE orders o JOIN users u ON o.user_id = u.id -- update with join
SET o.vip = 1 WHERE u.tier = 'gold';
⚠️ Always include a WHERE clause — UPDATE without one changes every row. Test with a matching SELECT first, and run inside a transaction so you can roll back.
DELETE Statement
🗄️ MySQL · DML · 3 min read
DELETE FROM users WHERE id = 5;
DELETE FROM orders WHERE created_at < '2020-01-01' LIMIT 1000; -- batched
DELETE o FROM orders o JOIN users u ON o.user_id=u.id WHERE u.banned=1;
⚠️ Like UPDATE, a DELETE without WHERE wipes the whole table. For mass deletes, use LIMIT batches to avoid long locks; to empty a table entirely, TRUNCATE is faster.
SELECT Statement
🗄️ MySQL · DML · 5 min read
SELECT retrieves rows. Knowing the logical execution order of its clauses is key to writing correct queries.
SELECT category_id, COUNT(*) AS cnt
FROM products
WHERE price > 100
GROUP BY category_id
HAVING COUNT(*) > 5
ORDER BY cnt DESC
LIMIT 10;
Logical Processing Order
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
💡 This order explains common gotchas: you can't use a SELECT alias in WHERE (WHERE runs first) but you can in ORDER BY (it runs after SELECT). Avoid SELECT * in production — name the columns you need.
WHERE Clause
🗄️ MySQL · Querying · 4 min read
WHERE filters rows before grouping/aggregation using conditions and operators.
WHERE price BETWEEN 10 AND 100
WHERE status IN ('active','pending')
WHERE name LIKE 'A%' -- starts with A
WHERE email IS NOT NULL
WHERE price > 100 AND category_id = 3
WHERE created_at >= '2024-01-01'
💡 For WHERE to use an index, avoid wrapping the column in a function (WHERE YEAR(created_at)=2024 can't use an index — use a range instead). Leading wildcards (LIKE '%x') also prevent index use.
ORDER BY
🗄️ MySQL · Querying · 3 min read
Sorts the result set by one or more columns, ascending (default) or descending.
SELECT * FROM products ORDER BY price DESC;
SELECT * FROM users ORDER BY last_name ASC, first_name ASC; -- multi-column
SELECT name, price*0.9 AS sale FROM products ORDER BY sale; -- by alias
💡 An index on the ORDER BY column(s) lets MySQL skip a sort (avoids "Using filesort" in EXPLAIN). Multi-column sorts benefit from a matching composite index in the same order.
GROUP BY
🗄️ MySQL · Querying · 4 min read
Groups rows that share values, so aggregate functions compute per group.
SELECT category_id, COUNT(*) AS cnt, AVG(price) AS avg_price
FROM products
GROUP BY category_id;
⚠️ Every non-aggregated column in SELECT must appear in GROUP BY (enforced by ONLY_FULL_GROUP_BY, default in modern MySQL). To filter groups, use HAVING (not WHERE).
HAVING Clause
🗄️ MySQL · Querying · 3 min read
HAVING filters groups after aggregation — like WHERE, but it can use aggregate functions.
SELECT category_id, COUNT(*) AS cnt
FROM products
GROUP BY category_id
HAVING COUNT(*) > 5; -- filter groups
WHERE
HAVING
Filters
Rows (before grouping)
Groups (after aggregation)
Aggregates
No
Yes
💡 Interview classic: WHERE filters rows before GROUP BY; HAVING filters the aggregated groups after. Put row conditions in WHERE (faster, index-able) and only aggregate conditions in HAVING.
DISTINCT
🗄️ MySQL · Querying · 3 min read
Removes duplicate rows from the result set.
SELECT DISTINCT country FROM users;
SELECT DISTINCT country, city FROM users; -- distinct combinations
SELECT COUNT(DISTINCT country) FROM users;
💡 DISTINCT applies to the whole row (all selected columns), not just the first. It triggers a sort/hash — can be costly on large sets; sometimes a GROUP BY or EXISTS is clearer/faster.
LIMIT
🗄️ MySQL · Querying · 3 min read
Restricts the number of returned rows — for top-N queries and pagination.
SELECT * FROM products ORDER BY price DESC LIMIT 10; -- top 10
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40; -- page 3 (size 20)
⚠️ Large OFFSET is slow — MySQL scans & discards all skipped rows. For deep pagination, use keyset (seek) pagination: WHERE id > :lastId ORDER BY id LIMIT 20.
Aggregate Functions
🗄️ MySQL · Functions · 3 min read
Compute a single value over a set of rows — usually with GROUP BY.
💡 Avoid using string functions on indexed columns in WHERE (WHERE UPPER(email)=...) — it disables the index. Normalize data on write instead, or use a generated/functional index.
Date Functions
🗄️ MySQL · Functions · 3 min read
NOW(), CURDATE(), CURTIME()
DATE_ADD(d, INTERVAL 7 DAY)
DATEDIFF(d1, d2) -- days between
DATE_FORMAT(d, '%Y-%m-%d')
YEAR(d), MONTH(d), DAY(d)
TIMESTAMPDIFF(HOUR, t1, t2)
⚠️ For range filters use WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01' rather than WHERE YEAR(created_at)=2024 — the former can use an index, the latter can't.
💡 Use ROUND(x, n) for display rounding, but store money as DECIMAL and do exact arithmetic — never round-trip through FLOAT.
Joins
🗄️ MySQL · Joins · ★ Interview Topic · 11 min read
A JOIN combines rows from two or more tables based on a related column. Joins are how normalized data is reassembled — and a top interview topic.
The Join Types — Visual
Quick Reference
Join
Returns
INNER JOIN
Only matching rows in both tables
LEFT JOIN
All left rows + matches (NULLs if none)
RIGHT JOIN
All right rows + matches
FULL JOIN
All rows from both (emulated in MySQL)
CROSS JOIN
Cartesian product (every combination)
SELF JOIN
Table joined to itself
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE o.total > 100;
⚠️ Index the join columns (usually the FK) — unindexed joins force full scans and explode in cost. Watch out for accidental Cartesian products from a missing/incorrect ON condition. Use EXPLAIN to confirm the join strategy.
💡 Interview default: INNER for "rows that match in both"; LEFT for "all of A, plus B if present" (great for finding non-matches with WHERE b.id IS NULL).
Inner Join
🗄️ MySQL · Joins · 3 min read
Returns only rows where the join condition matches in both tables. The most common join.
SELECT o.id, u.name
FROM orders o
INNER JOIN users u ON u.id = o.user_id;
-- orders without a matching user are excluded
💡 JOIN with no keyword = INNER JOIN. If a row has no match on the other side, it's dropped from the result.
Left Join
🗄️ MySQL · Joins · 3 min read
Returns all rows from the left table, plus matching right-table rows (NULLs where there's no match).
SELECT u.name, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
-- users with no orders still appear (order_id = NULL)-- find users who never ordered:
SELECT u.* FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;
💡 The LEFT JOIN ... WHERE right.id IS NULL pattern is the standard way to find "rows in A with no match in B" (anti-join).
Right Join
🗄️ MySQL · Joins · 2 min read
The mirror of LEFT JOIN — returns all rows from the right table plus matches from the left.
SELECT o.id, u.name
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;
-- all orders, even those with no valid user
💡 RIGHT JOIN is rarely used in practice — most people reorder the tables and use LEFT JOIN for readability. Any RIGHT JOIN can be rewritten as a LEFT JOIN.
Full Join
🗄️ MySQL · Joins · 3 min read
Returns all rows from both tables, matching where possible. MySQL has no native FULL JOIN — emulate it with UNION of LEFT + RIGHT.
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id
UNION
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id;
💡 PostgreSQL/SQL Server support FULL OUTER JOIN directly. In MySQL, the LEFT ∪ RIGHT UNION trick gives the same result.
Self Join
🗄️ MySQL · Joins · 3 min read
A table joined to itself (using aliases) — for hierarchical or comparative data within one table.
-- employees and their managers (same table)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
💡 The classic use: org charts (employee → manager), or comparing rows within a table (e.g. find pairs). Always alias the table twice to distinguish the two "copies."
Cross Join
🗄️ MySQL · Joins · 2 min read
Produces the Cartesian product — every row of A combined with every row of B (no ON condition).
SELECT s.size, c.color
FROM sizes s CROSS JOIN colors c; -- all size/color combos
⚠️ Result size = rows(A) × rows(B) — explodes fast. An accidental cross join (forgetting the ON clause in a regular join) is a common cause of runaway queries. Use it intentionally for generating combinations.
Subqueries
🗄️ MySQL · Subqueries & Views · 4 min read
A subquery is a query nested inside another — in WHERE, FROM, or SELECT.
-- scalar subquery
SELECT name FROM products WHERE price > (SELECT AVG(price) FROM products);
-- IN subquery
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- derived table (FROM subquery)
SELECT c, n FROM (SELECT category_id c, COUNT(*) n FROM products GROUP BY category_id) t
WHERE n > 5;
💡 Many subqueries can be rewritten as JOINs, which the optimizer often handles better. Use EXISTS instead of IN for large/correlated checks. CTEs (WITH, MySQL 8+) improve readability over nested subqueries.
Correlated Subqueries
🗄️ MySQL · Subqueries & Views · 4 min read
A correlated subquery references the outer query — so it re-runs for each outer row.
-- each user's orders above their own average
SELECT o.* FROM orders o
WHERE o.total > (SELECT AVG(o2.total) FROM orders o2 WHERE o2.user_id = o.user_id);
-- EXISTS (correlated) — users who have at least one order
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
⚠️ Correlated subqueries run once per outer row — potentially slow on large datasets. Consider rewriting as a JOIN or using window functions (MySQL 8+). EXISTS short-circuits on the first match, often faster than IN here.
Views
🗄️ MySQL · Subqueries & Views · 8 min read · ★ Interview Topic
A view is a virtual table defined by a stored SELECT query. It doesn't store data — it runs its query each time you read it, presenting a simplified or secured slice of the underlying tables.
How a View Works — Visual
CREATE VIEW active_customers AS
SELECT u.id, u.name, COUNT(o.id) AS orders
FROM users u JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
GROUP BY u.id, u.name;
SELECT * FROM active_customers WHERE orders > 5; -- query it like a table
Why Use Views
Simplify complex queries — hide joins/aggregations behind a name.
Security — expose only certain columns/rows; grant access to the view, not the tables.
Abstraction — change underlying tables without breaking consumers.
⚠️ A view runs its query every time (no stored data, so no speedup by itself). Some views are updatable (simple, single-table); complex ones (joins, aggregates, DISTINCT) are read-only. For cached results, use a summary table or materialized-view pattern (next).
Materialized Views (Concept)
🗄️ MySQL · Subqueries & Views · 4 min read
A materialized view physically stores the query result and refreshes periodically — trading freshness for read speed. MySQL has no native materialized views; you emulate them.
-- emulation: a summary table refreshed on a schedule/trigger
CREATE TABLE mv_sales_daily (day DATE, total DECIMAL(12,2));
-- refresh via event/cron:
REPLACE INTO mv_sales_daily
SELECT DATE(created_at), SUM(total) FROM orders GROUP BY DATE(created_at);
💡 Regular views = always fresh, computed on read. Materialized = precomputed & stored (fast reads, can be stale). PostgreSQL/Oracle support them natively; in MySQL use a summary table refreshed by a scheduled event or triggers.
Stored Procedures
🗄️ MySQL · Programmable · ★ Interview Topic · 8 min read
A stored procedure is a named set of SQL statements stored in the database and executed with CALL. It encapsulates logic, supports parameters and control flow, and runs close to the data.
DELIMITER //
CREATE PROCEDURE transfer(IN from_id BIGINT, IN to_id BIGINT, IN amt DECIMAL(10,2))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN ROLLBACK; RESIGNAL; END;
START TRANSACTION;
UPDATE accounts SET balance = balance - amt WHERE id = from_id;
UPDATE accounts SET balance = balance + amt WHERE id = to_id;
COMMIT;
END //
DELIMITER ;
CALL transfer(1, 2, 100.00);
✓ Pros
Logic close to data (less round-trips)
Reusable, parameterized
Can reduce network traffic
Centralized, can be permission-controlled
✗ Cons
Business logic in DB (hard to version/test)
DB-specific (poor portability)
Harder debugging
Can overload the DB server
💡 Use procedures for data-intensive batch operations and to enforce critical transactions atomically. Modern apps often keep business logic in the application layer (testable, portable) and reserve procedures for performance-critical DB work.
Functions (Stored)
🗄️ MySQL · Programmable · 3 min read
A stored function returns a single value and can be used inside SQL expressions (unlike a procedure).
DELIMITER //
CREATE FUNCTION discounted_price(price DECIMAL(10,2), pct INT)
RETURNS DECIMAL(10,2) DETERMINISTIC
BEGIN
RETURN price - (price * pct / 100);
END //
DELIMITER ;
SELECT name, discounted_price(price, 10) FROM products;
Procedure
Function
Returns
0..N via OUT params
Exactly one value
Called by
CALL
Inside SQL expressions
Transactions
Yes
No (typically)
💡 Use a function when you need a reusable computed value inside queries; a procedure for multi-statement operations and transactions.
Triggers
🗄️ MySQL · Programmable · 4 min read
A trigger automatically runs in response to INSERT/UPDATE/DELETE on a table — for auditing, validation, or derived data.
CREATE TRIGGER audit_user_update
AFTER UPDATE ON users
FOR EACH ROW
INSERT INTO user_audit(user_id, old_email, new_email, changed_at)
VALUES (OLD.id, OLD.email, NEW.email, NOW());
Timing: BEFORE/AFTER; events: INSERT/UPDATE/DELETE. Use OLD. and NEW. to access row values.
⚠️ Triggers run invisibly — they make behaviour hard to trace and can hurt performance on bulk operations. Use sparingly (audit logs are a good fit); keep complex logic in the application layer.
Cursors
🗄️ MySQL · Programmable · 3 min read
A cursor iterates row-by-row over a result set inside a stored procedure — for procedural processing when set-based SQL won't do.
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT id FROM users;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO uid;
IF done THEN LEAVE read_loop; END IF;
-- process each row
END LOOP;
CLOSE cur;
⚠️ Cursors are slow (row-by-row vs set-based). Almost always a single set-based SQL statement is faster and clearer — reach for cursors only when truly necessary.
Indexes
🗄️ MySQL · Indexes · ★ Interview Topic · 11 min read
An index is a data structure (usually a B+ tree) that speeds up data retrieval — like a book's index, it lets the DB find rows without scanning the whole table. The single biggest lever for query performance.
With vs Without an Index — Visual
CREATE INDEX idx_email ON users(email);
CREATE UNIQUE INDEX uq_sku ON products(sku);
CREATE INDEX idx_cat_price ON products(category_id, price); -- composite
SHOW INDEX FROM users;
When to Index
Columns in WHERE, JOIN (FKs!), ORDER BY, GROUP BY.
⚠️ Indexes speed reads but slow writes (each INSERT/UPDATE maintains them) and use disk. Don't over-index. They're useless if a query wraps the column in a function or uses a leading LIKE '%x'. Verify usage with EXPLAIN.
💡 Interview essentials: B+ tree structure, the read/write trade-off, composite-index left-prefix rule, and "covering index" (query satisfied entirely from the index). Index FK and frequent filter columns first.
Clustered Index
🗄️ MySQL · Indexes · 4 min read
A clustered index determines the physical order of rows on disk — the table is the index. In InnoDB, the primary key is the clustered index; rows are stored in PK order.
💡 Consequences in InnoDB: (1) there's exactly one clustered index (the PK); (2) PK lookups are very fast (data is right there); (3) a random PK (UUID) causes page splits/fragmentation on insert — prefer a monotonic AUTO_INCREMENT PK. Secondary indexes store the PK value to find the row.
Non-Clustered Index
🗄️ MySQL · Indexes · 4 min read
A non-clustered (secondary) index is a separate structure that stores the indexed column(s) + a pointer to the row (the PK in InnoDB). The table data stays in clustered (PK) order.
Clustered
Non-clustered
Per table
One (the PK)
Many
Stores
The actual rows
Key + PK pointer
Lookup
Direct
Index → PK → row (2 steps)
💡 A secondary-index lookup does a "bookmark lookup" (index → clustered index → row). A covering index (includes all needed columns) skips that second step — much faster.
Composite Index
🗄️ MySQL · Indexes · 4 min read
A composite (multi-column) index covers several columns. Column order matters — the leftmost-prefix rule.
CREATE INDEX idx_cat_price ON products(category_id, price);
-- USES the index:
WHERE category_id = 3 -- leftmost
WHERE category_id = 3 AND price > 100 -- both-- does NOT use it:
WHERE price > 100 -- skips the leading column
💡 Left-prefix rule: a composite index on (a, b, c) helps queries filtering on a, a+b, or a+b+c — but not b alone or c alone. Order columns by selectivity and query patterns (equality columns first, then range).
Unique Index
🗄️ MySQL · Indexes · 3 min read
A unique index enforces that all values in the column(s) are distinct — combining a constraint with index performance.
CREATE UNIQUE INDEX uq_email ON users(email);
-- duplicate insert now fails with a duplicate-key error
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);
💡 A unique index both speeds lookups and guarantees uniqueness. Unlike a PK, it allows (one) NULL in MySQL and you can have several per table. The PK is a special unique + not-null + clustered index.
Query Optimization
🗄️ MySQL · Optimization · ★ Interview Topic · 9 min read
Query optimization is making queries run faster — primarily by helping the optimizer use indexes and read fewer rows. Always measure with EXPLAIN first.
Optimization Checklist
Index the right columns — WHERE, JOIN (FKs), ORDER BY, GROUP BY.
Keep WHERE sargable — no functions on indexed columns, no leading LIKE '%'.
Prefer JOINs over correlated subqueries; use EXISTS over IN for big sets.
Limit result size — paginate; use keyset pagination for deep pages.
Batch inserts/updates; avoid N+1 query patterns from the app.
-- Bad: function on column disables index
WHERE DATE(created_at) = '2024-01-01'
-- Good: sargable range
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'
💡 The optimizer picks the plan; your job is to give it good indexes and sargable queries. Process: run EXPLAIN → spot full scans/filesorts → add/fix an index → re-measure. 90% of slow queries are a missing index or a non-sargable WHERE.
Explain Plan
🗄️ MySQL · Optimization · ★ Interview Topic · 8 min read
EXPLAIN shows how MySQL will execute a query — which indexes it uses, the join order, and how many rows it estimates scanning. The first tool for diagnosing slow queries.
EXPLAIN SELECT * FROM orders WHERE user_id = 5;
EXPLAIN ANALYZE SELECT ...; -- MySQL 8: actual timings
Key Columns
Column
What to look at
type
Access type: const/eq_ref/ref = good · index/ALL = scan (bad)
key
Which index is used (NULL = none!)
rows
Estimated rows examined (lower = better)
Extra
Using index (covering, good) · Using filesort/temporary (costly)
💡 Red flags: type=ALL (full scan), key=NULL (no index), huge rows, or Using filesort/Using temporary in Extra. The fix is usually a better index or a sargable rewrite. Know the type ladder: system > const > eq_ref > ref > range > index > ALL.
Query Execution Plan
🗄️ MySQL · Optimization · 4 min read
The execution plan is the concrete strategy the optimizer chooses to run a query — access methods, join algorithm, and order. EXPLAIN exposes it.
Access methods — index lookup, range scan, full scan.
💡 The optimizer is cost-based — it uses table/index statistics. If plans look wrong, run ANALYZE TABLE to refresh statistics. Use optimizer hints only as a last resort.
SQL Performance Tuning
🗄️ MySQL · Optimization · ★ Interview Topic · 9 min read
Performance tuning spans queries, schema, indexes, server config, and the application. Measure first — find the actual bottleneck before changing anything.
Slow query log, EXPLAIN ANALYZE, Performance Schema, SHOW PROCESSLIST.
💡 The biggest single lever is almost always indexing + sargable queries. Next: innodb_buffer_pool_size (keep hot data in RAM) and killing N+1 patterns from the app. Enable the slow query log to find the worst offenders.
Normalization (1NF, 2NF, 3NF)
🗄️ MySQL · Normalization · ★ Interview Topic · 11 min read
Normalization organizes columns and tables to minimize redundancy and prevent update/insert/delete anomalies, by progressively applying normal forms.
The Normal Forms — Visual
Summary
Form
Rule
1NF
Atomic columns, no repeating groups, a primary key
2NF
1NF + no partial dependency (non-key cols depend on the whole PK)
3NF
2NF + no transitive dependency (non-key cols depend only on the key)
BCNF
Stricter 3NF — every determinant is a candidate key
💡 Mnemonic: "the key, the whole key, and nothing but the key" (1NF→2NF→3NF). Most schemas aim for 3NF, then denormalize selectively for read performance. Higher normal forms reduce redundancy but add joins.
1NF — First Normal Form
🗄️ MySQL · Normalization · 3 min read
A table is in 1NF when every column holds atomic (indivisible) values — no repeating groups or lists in a cell — and each row is unique (has a PK).
-- NOT 1NF: phones in one column
| id | name | phones |
| 1 | Sam | "111, 222" |
-- 1NF: one value per cell (separate rows or table)
| user_id | phone |
| 1 | 111 |
| 1 | 222 |
💡 The fix for non-atomic data is usually a separate child table (one-to-many). Storing comma-separated lists is the classic 1NF violation.
2NF — Second Normal Form
🗄️ MySQL · Normalization · 3 min read
1NF + no partial dependency: every non-key column depends on the entire primary key (only relevant with composite PKs).
-- PK = (order_id, product_id). product_name depends only on product_id → partial!
| order_id | product_id | product_name | qty |
-- 2NF: move product_name to a products table
order_items(order_id, product_id, qty)
products(product_id, product_name)
💡 2NF only matters when the PK is composite. The cure: split out columns that depend on just part of the key into their own table.
3NF — Third Normal Form
🗄️ MySQL · Normalization · 3 min read
2NF + no transitive dependency: non-key columns depend only on the key, not on other non-key columns.
-- dept_name depends on dept_id (a non-key col) → transitive
| emp_id | name | dept_id | dept_name |
-- 3NF: split department out
employees(emp_id, name, dept_id)
departments(dept_id, dept_name)
💡 3NF is the practical target for most OLTP schemas — it removes the redundancy that causes update anomalies (changing a dept name in one place, not hundreds of rows).
BCNF — Boyce-Codd Normal Form
🗄️ MySQL · Normalization · 3 min read
A stricter version of 3NF: for every functional dependency X → Y, X must be a candidate key. It handles edge cases 3NF misses (overlapping candidate keys).
💡 Most 3NF tables are already BCNF. BCNF matters in rare cases with multiple overlapping candidate keys. For interviews, knowing it's "3NF where every determinant is a candidate key" is enough.
Denormalization
🗄️ MySQL · Normalization · 4 min read
Denormalization deliberately adds redundancy (duplicated columns, precomputed aggregates) to a normalized schema to speed up reads by avoiding joins.
-- store order_count on users to avoid COUNT(*) joins on every read
ALTER TABLE users ADD COLUMN order_count INT DEFAULT 0;
-- keep it in sync via triggers / app logic / scheduled refresh
⚠️ Denormalization trades write complexity & consistency risk for read speed — you must keep the duplicated data in sync. Normalize first (3NF), then denormalize only proven read hotspots. Common in reporting/analytics and read-heavy systems.
Primary Key vs Unique Key
🗄️ MySQL · Keys & Constraints · ★ Interview Topic · 7 min read
Both enforce uniqueness, but a primary key is the single, non-null, canonical identifier of a row, while a unique key just guarantees distinct values on other columns.
Primary Key
Unique Key
Per table
Exactly one
Many allowed
NULLs
Not allowed
One NULL allowed (MySQL)
Index
Clustered (InnoDB)
Secondary (non-clustered)
Purpose
Row identity
Enforce distinctness on a column
CREATE TABLE users (
id BIGINT AUTO_INCREMENT PRIMARY KEY, -- the identifier
email VARCHAR(120) NOT NULL UNIQUE -- unique but not the PK
);
💡 Interview answer: a table has one PK (non-null, the clustered index, the identity); it can have many unique keys (allow a NULL, are secondary indexes). Prefer a surrogate AUTO_INCREMENT PK and put a UNIQUE on natural keys like email.
Foreign Key
🗄️ MySQL · Keys & Constraints · 4 min read
A foreign key links a column to another table's primary key, enforcing referential integrity — you can't reference a row that doesn't exist.
CREATE TABLE orders (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE -- delete orders when user is deleted
ON UPDATE RESTRICT
);
Action
On parent delete/update
CASCADE
Apply same change to children
RESTRICT / NO ACTION
Block if children exist
SET NULL
Null the child FK
💡 FKs require InnoDB. Always index the FK column (MySQL doesn't auto-index it on the child side) — unindexed FKs make joins and cascade checks slow.
Unique Key
🗄️ MySQL · Keys & Constraints · 3 min read
A unique key constraint ensures all values in a column (or set of columns) are distinct — preventing duplicates like two users with the same email.
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);
ALTER TABLE bookings ADD UNIQUE (room_id, date); -- composite unique
💡 A composite unique key enforces uniqueness on the combination (e.g. one booking per room per date). It also creates an index. Allows a single NULL per column in MySQL.
Composite Key
🗄️ MySQL · Keys & Constraints · ★ Interview Topic · 6 min read
A composite key is a primary (or unique) key made of two or more columns — used when no single column uniquely identifies a row. Common in join/junction tables.
💡 The composite PK both identifies the row and prevents duplicate enrollments. Column order matters for the resulting index (left-prefix rule) — put the most-queried column first. Many teams still prefer a surrogate id PK + a composite UNIQUE for simpler FKs.
Constraints
🗄️ MySQL · Keys & Constraints · 4 min read
Constraints enforce data integrity rules at the database level — so invalid data can never be stored, regardless of the application.
Constraint
Enforces
PRIMARY KEY
Unique + not null identity
FOREIGN KEY
Referential integrity
UNIQUE
No duplicate values
NOT NULL
Value required
CHECK
Custom condition (MySQL 8+)
DEFAULT
Fallback value
CREATE TABLE products (
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
status VARCHAR(20) DEFAULT 'active'
);
💡 Enforce integrity at the DB (constraints) and validate in the app — defense in depth. The DB is the last line of defense and guarantees consistency even if app validation is bypassed.
Relationships
🗄️ MySQL · Relationships · 4 min read
Relationships model how entities relate, implemented with foreign keys. Three cardinalities:
Type
Implementation
Example
One-to-One
FK with UNIQUE (or shared PK)
User ↔ Profile
One-to-Many
FK on the "many" side
User → Orders
Many-to-Many
Junction table
Students ↔ Courses
💡 The FK always lives on the "many" side. Cardinality drives the schema: get this right at design time — it's painful to change later.
One-to-One
🗄️ MySQL · Relationships · 3 min read
Each row in A relates to at most one row in B (and vice versa) — e.g. a user and their profile.
💡 Implement 1:1 by making the FK also UNIQUE (or the PK). Use it to split rarely-used/large columns (e.g. BLOBs) off the main table for performance.
One-to-Many
🗄️ MySQL · Relationships · ★ Interview Topic · 6 min read
The most common relationship: one parent row relates to many child rows — e.g. one user has many orders. The FK goes on the "many" side.
CREATE TABLE orders (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL, -- FK on the "many" side
FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE INDEX idx_user ON orders(user_id); -- always index the FK
💡 In JPA this maps to @OneToMany/@ManyToOne. Always index the FK column — it's used in every join and cascade. The "one" side has no FK; the "many" side carries it.
Many-to-Many
🗄️ MySQL · Relationships · ★ Interview Topic · 7 min read
Rows in A relate to many in B and vice versa — e.g. students and courses. SQL can't model this directly; you use a junction (join) table.
💡 A many-to-many is always resolved into two one-to-many relationships via a junction table. The junction can also hold relationship attributes (e.g. enrolled_at, grade). In JPA: @ManyToMany with @JoinTable.
Transactions
🗄️ MySQL · Transactions · ★ Interview Topic · 9 min read
A transaction is a sequence of operations executed as a single, all-or-nothing unit. Either every statement commits, or none does (rollback) — keeping data consistent.
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- if anything fails:
ROLLBACK; -- undo everything
-- else:
COMMIT; -- make permanent
The Money-Transfer Example
Debiting one account and crediting another must happen together. Without a transaction, a crash between the two updates loses money. The transaction guarantees both or neither.
💡 Transactions need a transactional engine — InnoDB (not MyISAM). They're governed by the four ACID properties and tuned by isolation levels (next topics). In Spring, @Transactional manages this for you.
ACID Properties
🗄️ MySQL · Transactions · ★ Interview Topic · 8 min read
ACID is the set of four guarantees that make database transactions reliable.
Property
Guarantee
Atomicity
All operations succeed or all roll back (no partial)
Consistency
Moves DB from one valid state to another (constraints hold)
Committed data survives crashes (written to disk/log)
💡 InnoDB delivers ACID: atomicity/durability via the redo/undo logs (write-ahead logging), isolation via MVCC + locking. This is the key reason to use InnoDB over MyISAM. Contrast with the CAP theorem for distributed systems.
Commit
🗄️ MySQL · Transactions · 2 min read
COMMIT makes all changes in the current transaction permanent and visible to other transactions.
START TRANSACTION;
INSERT INTO orders ...;
COMMIT; -- durable; can't be undone
💡 By default MySQL runs in autocommit=1 — each statement is its own committed transaction. START TRANSACTION suspends autocommit until you COMMIT/ROLLBACK.
Rollback
🗄️ MySQL · Transactions · 2 min read
ROLLBACK undoes all changes since the transaction began (or since a savepoint) — restoring the prior state.
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- error detected!
ROLLBACK; -- the update is undone
💡 Rollback works via InnoDB's undo log. DDL statements (CREATE/DROP/ALTER) auto-commit and cannot be rolled back — only DML can.
Savepoint
🗄️ MySQL · Transactions · 3 min read
A savepoint marks a point within a transaction you can partially roll back to, without aborting the whole transaction.
START TRANSACTION;
INSERT INTO log ...;
SAVEPOINT sp1;
UPDATE risky ...;
ROLLBACK TO sp1; -- undo only the UPDATE, keep the INSERT
COMMIT;
💡 Savepoints enable nested/partial rollback — useful in long procedures where you want to retry one step without losing earlier work. Spring's nested propagation uses savepoints.
Isolation Levels
🗄️ MySQL · Transactions · ★ Interview Topic · 9 min read
Isolation levels control how/when one transaction's changes become visible to others — trading consistency against concurrency. Each level prevents specific read anomalies.
Levels vs Anomalies — Visual
Level
Dirty read
Non-repeatable
Phantom
READ UNCOMMITTED
❌ possible
❌
❌
READ COMMITTED
✅ prevented
❌
❌
REPEATABLE READ (InnoDB default)
✅
✅
✅*
SERIALIZABLE
✅
✅
✅
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@transaction_isolation;
💡 Higher isolation = more consistency, less concurrency (more locking). InnoDB's default is REPEATABLE READ, and thanks to MVCC + gap locks it largely prevents phantoms too (*) — stronger than the SQL standard requires. Most apps are fine on the default or READ COMMITTED.
Dirty Reads
🗄️ MySQL · Transactions · 3 min read
A dirty read happens when a transaction reads data that another transaction has modified but not yet committed — and that change may later be rolled back.
T1: UPDATE accounts SET balance = 0 WHERE id=1; -- not committed
T2: SELECT balance FROM accounts WHERE id=1; -- reads 0 (dirty!)
T1: ROLLBACK; -- the 0 never really existed
💡 Only possible at READ UNCOMMITTED. Every higher level (READ COMMITTED and up) prevents dirty reads — you only see committed data.
Non-Repeatable Reads
🗄️ MySQL · Transactions · 3 min read
A non-repeatable read occurs when a transaction reads the same row twice and gets different values, because another committed transaction changed it in between.
T1: SELECT balance FROM accounts WHERE id=1; -- 100
T2: UPDATE accounts SET balance=50 WHERE id=1; COMMIT;
T1: SELECT balance FROM accounts WHERE id=1; -- 50 (changed!)
💡 Prevented at REPEATABLE READ and above (InnoDB's default), where a transaction sees a consistent snapshot for its whole duration via MVCC.
Phantom Reads
🗄️ MySQL · Transactions · 3 min read
A phantom read occurs when a transaction re-runs a range query and finds new rows that another committed transaction inserted matching the condition.
T1: SELECT * FROM orders WHERE total > 100; -- 5 rows
T2: INSERT INTO orders (total) VALUES (200); COMMIT;
T1: SELECT * FROM orders WHERE total > 100; -- 6 rows (phantom!)
💡 Fully prevented at SERIALIZABLE. InnoDB's REPEATABLE READ also blocks most phantoms using gap/next-key locks — going beyond the SQL standard. The three anomalies (dirty → non-repeatable → phantom) map directly to the isolation ladder.
Locking
🗄️ MySQL · Concurrency · ★ Interview Topic · 9 min read
Locks coordinate concurrent access to data so transactions don't corrupt each other. MySQL/InnoDB uses locks at different granularities and modes.
Lock Modes
Mode
Meaning
Shared (S) — read lock
Many can read; blocks writers
Exclusive (X) — write lock
One writer; blocks everyone
Row-level
Locks individual rows (InnoDB)
Table-level
Locks the whole table (MyISAM)
SELECT * FROM accounts WHERE id=1 FOR UPDATE; -- exclusive row lock
SELECT * FROM accounts WHERE id=1 FOR SHARE; -- shared row lock
Optimistic vs Pessimistic
Pessimistic — lock rows up front (FOR UPDATE); safe but reduces concurrency.
Optimistic — no locks; detect conflicts at commit via a version column. Better for low-contention.
💡 InnoDB does row-level locking + MVCC, so readers don't block writers and vice versa (huge concurrency win over MyISAM's table locks). Locks held too long cause contention and deadlocks (next).
Row-Level Locking
🗄️ MySQL · Concurrency · 3 min read
InnoDB locks only the specific rows a transaction touches, so other transactions can work on different rows concurrently — maximizing throughput.
💡 Row locks are set on index records. A query without a good index may lock many rows (or escalate effort) — another reason indexing matters. Includes record, gap, and next-key locks (the latter prevent phantoms).
Table-Level Locking
🗄️ MySQL · Concurrency · 3 min read
The entire table is locked for a write — used by MyISAM. Simple and low-overhead, but it serializes writes (only one writer at a time).
Row-level (InnoDB)
Table-level (MyISAM)
Concurrency
High
Low (one writer)
Overhead
Higher (track many locks)
Very low
Best for
OLTP, mixed read/write
Read-heavy, few writes
💡 Table locking kills write concurrency — a key reason InnoDB replaced MyISAM as the default. You can still take explicit table locks (LOCK TABLES) but rarely should.
Deadlocks
🗄️ MySQL · Concurrency · ★ Interview Topic · 8 min read
A deadlock occurs when two transactions each hold a lock the other needs — neither can proceed. InnoDB detects this and rolls back one transaction (the "victim").
Prevention
Consistent lock ordering — always acquire rows/tables in the same order (breaks the cycle).
Keep transactions short — hold locks briefly.
Right indexes — fewer rows locked.
Retry logic — catch the deadlock error (1213) and retry the transaction.
SHOW ENGINE INNODB STATUS; -- "LATEST DETECTED DEADLOCK" section
💡 InnoDB auto-detects deadlocks and rolls back the cheaper transaction — your app must catch error 1213 and retry. The most reliable prevention is consistent lock ordering + short transactions.
Concurrency Control
🗄️ MySQL · Concurrency · 4 min read
Concurrency control lets many transactions run simultaneously while preserving consistency. InnoDB combines MVCC with locking.
MVCC (Multi-Version Concurrency Control) — readers see a consistent snapshot without locking; writers create new row versions. So reads don't block writes.
Locking — for writes and explicit locks (FOR UPDATE).
Isolation levels — tune the consistency/concurrency trade-off.
💡 MVCC is why InnoDB scales: a long-running report (read) doesn't block transactional writes. Each transaction reads the snapshot as of its start (REPEATABLE READ) or statement (READ COMMITTED).
InnoDB Storage Engine
🗄️ MySQL · Storage Engines · ★ Interview Topic · 8 min read
InnoDB is MySQL's default storage engine — transactional, ACID-compliant, row-locking, with foreign keys and crash recovery. The right choice for almost all applications.
Data stored in PK order; buffer pool caches hot pages in RAM.
InnoDB
MyISAM
Transactions
Yes (ACID)
No
Locking
Row-level
Table-level
Foreign keys
Yes
No
Crash recovery
Yes
Weak
💡 Always use InnoDB unless you have a specific legacy reason. Tune innodb_buffer_pool_size to ~70% of RAM — keeping hot data/indexes in memory is the single biggest InnoDB performance setting.
MyISAM Storage Engine
🗄️ MySQL · Storage Engines · 3 min read
MyISAM is the older, non-transactional engine — fast for read-heavy workloads but lacks transactions, foreign keys, and row-level locking.
⚠️ MyISAM has no transactions, no FKs, table-level locking, and weak crash recovery — avoid it for anything that writes concurrently or needs reliability. It survives mainly in legacy systems and some read-only/full-text use cases (InnoDB now has full-text too).
💡 Interview answer: choose InnoDB for OLTP/transactional integrity (the default); MyISAM only for legacy or pure read-only data where its simplicity is acceptable.
Partitioning
🗄️ MySQL · Scaling & HA · ★ Interview Topic · 8 min read
Partitioning splits one large table into smaller physical pieces (within a single server) based on a key, while it still looks like one table. Improves query performance and maintenance on huge tables.
Partition Types
Type
Splits by
RANGE
Value ranges (e.g. by year)
LIST
Discrete value sets (e.g. region)
HASH
Hash of a column (even distribution)
KEY
Like HASH, MySQL-managed
CREATE TABLE orders (
id BIGINT, created_at DATE, ...
) PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
💡 Partition pruning is the win — a query filtering on the partition key only scans relevant partitions. Also makes archiving easy (DROP PARTITION p2023 is instant). Partitioning ≠ sharding: partitioning is one server, sharding spans many.
Replication
🗄️ MySQL · Scaling & HA · ★ Interview Topic · 9 min read
Replication copies data from a primary (master) to one or more replicas (slaves). It enables read scaling, high availability, and backups without loading the primary.
How It Works
Primary records changes in its binary log (binlog).
Replicas pull and replay the binlog to stay in sync.
Async (default, fast, possible lag) vs semi-sync (waits for one replica) vs group replication (synchronous, HA).
⚠️ Async replication has replication lag — a read replica may be slightly stale. Don't read-your-own-write from a replica right after writing to the primary. App must route writes → primary, reads → replicas.
Master-Slave Replication
🗄️ MySQL · Scaling & HA · 4 min read
The classic topology: one primary handles all writes, multiple replicas serve reads. Scales read-heavy workloads and provides a failover target.
Topology
Notes
Master-Slave
1 writer, N read replicas (read scaling)
Master-Master
2 writers (conflict risk; needs care)
Group Replication
Synchronous multi-node, auto-failover (HA)
💡 Read scaling is easy (add replicas); write scaling is hard (one primary) — that's where sharding comes in. On primary failure, promote a replica (manual, or automated with tools like Orchestrator / MySQL InnoDB Cluster).
Sharding
🗄️ MySQL · Scaling & HA · ★ Interview Topic · 9 min read
Sharding splits data horizontally across multiple servers (shards), each holding a subset of rows. It's how you scale writes and storage beyond one machine.
Strategies & Trade-offs
Range / Hash / Directory sharding by a shard key (e.g. user_id).
Choosing a good shard key is critical — avoid hotspots and cross-shard queries.
⚠️ Sharding adds major complexity: cross-shard joins/transactions are hard, rebalancing is painful, and JOINs across shards may need app-side merging. Exhaust vertical scaling, read replicas, and caching first. Partitioning (one server) ≠ sharding (many servers).
High Availability (HA) Databases
🗄️ MySQL · Scaling & HA · ★ Interview Topic · 8 min read
High availability means the database keeps serving through failures — minimizing downtime via redundancy and automatic failover.
HA Building Blocks
Replication
Replicas as standby; promote one if the primary fails.
Automatic Failover
InnoDB Cluster (Group Replication) + MySQL Router, or Orchestrator.
Multi-AZ
Spread nodes across availability zones (cloud RDS/Aurora).
Backups + PITR
Point-in-time recovery from backups + binlog.
💡 Measured by RTO (recovery time) & RPO (data loss window). Managed options (AWS RDS Multi-AZ, Aurora) provide HA with automatic failover out of the box. MySQL InnoDB Cluster = Group Replication + MySQL Router + Shell for self-hosted HA.
Database Scalability
🗄️ MySQL · Scaling & HA · ★ Interview Topic · 9 min read
Scalability is handling growing load. Databases scale vertically (bigger server) or horizontally (more servers), with reads and writes scaling very differently.
The Scaling Ladder — Visual
💡 Practical order: tune queries/indexes → add caching (Redis) → vertical scale → read replicas (read scaling) → partition large tables → shard (write scaling, last resort). Reads scale easily; writes are the hard limit — that's the core scalability insight.
Backup and Restore
🗄️ MySQL · Operations · 4 min read
Backups protect against data loss. MySQL offers logical (SQL dump) and physical (file copy) backups.
⚠️ Use --single-transaction for consistent InnoDB dumps without locking. An untested backup is not a backup — regularly practice restores. Combine full backups + binlog for point-in-time recovery.
Database Security
🗄️ MySQL · Operations · 4 min read
Least privilege — app users get only needed grants; never use root from apps.
Encrypt — TLS in transit; encryption at rest (TDE) for sensitive data.
No SQL injection — always use parameterized queries / prepared statements.
Network — bind to private interfaces, firewall the port (3306), no public exposure.
💡 The biggest real-world risks: SQL injection (fix with prepared statements), over-privileged accounts, and databases exposed to the internet. Run mysql_secure_installation on new installs.
User Management
🗄️ MySQL · Operations · 3 min read
CREATE USER 'app'@'%' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'%';
ALTER USER 'app'@'%' IDENTIFIED BY 'new_password';
DROP USER 'app'@'%';
SHOW GRANTS FOR 'app'@'%';
💡 The 'user'@'host' form scopes where a user can connect from — restrict the host (e.g. app subnet) rather than % (anywhere) in production.
Privileges and Roles
🗄️ MySQL · Operations · 3 min read
Privileges grant specific actions; roles (MySQL 8+) bundle privileges for easy assignment.
CREATE ROLE 'readwrite';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'readwrite';
GRANT 'readwrite' TO 'app'@'%';
SET DEFAULT ROLE ALL TO 'app'@'%';
💡 Roles (MySQL 8+) make privilege management scalable — define a role once, assign to many users. Always follow least privilege; grant at the narrowest scope (db.table) needed.
Connection Pooling
🗄️ MySQL · Operations · 4 min read
Opening a DB connection is expensive. A connection pool keeps a set of reusable connections open, handing them to the app on demand — drastically cutting latency and load.
# Spring Boot uses HikariCP by default
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=3000
⚠️ Size the pool carefully — too small starves the app; too large overwhelms MySQL (each connection uses memory + a thread). A common formula: pool ≈ (core_count × 2) + effective_spindles; measure under load. HikariCP is the fast default in Spring Boot.
Database Monitoring
🗄️ MySQL · Operations · 4 min read
Monitor health and performance to catch problems early.
What
How
Slow queries
Slow query log + pt-query-digest
Live queries
SHOW PROCESSLIST
Internals
Performance Schema, SHOW ENGINE INNODB STATUS
Metrics/alerts
Prometheus mysqld_exporter + Grafana, PMM
💡 Key metrics: query latency, slow-query count, connections, buffer pool hit ratio, replication lag, locks/deadlocks. Enable the slow query log first — it surfaces the worst offenders immediately.
Database Migration
🗄️ MySQL · Operations · 4 min read
Schema migrations evolve the database structure over time in a controlled, versioned, repeatable way.
# Flyway versioned migration files
V1__create_users.sql
V2__add_email_index.sql
V3__add_orders_table.sql
# Flyway/Liquibase apply pending migrations in order, tracked in a metadata table
💡 Keep migrations in version control alongside code (Flyway/Liquibase). For large tables, use online schema change tools (pt-online-schema-change, gh-ost) to avoid long locks. Always make migrations backward-compatible for zero-downtime deploys.
Data Archiving
🗄️ MySQL · Operations · 3 min read
Archiving moves old, rarely-accessed data out of hot tables into archive storage — keeping the active dataset small and fast.
# move old rows to an archive table, then delete in batches
INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < '2022-01-01';
DELETE FROM orders WHERE created_at < '2022-01-01' LIMIT 1000; # batched
💡 Smaller hot tables = faster queries, indexes, and backups. Partitioning makes archiving trivial — just DROP PARTITION for old date ranges. Define a retention policy aligned with compliance needs.
SQL Best Practices
🗄️ MySQL · Best Practices · 4 min read
✓ Do
Name explicit columns (no SELECT *)
Parameterize queries (prevent injection)
Index WHERE/JOIN/ORDER BY columns
Keep WHERE sargable (no fn on columns)
Paginate large result sets
Use transactions for multi-step writes
EXPLAIN slow queries
✗ Avoid
SELECT * in app code
String-concatenated SQL
Functions on indexed columns in WHERE
N+1 query patterns
Huge OFFSET pagination
Unbounded queries (no LIMIT)
Implicit cross joins
💡 The recurring theme: be explicit, be sargable, be indexed, be safe (parameterized + transactional). These habits prevent most performance and security problems.
Schema Design Best Practices
🗄️ MySQL · Best Practices · 4 min read
Normalize to 3NF, then denormalize selectively for read hotspots.
Surrogate PKs (BIGINT AUTO_INCREMENT); UNIQUE on natural keys.
Right data types — smallest that fits; DECIMAL for money; UTC DATETIME/TIMESTAMP.
Constraints — NOT NULL, FK, UNIQUE, CHECK to enforce integrity in the DB.
Index for access patterns — FKs and frequent filters; avoid over-indexing.
Plan for scale — partition/archive large tables; avoid unbounded columns.
💡 Good schema design is the foundation everything else rests on — it's far cheaper to get right up front than to refactor a live production database later.
CAP Theorem & Consistency vs Availability
🗄️ MySQL · Best Practices & Theory · ★ Interview Topic · 8 min read
The CAP theorem states a distributed data store can guarantee at most two of: Consistency, Availability, Partition tolerance. Since network partitions are unavoidable, the real choice under a partition is C vs A.
During a network partition you must choose: refuse to answer to stay consistent (CP), or answer with possibly-stale data to stay available (AP). Many systems offer eventual consistency (AP) or tunable consistency.
💡 A single MySQL server is effectively CA (no partitions within one node). Once you add replication/clustering across the network, you face CAP: async replicas favour availability (stale reads possible = AP-ish); synchronous group replication favours consistency (CP). Relational DBs lean CP; many NoSQL stores lean AP. Modern nuance: the PACELC theorem adds the latency-vs-consistency trade-off even when there's no partition.
