MySQL.
Interview cheat sheet.

MySQL is a popular open-source relational database management system that uses structured query language (SQL). It supports ACID properties, multiple storage engines (InnoDB default), and features like transactions, indexes, views, stored procedures, and triggers. Key concepts include database normalization, query optimization, joins, aggregate functions, and proper indexing strategies. Essential for web applications, data warehousing, and any system requiring structured data storage with relational integrity.

Database vs Schema

• Database: Container for tables, views, procedures
• Schema: In MySQL, schema = database (used interchangeably)

ACID Properties

• Atomicity: All or nothing transactions
• Consistency: Data integrity maintained
• Isolation: Concurrent transactions don't interfere
• Durability: Committed data persists

Storage Engines

• InnoDB: Default, supports transactions, foreign keys
• MyISAM: Fast, no transactions, table-level locking
• Memory: Data in RAM, fastest, volatile

Numeric

INT         -- -2^31 to 2^31-1
BIGINT      -- -2^63 to 2^63-1
DECIMAL(M,D) -- Exact decimal, M digits, D decimals
FLOAT/DOUBLE -- Approximate decimal

String

VARCHAR(255)   -- Variable length, max 65,535
CHAR(10)       -- Fixed length, max 255
TEXT           -- Max 65,535 characters
MEDIUMTEXT     -- Max 16,777,215 characters
LONGTEXT       -- Max 4,294,967,295 characters

Date/Time

DATE           -- 'YYYY-MM-DD'
TIME           -- 'HH:MM:SS'
DATETIME       -- 'YYYY-MM-DD HH:MM:SS'
TIMESTAMP      -- Auto-updates, timezone aware
YEAR           -- Year in 2 or 4 digit format

JSON (MySQL 5.7+)

JSON           -- Native JSON data type

Database Operations

CREATE DATABASE dbname;
USE dbname;
DROP DATABASE dbname;
SHOW DATABASES;

Table Operations

-- Create table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Alter table
ALTER TABLE users ADD age INT;
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users MODIFY name VARCHAR(200);
ALTER TABLE users CHANGE name full_name VARCHAR(200);

-- Drop table
DROP TABLE users;
TRUNCATE TABLE users;  -- Delete all rows, reset AUTO_INCREMENT

Insert

INSERT INTO users (name, email) VALUES ('John', 'john@email.com');
INSERT INTO users VALUES (NULL, 'Jane', 'jane@email.com', NOW());
INSERT INTO users (name, email) 
VALUES ('User1', 'u1@email.com'), ('User2', 'u2@email.com');

Select

SELECT * FROM users;
SELECT name, email FROM users WHERE id > 10;
SELECT DISTINCT city FROM users;
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users LIMIT 10 OFFSET 20;

Update

UPDATE users SET name = 'John Doe' WHERE id = 1;
UPDATE users SET age = age + 1 WHERE birthday = CURDATE();

Delete

DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE created_at < '2023-01-01';

PRIMARY KEY    -- Unique, not null, only one per table
FOREIGN KEY    -- References another table
UNIQUE         -- All values must be different
NOT NULL       -- Cannot be empty
DEFAULT        -- Default value if not specified
CHECK          -- Validates data (MySQL 8.0+)
AUTO_INCREMENT -- Automatically increments

-- Example
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10,2) CHECK (amount > 0),
    status VARCHAR(20) DEFAULT 'pending',
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Inner Join

-- Only matching records
SELECT u.name, o.amount 
FROM users u 
INNER JOIN orders o ON u.id = o.user_id;

Left Join

-- All from left table
SELECT u.name, o.amount 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id;

Right Join

-- All from right table
SELECT u.name, o.amount 
FROM users u 
RIGHT JOIN orders o ON u.id = o.user_id;

Cross Join

-- Cartesian product
SELECT * FROM users CROSS JOIN products;

Self Join

-- Table joins itself
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

COUNT(*)      -- Count rows
COUNT(column) -- Count non-null values
SUM(column)   -- Sum of values
AVG(column)   -- Average
MAX(column)   -- Maximum value
MIN(column)   -- Minimum value

-- Group By
SELECT city, COUNT(*) as user_count 
FROM users 
GROUP BY city
HAVING COUNT(*) > 10;

-- With Rollup
SELECT city, status, COUNT(*) 
FROM orders 
GROUP BY city, status WITH ROLLUP;

In SELECT

SELECT name, 
       (SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_count
FROM users u;

In WHERE

SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

SELECT * FROM products 
WHERE price > (SELECT AVG(price) FROM products);

Correlated Subquery

SELECT * FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 
                WHERE e2.department = e1.department);

