-- ── CREATE TABLE ──
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
full_name VARCHAR(100),
age TINYINT UNSIGNED DEFAULT NULL,
balance DECIMAL(10,2) DEFAULT 0.00,
is_active BOOLEAN DEFAULT TRUE,
role ENUM('user','admin','moderator') DEFAULT 'user',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_email (email),
INDEX idx_role_active (role, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ── INSERT ──
INSERT INTO users (username, email, password, full_name)
VALUES ('alice', 'alice@example.com', SHA2('secret', 256), 'Alice Smith');
INSERT INTO users (username, email, password) VALUES
('bob', 'bob@example.com', SHA2('pass1', 256)),
('carol', 'carol@example.com', SHA2('pass2', 256)),
('dave', 'dave@example.com', SHA2('pass3', 256));
-- ── SELECT ──
SELECT * FROM users WHERE is_active = TRUE AND role = 'admin';
SELECT username, full_name FROM users WHERE age BETWEEN 25 AND 35;
SELECT * FROM users WHERE username LIKE 'al%';
SELECT * FROM users WHERE email IN ('a@x.com', 'b@x.com');
SELECT * FROM users WHERE role IS NOT NULL;
-- ── UPDATE ──
UPDATE users SET balance = balance + 100.00 WHERE id = 1;
UPDATE users SET is_active = FALSE WHERE last_login < '2024-01-01';
-- ── DELETE ──
DELETE FROM users WHERE is_active = FALSE AND created_at < '2023-01-01';
-- ── ALTER TABLE ──
ALTER TABLE users ADD COLUMN avatar VARCHAR(500) AFTER full_name;
ALTER TABLE users MODIFY COLUMN username VARCHAR(100) NOT NULL;
ALTER TABLE users DROP COLUMN avatar;
ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 0 AND age <= 150);
| Type | Bytes | Range |
|---|
| TINYINT | 1 | -128 to 127 (unsigned: 0-255) |
| SMALLINT | 2 | -32K to 32K |
| INT | 4 | -2B to 2B |
| BIGINT | 8 | -9.2Q to 9.2Q |
| FLOAT | 4 | ~7 decimal digits |
| DOUBLE | 8 | ~15 decimal digits |
| DECIMAL(M,D) | Varies | Exact precision (e.g. money) |
| BOOLEAN | 1 | TRUE (1) / FALSE (0) |
| Type | Max Length | Notes |
|---|
| CHAR(N) | 255 | Fixed-length, padded |
| VARCHAR(N) | 65,535 | Variable-length |
| TEXT | 65,535 | Variable, no default value |
| MEDIUMTEXT | 16M | Medium text |
| LONGTEXT | 4GB | Large text |
| BLOB | 65,535 | Binary data |
| JSON | 1GB | Validated JSON format |
| ENUM | 65,535 | Predefined list of values |
-- ── Create Indexes ──
CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_username ON users(username);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
-- ── Composite Index (order matters!) ──
-- Good for: WHERE user_id = ? AND status = ? ORDER BY created_at
CREATE INDEX idx_orders_composite ON orders(user_id, status, created_at);
-- ── Fulltext Index ──
ALTER TABLE articles ADD FULLTEXT INDEX ft_title_body (title, body);
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('database optimization' IN NATURAL LANGUAGE MODE);
-- ── Prefix Index (for long strings) ──
CREATE INDEX idx_url_prefix ON pages(url(100));
-- ── Partial / Functional Index (MySQL 8.0+) ──
CREATE INDEX idx_active_users ON users((is_active AND role = 'admin'));
-- ── Explain Query Plan ──
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at DESC;
-- ── Covering Index (all fields in index, no table lookup) ──
CREATE INDEX idx_covering ON orders(user_id, status, total);
SELECT user_id, status, total FROM orders WHERE user_id = 1;
-- "Using index" in EXPLAIN output means covering index is used
-- ── Managing Indexes ──
SHOW INDEX FROM users;
ALTER TABLE users DROP INDEX idx_users_email;
ANALYZE TABLE users; -- update index statistics
-- ── View Index Usage ──
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'myapp';
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'myapp';
-- ── Query Optimization Tips ──
-- 1. Avoid SELECT * — specify only needed columns
SELECT id, username, email FROM users WHERE role = 'admin';
-- 2. Use LIMIT for pagination
SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at DESC LIMIT 20 OFFSET 40;
-- Better: keyset pagination (avoids OFFSET scan)
SELECT * FROM orders
WHERE user_id = 1 AND created_at < '2024-06-01 00:00:00'
ORDER BY created_at DESC LIMIT 20;
-- 3. Use UNION ALL instead of UNION (no dedup overhead)
SELECT user_id FROM orders WHERE status = 'completed'
UNION ALL
SELECT user_id FROM returns;
-- 4. Batch inserts (much faster than single inserts)
INSERT INTO logs (user_id, action, created_at) VALUES
(1, 'login', NOW()), (2, 'login', NOW()), (3, 'signup', NOW());
-- 5. Avoid functions on indexed columns (breaks index usage)
-- BAD: WHERE YEAR(created_at) = 2024
-- GOOD: WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
-- 6. Use COUNT(*) for InnoDB (no full table scan needed)
SELECT COUNT(*) FROM users WHERE is_active = TRUE;
-- 7. Optimize LIKE queries
-- BAD: WHERE name LIKE '%alice%' (full scan)
-- GOOD: WHERE name LIKE 'alice%' (uses index)
| Column | Meaning |
|---|
| type | Access method (ALL=scan, index=using index, ref=lookup) |
| key | Index actually used |
| key_len | Bytes of index used |
| rows | Estimated rows examined |
| Extra | "Using index" = covering, "Using filesort" = slow |
| possible_keys | Indexes MySQL could use |
| Scenario | Index Type |
|---|
| Single column lookups | B-Tree (default) |
| Full-text search | FULLTEXT index |
| Spatial data | SPATIAL index |
| High cardinality hash | HASH (memory engine only) |
| JSON fields | Generated column + B-Tree |
| Multiple WHERE + ORDER BY | Composite B-Tree |
🚫Avoid SELECT * in production queries. It prevents covering indexes, increases network I/O, and locks more data. Always specify the columns you need. Use keyset pagination instead of OFFSET for large datasets.
-- ── Window Functions (MySQL 8.0+) ──
-- ROW_NUMBER: sequential number
SELECT
username, total,
ROW_NUMBER() OVER (ORDER BY total DESC) AS rank
FROM orders;
-- RANK / DENSE_RANK
SELECT
username, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;
-- RANK: 1,2,2,4 (gaps for ties)
-- DENSE_RANK: 1,2,2,3 (no gaps)
-- Running Total
SELECT
order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM daily_sales;
-- Moving Average
SELECT
order_date, revenue,
AVG(revenue) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7d
FROM daily_sales;
-- LAG / LEAD
SELECT
order_date, revenue,
LAG(revenue, 1) OVER (ORDER BY order_date) AS prev_day,
LEAD(revenue, 1) OVER (ORDER BY order_date) AS next_day,
revenue - LAG(revenue, 1) OVER (ORDER BY order_date) AS day_over_day
FROM daily_sales;
-- FIRST_VALUE / LAST_VALUE
SELECT
product, month, sales,
FIRST_VALUE(sales) OVER (PARTITION BY product ORDER BY month) AS first_month,
LAST_VALUE(sales) OVER (
PARTITION BY product ORDER BY month
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_month
FROM product_sales;
-- NTILE (percentile buckets)
SELECT
customer_id, total_spent,
NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile
FROM customer_totals;
-- ── Common Table Expressions (CTEs) ──
-- Basic CTE
WITH active_users AS (
SELECT id, username, email FROM users WHERE is_active = TRUE
)
SELECT * FROM active_users WHERE username LIKE 'a%';
-- Multiple CTEs
WITH
active_users AS (
SELECT id, username FROM users WHERE is_active = TRUE
),
user_orders AS (
SELECT user_id, COUNT(*) AS order_count, SUM(total) AS total_spent
FROM orders WHERE status = 'completed'
GROUP BY user_id
)
SELECT
u.username,
COALESCE(o.order_count, 0) AS orders,
COALESCE(o.total_spent, 0) AS spent
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id
ORDER BY spent DESC
LIMIT 20;
-- Recursive CTE (hierarchical data, e.g. org chart)
WITH RECURSIVE org_chart AS (
-- Anchor
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive
SELECT e.id, e.name, e.manager_id, oc.level + 1
FROM employees e
INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;
-- Recursive CTE (bill of materials)
WITH RECURSIVE bom AS (
SELECT id, name, parent_id, 1 AS depth
FROM parts WHERE id = 1
UNION ALL
SELECT p.id, p.name, p.parent_id, b.depth + 1
FROM parts p
INNER JOIN bom b ON p.parent_id = b.id
)
SELECT * FROM bom;
⚠️Use CTEs over subqueries for readability. CTEs can be referenced multiple times, making them DRY. Recursive CTEs are useful for tree/graph structures (org charts, categories, comments). Always include a termination condition in recursive CTEs to avoid infinite loops.
-- ── Stored Procedure ──
DELIMITER //
CREATE PROCEDURE transfer_funds(
IN p_from INT, IN p_to INT, IN p_amount DECIMAL(10,2)
)
BEGIN
DECLARE v_balance DECIMAL(10,2);
SELECT balance INTO v_balance FROM accounts WHERE id = p_from FOR UPDATE;
IF v_balance < p_amount THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
END IF;
UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
END //
DELIMITER ;
CALL transfer_funds(1, 2, 500.00);
-- ── Trigger ──
DELIMITER //
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE users SET order_count = order_count + 1 WHERE id = NEW.user_id;
INSERT INTO order_log (order_id, action, created_at) VALUES (NEW.id, 'created', NOW());
END //
DELIMITER ;
-- ── Event Scheduler (periodic task) ──
SET GLOBAL event_scheduler = ON;
DELIMITER //
CREATE EVENT cleanup_expired_sessions
ON SCHEDULE EVERY 1 HOUR
DO
DELETE FROM sessions WHERE expires_at < NOW();
END //
DELIMITER ;
-- ── JSON Functions (MySQL 5.7+) ──
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
attrs JSON
);
INSERT INTO products VALUES (1, 'Laptop', '{"color": "silver", "ram": "16GB", "storage": "512GB"}');
SELECT name, attrs->>"$.color" AS color, attrs->>"$.ram" AS ram FROM products;
SELECT name FROM products WHERE JSON_CONTAINS(attrs, '"16GB"', '$.ram');
SELECT JSON_KEYS(attrs) AS keys FROM products WHERE id = 1;
SELECT JSON_ARRAYAGG(name) AS names FROM products;
UPDATE products SET attrs = JSON_SET(attrs, '$.price', 999) WHERE id = 1;
⚠️Use stored programs sparingly. They are hard to version control, test, and debug. Prefer application-level logic for business rules. Triggers can cause unexpected side effects and are invisible to the application. Use them only for audit logging or data integrity that cannot be enforced by constraints.
Q: What is the difference between CHAR and VARCHAR?CHAR is fixed-length (padded with spaces), faster for very short constant-length data. VARCHAR is variable-length, saves space for varying-length strings. Use VARCHAR for most string fields (names, emails). Use CHAR for codes, flags, or truly fixed-length data (country codes, MD5 hashes).
Q: What is a covering index?A covering index contains all columns needed for a query, so MySQL can satisfy the query entirely from the index without reading the actual table rows. Seen as "Using index" in EXPLAIN Extra. Example: if you SELECT a, b from a table with INDEX(a, b), it is a covering query.
Q: Explain MySQL isolation levels.READ UNCOMMITTED: can read uncommitted data (dirty reads). READ COMMITTED: only committed data, but non-repeatable reads possible. REPEATABLE READ (default): snapshot read at transaction start, prevents non-repeatable reads. SERIALIZABLE: full isolation, serializes transactions. InnoDB prevents phantom reads even at REPEATABLE READ using next-key locking.
Q: What is the difference between InnoDB and MyISAM?InnoDB: supports transactions, foreign keys, row-level locking, crash recovery, ACID compliance. MyISAM: table-level locking, no transactions, faster for read-heavy workloads, full-text search (also available in InnoDB since 5.6). Always use InnoDB for production.
Q: How do you optimize a slow MySQL query?1) Run EXPLAIN ANALYZE to see the execution plan. 2) Check for missing indexes (type=ALL). 3) Verify the right index is used (key column). 4) Avoid functions on indexed columns. 5) Use covering indexes. 6) Avoid SELECT *. 7) Use keyset pagination instead of OFFSET. 8) Check innodb_buffer_pool_size.
Q: What is the difference between RANK and DENSE_RANK?RANK: assigns same rank to ties, then skips ranks (1, 2, 2, 4). DENSE_RANK: assigns same rank to ties, no skips (1, 2, 2, 3). ROW_NUMBER: always unique sequential numbers (1, 2, 3, 4), no consideration for ties.
Q: What is a deadlock?A deadlock occurs when two transactions hold locks that the other needs. Transaction A locks row 1 and wants row 2, while Transaction B locks row 2 and wants row 1. MySQL detects deadlocks and rolls back one transaction. Prevention: always access tables in the same order, keep transactions short, use proper indexing.
Q: What is replication and why use it?Replication copies data from a master to one or more slaves. Used for: read scalability (offload reads to slaves), high availability (failover to slave), backups (without impacting master). MySQL uses binary log (binlog) on master. Slave reads binlog and replays events. InnoDB Cluster provides automated group replication.
💡Top MySQL interview topics: ACID properties, isolation levels, InnoDB vs MyISAM, indexing (B-Tree, composite, covering), EXPLAIN optimization, window functions, CTEs, transactions & locking, replication, JSON support, and query optimization techniques.