Curated questions covering queries, joins, indexes, transactions, stored procedures, normalization, and database optimization techniques.
MySQL is an open-source relational database management system (RDBMS). Key features: ACID compliance, support for SQL standard, multiple storage engines (InnoDB, MyISAM), replication, partitioning, full-text search, JSON support, and wide adoption in web applications (LAMP stack).
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id; -- includes users with no ordersSELECT department, COUNT(*) as emp_count, AVG(salary) as avg_sal
FROM employees
WHERE salary > 30000 -- filter rows first
GROUP BY department
HAVING COUNT(*) > 5; -- filter groups afterDELETE FROM users WHERE id = 1; -- specific rows, rollback-able
TRUNCATE TABLE temp_logs; -- all rows, fast, no rollback
DROP TABLE old_table; -- removes table entirelyCREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(255) UNIQUE NOT NULL,
username VARCHAR(50) UNIQUE
);-- Primary key creates clustered index automatically
CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));
-- Non-clustered index
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_email_status ON users(email, status); -- compositeCREATE TABLE example (
country_code CHAR(2), -- always 2 bytes
description VARCHAR(500) -- variable length
);CREATE TABLE events (
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
scheduled_at DATETIME -- no timezone conversion
);SELECT name FROM customers
UNION
SELECT name FROM suppliers; -- removes duplicates
SELECT name FROM customers
UNION ALL
SELECT name FROM suppliers; -- keeps duplicates, fasterWindow functions perform calculations across a set of rows related to the current row without collapsing them into groups. Available since MySQL 8.0.
SELECT name, salary, department,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
SUM(salary) OVER (PARTITION BY department) as dept_total,
LAG(salary) OVER (ORDER BY salary) as prev_salary
FROM employees;SELECT name, score,
ROW_NUMBER() OVER (ORDER BY score DESC) as row_num,
RANK() OVER (ORDER BY score DESC) as rank_val,
DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
FROM students;A stored procedure is a precompiled set of SQL statements stored in the database. Benefits: reusability, reduced network traffic, better performance, and centralized business logic.
DELIMITER //
CREATE PROCEDURE GetUserById(IN userId INT)
BEGIN
SELECT * FROM users WHERE id = userId;
END //
DELIMITER ;
CALL GetUserById(1);-- Function
CREATE FUNCTION GetFullName(first VARCHAR(50), last VARCHAR(50))
RETURNS VARCHAR(100)
DETERMINISTIC
RETURN CONCAT(first, " ", last);
SELECT GetFullName(first_name, last_name) FROM users;A trigger automatically executes in response to INSERT, UPDATE, or DELETE events on a table. Types: BEFORE and AFTER.
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
INSERT INTO audit_log(user_id, old_email, new_email, changed_at)
VALUES (OLD.id, OLD.email, NEW.email, NOW());
END;CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE status = "active";
SELECT * FROM active_users; -- use like a tableEXPLAIN SELECT * FROM users WHERE email = "alice@example.com";
-- Check: type (ALL=bad, ref/eq_ref=good), key (index used), rows (estimated)-- JOIN (usually faster)
SELECT u.name FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- Subquery equivalent
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders);-- Non-correlated (executes once)
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- Correlated (executes per row - slow)
SELECT * FROM users u
WHERE (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) > 5;SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;-- Pessimistic
START TRANSACTION;
SELECT * FROM products WHERE id = 1 FOR UPDATE;
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;
-- Optimistic
UPDATE products SET stock = stock-1, version = version+1
WHERE id = 1 AND version = 5; -- fails if version changedSELECT DISTINCT department FROM employees; -- unique departments
SELECT department, COUNT(*), AVG(salary)
FROM employees
GROUP BY department; -- aggregation per groupSELECT
COUNT(*) as total_rows,
COUNT(email) as rows_with_email,
COUNT(DISTINCT email) as unique_emails
FROM users;SELECT IFNULL(phone, "N/A") FROM users;
SELECT COALESCE(phone, mobile, "N/A") FROM users;
SELECT total / NULLIF(count, 0) FROM stats; -- avoid division by zeroSELECT * FROM users WHERE name LIKE "Al%"; -- starts with Al
SELECT * FROM users WHERE email REGEXP "^[a-z]+@gmail\.com$";STRAIGHT_JOIN forces MySQL to join tables in the order they appear in the query, overriding the optimizer. Use only when you know the optimizer is choosing a suboptimal join order.
-- Force users to be the driving table
SELECT STRAIGHT_JOIN u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;SET NAMES utf8mb4;
SELECT LENGTH("hello"); -- 5 bytes
SELECT CHAR_LENGTH("hello"); -- 5 chars
-- For emoji (4 bytes in UTF-8):
SELECT LENGTH("hi"); -- 6 bytes
SELECT CHAR_LENGTH("hi"); -- 3 charsSELECT CONCAT("Hello", " ", "World"); -- "Hello World"
SELECT CONCAT("Hello", NULL, "World"); -- NULL
SELECT CONCAT_WS(", ", "Alice", NULL, "NYC"); -- "Alice, NYC"SELECT DATE_FORMAT(NOW(), "%Y-%m-%d %H:%i:%s"); -- "2026-04-22 10:30:00"
SELECT STR_TO_DATE("22/04/2026", "%d/%m/%Y"); -- 2026-04-22SELECT DATEDIFF("2026-12-31", "2026-01-01"); -- 364 days
SELECT TIMESTAMPDIFF(MONTH, "2026-01-01", "2026-12-31"); -- 11 months-- Composite index: efficient for WHERE a=? AND b=?
CREATE INDEX idx_name_dept ON employees(last_name, department);
-- Leftmost prefix rule: can use for:
-- WHERE last_name = ?
-- WHERE last_name = ? AND department = ?
-- Cannot use for: WHERE department = ? aloneSTART TRANSACTION;
INSERT INTO orders VALUES (1, 100);
SAVEPOINT after_order;
INSERT INTO payments VALUES (1, 100);
ROLLBACK TO SAVEPOINT after_order; -- undo payment only
COMMIT; -- commits the orderCREATE TABLE shirts (
size ENUM("XS","S","M","L","XL"),
colors SET("red","green","blue","black")
);
INSERT INTO shirts VALUES ("M", "red,blue");SELECT * FROM users USE INDEX (idx_email) WHERE email = "a@b.com";
SELECT * FROM users FORCE INDEX (idx_email) WHERE email = "a@b.com";SHOW PROCESSLIST; -- active connections and queries
SHOW FULL PROCESSLIST; -- includes full query text
SELECT table_name, table_rows, data_length
FROM information_schema.tables
WHERE table_schema = "mydb";-- Create full-text index
CREATE FULLTEXT INDEX ft_idx ON articles(title, body);
-- Natural language search
SELECT *, MATCH(title,body) AGAINST("mysql performance") AS score
FROM articles
WHERE MATCH(title,body) AGAINST("mysql performance")
ORDER BY score DESC;CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);-- GROUP BY: one row per department
SELECT department, AVG(salary) FROM employees GROUP BY department;
-- PARTITION BY: all rows preserved with department average
SELECT name, salary, department,
AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;CREATE TEMPORARY TABLE temp_results AS
SELECT user_id, SUM(amount) as total
FROM orders
GROUP BY user_id;
SELECT u.name, t.total
FROM users u JOIN temp_results t ON u.id = t.user_id;SELECT * FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);SELECT email FROM customers
UNION
SELECT email FROM subscribers;
SELECT email FROM customers
UNION ALL
SELECT email FROM subscribers;SELECT name, score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
RANK() OVER (ORDER BY score DESC) AS rank_num,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_num
FROM students;CREATE TABLE orders (
price DECIMAL(10,2),
quantity INT,
total DECIMAL(10,2) GENERATED ALWAYS AS (price * quantity) STORED
);An invisible index exists but is ignored by the optimizer unless optimizer settings allow it. It helps test whether an index can be removed without dropping it immediately.
ALTER TABLE users ALTER INDEX idx_email INVISIBLE;
EXPLAIN SELECT * FROM users WHERE email = "a@b.com";
ALTER TABLE users ALTER INDEX idx_email VISIBLE;SELECT JSON_EXTRACT(profile, "$.city") AS city
FROM users
WHERE JSON_EXTRACT(profile, "$.verified") = true;Explore 500+ free tutorials across 20+ languages and frameworks.