Types

-- B-Tree Index (default)
CREATE INDEX idx_name ON users(name);

-- Unique Index
CREATE UNIQUE INDEX idx_email ON users(email);

-- Composite Index
CREATE INDEX idx_name_city ON users(name, city);

-- Full-text Index
CREATE FULLTEXT INDEX idx_description ON products(description);

-- Show indexes
SHOW INDEX FROM users;

-- Drop index
DROP INDEX idx_name ON users;

Index Guidelines

• Index columns in WHERE, JOIN, ORDER BY
• Avoid indexing low-cardinality columns
• Consider composite indexes for multiple columns
• Too many indexes slow down INSERT/UPDATE

START TRANSACTION;
-- or BEGIN;

INSERT INTO accounts (name, balance) VALUES ('John', 1000);
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;  -- Save changes
-- or
ROLLBACK;  -- Undo changes

-- Isolation levels
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- Default
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Create view
CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE status = 'active';

-- Use view
SELECT * FROM active_users;

-- Update view
CREATE OR REPLACE VIEW active_users AS
SELECT id, name, email, created_at FROM users WHERE status = 'active';

-- Drop view
DROP VIEW active_users;

Stored Procedure

DELIMITER //
CREATE PROCEDURE GetUserOrders(IN user_id INT)
BEGIN
    SELECT * FROM orders WHERE user_id = user_id;
END //
DELIMITER ;

-- Call procedure
CALL GetUserOrders(1);

Function

DELIMITER //
CREATE FUNCTION CalculateTax(amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN amount * 0.1;
END //
DELIMITER ;

-- Use function
SELECT CalculateTax(100);

DELIMITER //
CREATE TRIGGER update_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END //
DELIMITER ;

Query Optimization

-- Use EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = 'john@email.com';

-- Avoid SELECT *
SELECT id, name FROM users;  -- Better than SELECT *

-- Use LIMIT
SELECT * FROM users LIMIT 10;

-- Avoid functions in WHERE
-- Bad: WHERE YEAR(created_at) = 2023
-- Good: WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31'

Index Optimization

-- Use covering index
CREATE INDEX idx_cover ON users(city, name, email);

-- Index hints
SELECT * FROM users USE INDEX (idx_email) WHERE email = 'test@email.com';

Query Cache

-- Check if enabled
SHOW VARIABLES LIKE 'query_cache_type';

-- Clear cache
RESET QUERY CACHE;

User Management

-- Create user
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

-- Grant privileges
GRANT SELECT, INSERT, UPDATE ON dbname.* TO 'username'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- Revoke privileges
REVOKE INSERT ON dbname.* FROM 'username'@'localhost';

-- Show grants
SHOW GRANTS FOR 'username'@'localhost';

-- Drop user
DROP USER 'username'@'localhost';

Best Practices

• Use prepared statements to prevent SQL injection
• Encrypt sensitive data
• Regular backups
• Principle of least privilege
• Strong passwords

Finding Duplicates

-- Find duplicate emails
SELECT email, COUNT(*) as count
FROM users 
GROUP BY email 
HAVING COUNT(*) > 1;

-- Delete duplicates keeping one
DELETE u1 FROM users u1
INNER JOIN users u2 
WHERE u1.id > u2.id AND u1.email = u2.email;

Ranking Queries

-- Nth highest salary (e.g., 2nd highest)
SELECT DISTINCT salary 
FROM employees 
ORDER BY salary DESC 
LIMIT 1 OFFSET 1;

-- Using dense rank (MySQL 8.0+)
SELECT salary FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rank
    FROM employees
) ranked WHERE rank = 2;

-- Get top N per group
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rn
    FROM employees
) t WHERE rn <= 3;

Date Operations

-- Current date/time functions
SELECT CURDATE();                    -- 2024-01-15
SELECT NOW();                        -- 2024-01-15 10:30:00
SELECT CURTIME();                    -- 10:30:00
SELECT UNIX_TIMESTAMP();             -- 1705315800

-- Date calculations
SELECT DATE_ADD(NOW(), INTERVAL 30 DAY);
SELECT DATEDIFF('2024-12-31', NOW());
SELECT TIMESTAMPDIFF(MONTH, '2024-01-01', NOW());

-- Extract date parts
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW());
SELECT DAYNAME(NOW()), MONTHNAME(NOW());

Pagination Patterns

-- Basic pagination
SELECT * FROM users 
ORDER BY id 
LIMIT 10 OFFSET 20;  -- Page 3, 10 per page

-- Optimized pagination (keyset)
SELECT * FROM users 
WHERE id > 1000  -- Last ID from previous page
ORDER BY id 
LIMIT 10;

Update Patterns

-- Update with JOIN
UPDATE users u
INNER JOIN (
    SELECT user_id, MAX(created_at) as last_order
    FROM orders 
    GROUP BY user_id
) o ON u.id = o.user_id
SET u.last_order_date = o.last_order;

-- Conditional update
UPDATE products 
SET price = CASE 
    WHEN category = 'electronics' THEN price * 1.1
    WHEN category = 'books' THEN price * 1.05
    ELSE price 
END;

DELETE vs TRUNCATE vs DROP

WHERE vs HAVING

UNION vs UNION ALL

Normal Forms Summary

Types of Keys

Hierarchical Data (Self-Referencing)

-- Employee hierarchy
WITH RECURSIVE emp_hierarchy AS (
    SELECT id, name, manager_id, 0 as level
    FROM employees 
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    INNER JOIN emp_hierarchy h ON e.manager_id = h.id
)
SELECT * FROM emp_hierarchy;

Running Totals

-- Cumulative sum
SELECT 
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) as running_total
FROM transactions;

Pivot Tables

-- Dynamic pivot
SELECT 
    product_id,
    SUM(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) as Jan,
    SUM(CASE WHEN month = 'Feb' THEN sales ELSE 0 END) as Feb,
    SUM(CASE WHEN month = 'Mar' THEN sales ELSE 0 END) as Mar
FROM monthly_sales
GROUP BY product_id;

Storage Engines Comparison

Replication Types

-- Master-Slave (Traditional)
CHANGE MASTER TO
    MASTER_HOST='master.example.com',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='password';
START SLAVE;

-- Check replication status
SHOW SLAVE STATUS\G

Partitioning Strategies

-- Range partitioning
CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

-- List partitioning
CREATE TABLE users (
    id INT,
    country VARCHAR(2)
) PARTITION BY LIST (country) (
    PARTITION pNA VALUES IN ('US', 'CA', 'MX'),
    PARTITION pEU VALUES IN ('FR', 'DE', 'UK'),
    PARTITION pASIA VALUES IN ('CN', 'JP', 'IN')
);

JSON Support (MySQL 5.7+)

-- JSON column
CREATE TABLE products (
    id INT PRIMARY KEY,
    attributes JSON
);

-- Insert JSON
INSERT INTO products VALUES 
(1, '{"color": "red", "size": "large", "price": 29.99}');

-- Query JSON
SELECT * FROM products 
WHERE JSON_EXTRACT(attributes, '$.color') = 'red';

-- Update JSON
UPDATE products 
SET attributes = JSON_SET(attributes, '$.price', 34.99)
WHERE id = 1;

Query Optimization Checklist

1. EXPLAIN every slow query
2. Check for missing indexes
3. Avoid SELECT * in production
4. Use covering indexes when possible
5. Denormalize for read-heavy workloads
6. Use prepared statements for repeated queries
7. Enable query cache for static data
8. Monitor slow query log

Configuration Tuning

-- Key variables to tune
innodb_buffer_pool_size = 70%_of_RAM
max_connections = 151
query_cache_size = 64M
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2  -- Performance vs durability

-- Check current settings
SHOW VARIABLES LIKE 'innodb%';

Index Strategy Best Practices

-- Analyze index usage
SELECT 
    table_name,
    index_name,
    cardinality
FROM information_schema.statistics
WHERE table_schema = 'your_db';

-- Find missing indexes (queries without index)
SELECT * FROM sys.statements_with_full_table_scans;

-- Remove duplicate indexes
SELECT * FROM sys.schema_redundant_indexes;

1. Data Types: Use appropriate sizes (TINYINT vs INT vs BIGINT)
2. Indexes: Index foreign keys and columns in WHERE/JOIN/ORDER BY
3. NULL Handling: Use IS NULL/IS NOT NULL, not = NULL
4. Subqueries: Prefer JOIN over correlated subqueries
5. Batch Operations: Use bulk inserts and multi-row updates
6. Connection Pooling: Reuse connections in applications
7. Monitoring: Enable slow query log and performance schema
8. Maintenance: Regular OPTIMIZE TABLE for fragmented tables
9. Backup Strategy: Use mysqldump or binary backups
10. Version Features: Leverage new features (CTEs in 8.0, JSON in 5.7)