Top 77 MySQL / SQL Interview Questions & Answers (2026)

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. It was developed in the 1970s by IBM and standardized by ANSI/ISO. SQL is used to: create and modify database structures (DDL), insert, update, delete, and query data (DML), control access to data (DCL), and manage transactions (TCL). SQL is declarative — you describe what data you want, not how to get it; the database engine determines the optimal execution plan. Key categories: DDL (Data Definition Language) — CREATE, ALTER, DROP, TRUNCATE; DML (Data Manipulation Language) — SELECT, INSERT, UPDATE, DELETE; DCL (Data Control Language) — GRANT, REVOKE; TCL (Transaction Control Language) — COMMIT, ROLLBACK, SAVEPOINT. All major relational databases (MySQL, PostgreSQL, Oracle, SQL Server, SQLite) use SQL with slight syntax variations. MySQL is a popular open-source RDBMS (Relational Database Management System) that is widely used for web applications.

A relational database organizes data into tables (also called relations) consisting of rows (records/tuples) and columns (fields/attributes). Tables are related to each other through keys — a primary key uniquely identifies each row, and a foreign key in one table references the primary key of another, establishing relationships. This model was proposed by E.F. Codd in 1970. Key principles: data is stored in normalized tables to eliminate redundancy; relationships are defined through keys; data integrity is enforced through constraints; SQL is used to query and manipulate data; ACID properties (Atomicity, Consistency, Isolation, Durability) ensure reliable transactions. Popular relational databases: MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, SQLite, MariaDB. Contrast with NoSQL databases (MongoDB, Cassandra, Redis) which store data in documents, key-value pairs, or other non-tabular formats and sacrifice some consistency for flexibility and scale.

A database is a container that organizes and stores a collection of related data. It can hold multiple tables, views, stored procedures, indexes, and other database objects. A database represents an entire application's data (e.g., an e-commerce database containing everything for the store). A table is a specific structure within a database that organizes data about a single entity or concept into rows and columns. For example, an e-commerce database might contain tables named users, products, orders, and order_items. Each table has a defined schema — the column names and their data types. Each row in a table is one record (one user, one product). Each column represents one attribute (user's email, product's price). Relationship: one database contains many tables; one table contains many rows and a fixed set of columns. In MySQL: CREATE DATABASE shop;, USE shop;, CREATE TABLE users (...);.

A primary key is a column (or combination of columns) that uniquely identifies each row in a table. Every table should have a primary key. Properties of a primary key: (1) Unique — no two rows can have the same primary key value; (2) Not NULL — a primary key column cannot contain NULL; (3) Immutable (best practice) — primary key values should not change once set. Common types: (1) Auto-increment integer: id INT AUTO_INCREMENT PRIMARY KEY — MySQL assigns the next integer automatically on each insert; (2) UUID/GUID: globally unique identifier — good for distributed systems but larger storage and slower index performance than integers; (3) Natural key: a real-world attribute that is naturally unique (email, SSN) — but natural keys can change or have format issues, so surrogate (synthetic) keys are usually preferred. Composite primary key: when no single column is unique, combine two or more columns: PRIMARY KEY (order_id, product_id) — common in junction tables.

A foreign key is a column in one table that references the primary key of another table, establishing a relationship between the two tables and enforcing referential integrity. Example: an orders table has a user_id column that is a foreign key referencing users.id — every order must belong to a valid user. Declaration: user_id INT, FOREIGN KEY (user_id) REFERENCES users(id). Referential integrity rules control what happens when the referenced row is updated or deleted — configured with ON DELETE and ON UPDATE actions: CASCADE — automatically delete/update child rows when parent is deleted/updated; SET NULL — set the foreign key to NULL; RESTRICT — prevent deletion of parent if child rows exist (default); SET DEFAULT — set to default value. Foreign keys enable JOINs between tables and prevent orphan records (orders without a valid user). In MySQL, foreign key constraints require both tables to use the InnoDB storage engine.

The SELECT statement is the most used SQL command — it retrieves data from one or more tables. Basic syntax: SELECT column1, column2 FROM table_name WHERE condition ORDER BY column ASC/DESC LIMIT n;. Key clauses: SELECT * — retrieves all columns (avoid in production — specify columns explicitly for clarity and performance); FROM — specifies the table(s); WHERE — filters rows (uses comparison operators: =, !=, <, >, BETWEEN, IN, LIKE, IS NULL); ORDER BY — sorts results (ASC default, DESC for descending); LIMIT — restricts number of rows returned; OFFSET — skips rows (for pagination: LIMIT 10 OFFSET 20); DISTINCT — removes duplicate rows: SELECT DISTINCT city FROM users; Aliases: SELECT first_name AS name — rename columns in output. Execution order (not syntax order): FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

The WHERE clause filters rows returned by SELECT, or affected by UPDATE/DELETE, based on a condition. Only rows where the condition evaluates to TRUE are included. Examples: WHERE age > 18, WHERE status = "active", WHERE email IS NOT NULL. Operators: Comparison: =, !=, <>, <, >, <=, >= ; Range: BETWEEN 18 AND 65 (inclusive); List: IN ("active", "pending"), NOT IN (...); Pattern matching: LIKE "John%" (% = any characters, _ = one character), NOT LIKE; NULL check: IS NULL, IS NOT NULL (never use = NULL); Logical: AND, OR, NOT. Combining conditions: WHERE age > 18 AND (status = "active" OR role = "admin"). Important: WHERE is evaluated BEFORE GROUP BY and aggregate functions — use HAVING to filter on aggregated values.

MySQL data types determine what kind of data can be stored in a column. Main categories: Numeric: INT (4 bytes, -2B to 2B), TINYINT (1 byte, 0-255 unsigned), BIGINT (8 bytes), DECIMAL(p,s)/NUMERIC (exact decimal, ideal for money — e.g., DECIMAL(10,2) for currency), FLOAT/DOUBLE (approximate floating point — avoid for monetary values). String: VARCHAR(n) (variable-length string up to n chars, most common), CHAR(n) (fixed-length — pads with spaces, faster for fixed-width data like country codes), TEXT/MEDIUMTEXT/LONGTEXT (large text — up to 4GB), ENUM("val1", "val2") (restricted set of values). Date/Time: DATE (YYYY-MM-DD), DATETIME (YYYY-MM-DD HH:MM:SS), TIMESTAMP (like DATETIME but stored as UTC, auto-updates supported), TIME, YEAR. Binary: BLOB, BINARY, VARBINARY. Boolean: MySQL uses TINYINT(1) — 0 = false, 1 = true. JSON: native JSON type in MySQL 5.7+ for semi-structured data.

INSERT adds new rows to a table: INSERT INTO users (name, email, age) VALUES ("Alice", "alice@example.com", 30); — specify columns and matching values. Insert multiple rows: INSERT INTO users (name, email) VALUES ("Bob", "b@b.com"), ("Carol", "c@c.com");. UPDATE modifies existing rows: UPDATE users SET status = "inactive", updated_at = NOW() WHERE id = 5;. Always include a WHERE clause with UPDATE — omitting it updates ALL rows in the table. Verify with SELECT first: SELECT * FROM users WHERE id = 5; then UPDATE the same condition. DELETE removes rows: DELETE FROM users WHERE id = 5;. Again, always include WHERE — DELETE FROM users; with no WHERE removes every row. For removing all rows while resetting auto-increment, use TRUNCATE instead. Safe practice: wrap destructive operations in a transaction so you can ROLLBACK if something goes wrong. Use LIMIT in UPDATE/DELETE to cap affected rows as a safety measure.

DELETE removes rows one by one, fires triggers, can have a WHERE clause to delete specific rows, is logged row-by-row (fully recoverable in a transaction), and does NOT reset the auto-increment counter. DELETE FROM users WHERE inactive = 1; — can delete selectively. TRUNCATE removes ALL rows at once, is faster (uses bulk deallocation of data pages), does NOT fire row-level triggers, cannot have a WHERE clause, DOES reset the auto-increment counter to 1, and in MySQL cannot be rolled back (it is implicitly committed — though this depends on the storage engine). TRUNCATE TABLE users; — clears the entire table instantly. DROP is different from both — it removes the entire table structure and data: DROP TABLE users;. Use cases: DELETE for removing specific rows or few rows; TRUNCATE for clearing an entire table quickly (e.g., clearing a staging/temp table); DROP for removing the table entirely. In InnoDB, TRUNCATE actually drops and recreates the table internally.

Aggregate functions perform a calculation on a set of rows and return a single value. They are used with SELECT and often with GROUP BY. Key functions: COUNT() — counts rows: COUNT(*) counts all rows including NULLs; COUNT(column) counts non-NULL values in that column; COUNT(DISTINCT column) counts unique non-NULL values; SUM(column) — adds up numeric values (ignores NULLs); AVG(column) — calculates average (ignores NULLs); MAX(column) / MIN(column) — largest/smallest value; GROUP_CONCAT(column) — concatenates values from multiple rows into a single string. Example: SELECT department, COUNT(*) AS employees, AVG(salary) AS avg_salary, MAX(salary) AS highest FROM employees GROUP BY department HAVING COUNT(*) > 5 ORDER BY avg_salary DESC;. Aggregate functions ignore NULL values (except COUNT(*)). They cannot be used in WHERE — use HAVING for filtering aggregated results.

GROUP BY groups rows that have the same values in specified columns into summary rows, typically used with aggregate functions. Each unique combination of GROUP BY column values produces one output row. Example: SELECT category, COUNT(*) AS total, AVG(price) AS avg_price FROM products GROUP BY category; — produces one row per category with the count and average price of products in each. Rules: (1) Every column in SELECT that is not an aggregate function must appear in GROUP BY; (2) NULL values are grouped together; (3) GROUP BY is evaluated after WHERE but before HAVING and SELECT. HAVING filters grouped results (like WHERE but for aggregates): GROUP BY category HAVING COUNT(*) > 10. Difference between WHERE and HAVING: WHERE filters individual rows before grouping; HAVING filters groups after aggregation. Multi-column grouping: GROUP BY year, month — groups by each unique year-month combination. MySQL 5.7+ with ONLY_FULL_GROUP_BY mode (default) enforces the rule that non-aggregated columns must be in GROUP BY.

ORDER BY sorts the query result set by one or more columns. Syntax: ORDER BY column1 ASC, column2 DESC. ASC (ascending, default) — smallest to largest, A to Z, oldest to newest. DESC (descending) — largest to smallest, Z to A, newest to oldest. Sorting by multiple columns: ORDER BY last_name ASC, first_name ASC — sorts by last name first, then by first name within the same last name. Sort by position: ORDER BY 2 DESC — sorts by the second column in the SELECT list (works but fragile — column order can change). Sort by expression: ORDER BY LENGTH(name), ORDER BY price * quantity DESC. Sort by alias: SELECT price * 1.1 AS total ORDER BY total. Handling NULLs: MySQL sorts NULL values as lowest value (ASC puts NULLs first, DESC puts them last). ORDER BY RAND() — randomly shuffles results (slow on large tables — loads all rows, shuffles, then returns). ORDER BY is processed after SELECT, so you can order by computed columns or aliases.

LIMIT restricts the number of rows returned by a query. OFFSET skips a specified number of rows before returning results. Together they enable pagination. Syntax: SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20; — returns rows 21-30. Alternative shorthand: LIMIT 20, 10 — skip 20, take 10 (confusingly reversed — first is offset, second is count). Pagination formula: for page N with page_size rows: OFFSET = (N - 1) * page_size. Example: page 3, 10 per page: LIMIT 10 OFFSET 20. Performance concern: OFFSET-based pagination is slow on large tables because MySQL must read and discard all offset rows before returning results — scanning 10,000 rows to return 10 is wasteful. For large datasets, use cursor-based pagination (keyset pagination): instead of OFFSET, use a WHERE clause based on the last seen ID: WHERE id > last_seen_id ORDER BY id LIMIT 10 — much faster because it uses the index.

SQL constraints enforce rules on data in tables, ensuring data integrity and accuracy. Types: (1) NOT NULL — column cannot contain NULL: name VARCHAR(100) NOT NULL; (2) UNIQUE — all values in a column must be distinct (NULLs are allowed — MySQL allows multiple NULLs in a unique column): email VARCHAR(255) UNIQUE; (3) PRIMARY KEY — combination of NOT NULL + UNIQUE, uniquely identifies each row; (4) FOREIGN KEY — references a primary key in another table, enforces referential integrity; (5) CHECK — validates values meet a condition (fully supported in MySQL 8.0+): CHECK (age >= 18); (6) DEFAULT — provides a default value when none is specified: status VARCHAR(20) DEFAULT "active"; (7) AUTO_INCREMENT — automatically generates incrementing integer values (MySQL-specific, not a standard constraint but commonly discussed with them). Constraints can be defined at column level (inline) or table level (after all columns, named constraints). Use CONSTRAINT constraint_name to name constraints for clear error messages.

NULL in SQL represents missing, unknown, or inapplicable data — it is NOT zero, empty string, or false. NULL is a special marker meaning "no value." Three-valued logic: any comparison involving NULL produces UNKNOWN (not TRUE or FALSE). This means WHERE column = NULL never returns rows — you must use IS NULL or IS NOT NULL. Arithmetic with NULL always returns NULL: 5 + NULL = NULL, NULL * 0 = NULL. Handling NULLs: IFNULL(expr, fallback) — returns fallback if expr is NULL: IFNULL(phone, "N/A"); COALESCE(a, b, c, ...) — returns the first non-NULL value from the list (standard SQL, more flexible than IFNULL); NULLIF(a, b) — returns NULL if a equals b, otherwise returns a (useful to prevent division by zero: total / NULLIF(count, 0)). Aggregate functions (SUM, AVG, MAX, MIN, COUNT(column)) ignore NULLs. COUNT(*) counts all rows including NULLs; COUNT(column) counts only non-NULL values in that column. Design: only allow NULL when truly meaning "unknown" — prefer NOT NULL with default values where possible.

CHAR(n) is a fixed-length character data type. It always uses exactly n bytes of storage, padding with spaces if the stored value is shorter. Maximum length: 255 characters. VARCHAR(n) is a variable-length character data type. It uses only as much space as needed plus 1-2 bytes for the length prefix (1 byte if ≤255 chars, 2 bytes if ≤65535). Maximum length: 65,535 bytes. When to use each: CHAR — best for fixed-width strings where all values are the same length: country codes (CHAR(2)), phone numbers with consistent formatting (CHAR(10)), MD5 hashes (CHAR(32)), UUIDs (CHAR(36)). CHAR can be slightly faster for retrieval because row offsets are predictable. VARCHAR — best for strings that vary widely in length: names, addresses, email addresses, descriptions. VARCHAR saves storage when values vary. Key point: trailing spaces are removed from VARCHAR on storage and comparison; CHAR pads and compares with trailing spaces. In modern MySQL with row-format compression, the performance difference is minimal.

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. Two wildcard characters: % — represents zero or more characters; _ — represents exactly one character. Examples: WHERE name LIKE "Jo%" — matches "John", "Joe", "Jonathan" (starts with Jo); WHERE email LIKE "%@gmail.com" — matches any Gmail address (ends with @gmail.com); WHERE name LIKE "%son%" — matches "Johnson", "Bronson", "Jason" (contains "son" anywhere); WHERE code LIKE "A_3" — matches "A13", "AB3", "AX3" (A, then any one char, then 3). Case sensitivity: in MySQL, LIKE is case-insensitive for string columns with a case-insensitive collation (default utf8mb4_general_ci). Use LIKE BINARY for case-sensitive matching. Performance: LIKE with a leading wildcard ("%pattern") cannot use a B-tree index — it causes a full table scan. LIKE with a trailing wildcard only ("pattern%") CAN use an index. For full-text search, use MySQL FULLTEXT indexes and MATCH...AGAINST instead of LIKE.

An alias is a temporary name assigned to a table or column for the duration of a query, improving readability and allowing shorter references. Column aliases: SELECT first_name AS name, price * quantity AS total_cost FROM orders; — the AS keyword is optional (first_name name also works, but AS improves clarity). Column aliases are available in ORDER BY and HAVING but NOT in WHERE (because WHERE is evaluated before SELECT). Table aliases: essential when joining tables to avoid ambiguous column names and shorten long table names: SELECT u.name, o.total FROM users AS u JOIN orders AS o ON u.id = o.user_id;. Table aliases are used throughout the query after they are defined. Required aliases: subqueries used in FROM must always have an alias: SELECT * FROM (SELECT id, name FROM users) AS active_users;. In self-joins (joining a table to itself), aliases are mandatory to distinguish the two instances: FROM employees AS manager JOIN employees AS report ON manager.id = report.manager_id.

A JOIN combines rows from two or more tables based on a related column. Types: INNER JOIN — returns only rows where the join condition matches in BOTH tables. Rows without a match are excluded. SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id; — only users who have orders, only orders with a valid user. LEFT JOIN (LEFT OUTER JOIN) — returns ALL rows from the left table plus matching rows from the right. If no match, right table columns are NULL. SELECT u.name, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_id; — all users, including those with no orders (their order columns show NULL). RIGHT JOIN — mirror of LEFT JOIN (all rows from right table). FULL OUTER JOIN — all rows from both tables (MySQL doesn't support this natively — simulate with UNION of LEFT and RIGHT JOIN). CROSS JOIN — Cartesian product — every row from table A paired with every row from table B (n×m rows). SELF JOIN — joining a table with itself using aliases.

INNER JOIN returns only the rows where there is a match in BOTH tables. Non-matching rows from either table are excluded from the result. Use INNER JOIN when you only want records that have corresponding entries in both tables. Example: customers who have placed at least one order. LEFT JOIN (LEFT OUTER JOIN) returns ALL rows from the left (first) table, plus the matching rows from the right table. If there is no match in the right table, the right table columns are filled with NULL. Use LEFT JOIN when you want all records from the left table regardless of whether they have matches. Example: all customers, showing their orders if they have any (NULLs for customers with no orders). Practical difference: SELECT c.name, o.id FROM customers c INNER JOIN orders o ON c.id = o.customer_id — shows only customers with orders. SELECT c.name, o.id FROM customers c LEFT JOIN orders o ON c.id = o.customer_id — shows all customers; those without orders have NULL for o.id. To find customers with NO orders: LEFT JOIN ... WHERE o.id IS NULL.

A subquery (or inner query / nested query) is a query embedded inside another SQL query. The inner query executes first and its result is used by the outer query. Types by location: (1) In WHERE: SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products); — products priced above average; (2) In FROM (derived table): SELECT dept, avg_sal FROM (SELECT department AS dept, AVG(salary) AS avg_sal FROM employees GROUP BY department) AS dept_stats WHERE avg_sal > 50000;; (3) In SELECT (scalar subquery): SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count FROM users;. Types by return value: Scalar — returns one value; Row — returns one row; Table — returns multiple rows/columns. Correlated subquery: references a column from the outer query — re-executes for each outer row (can be slow). Operators with subqueries: IN, NOT IN, EXISTS, NOT EXISTS, ANY, ALL. Often JOINs can replace subqueries with better performance.

UNION combines the result sets of two or more SELECT statements into a single result set. Rules: (1) Each SELECT must have the same number of columns; (2) Corresponding columns must have compatible data types; (3) Column names in the result come from the first SELECT. UNION automatically removes duplicate rows (like DISTINCT). UNION ALL keeps all rows including duplicates — faster because it skips the deduplication step. Use UNION ALL when you know there are no duplicates or don't need them removed. Example: SELECT id, name, "customer" AS type FROM customers UNION ALL SELECT id, name, "supplier" AS type FROM suppliers ORDER BY name; — note ORDER BY applies to the combined result. UNION vs JOIN: UNION stacks results vertically (more rows, same columns); JOIN combines results horizontally (same rows, more columns). Common use cases: combining similar data from multiple tables, combining historical and current data, implementing "find all X or Y" queries where a single table doesn't contain all the data.

Both WHERE and HAVING filter rows, but they operate at different stages: WHERE filters individual rows before any grouping or aggregation. It cannot reference aggregate functions. Example: WHERE salary > 50000 — filter employees with salary over 50k before grouping. HAVING filters groups after GROUP BY and aggregation. It can (and typically does) reference aggregate functions. Example: HAVING AVG(salary) > 50000 — filter departments where the average salary is over 50k. Both together: SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_sal FROM employees WHERE hire_date > "2020-01-01" GROUP BY department HAVING COUNT(*) > 5 ORDER BY avg_sal DESC; — WHERE filters employees hired after 2020 (before grouping), GROUP BY groups by department, HAVING keeps only departments with more than 5 such employees. Order of execution: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Performance tip: use WHERE (not HAVING) to filter non-aggregated conditions, as it reduces the number of rows before grouping.

Normalization is the process of organizing a relational database to reduce data redundancy and improve data integrity by decomposing tables into smaller, well-structured ones. It follows a series of Normal Forms (NF): 1NF (First Normal Form): each column contains atomic (indivisible) values; no repeating groups; each row is unique (has a primary key). No comma-separated lists or arrays in cells. 2NF: in 1NF AND every non-key attribute is fully functionally dependent on the entire primary key (no partial dependencies — relevant when the PK is composite). 3NF: in 2NF AND no transitive dependencies — non-key attributes must depend only on the primary key, not on other non-key attributes. Example violation: if a table stores employee_id, department_id, department_name — department_name depends on department_id (non-key), not directly on employee_id. Fix: move department_name to a separate departments table. BCNF (Boyce-Codd NF): stricter version of 3NF. Beyond 3NF: 4NF, 5NF exist but are rarely applied in practice. Normalization trades write efficiency for storage efficiency; denormalization trades redundancy for read performance.

Denormalization is the intentional introduction of redundancy into a database design by combining tables or duplicating data to improve read performance at the cost of storage and update complexity. It is the deliberate reversal of normalization for performance reasons. When to denormalize: when normalized queries require many JOINs that are too slow for the required throughput (read-heavy workloads); when reporting or analytics queries need to aggregate data frequently; when the data is rarely updated (changes to denormalized data must update all copies). Examples: (1) Storing a user's order count in the users table (instead of calculating with COUNT every time); (2) Storing product name in order_items even though products already has it (product name can change, but historical order data should keep the name at time of purchase); (3) Pre-computed summary/reporting tables updated on a schedule. Trade-offs: faster reads (fewer JOINs), slower writes (must update all redundant copies), risk of inconsistency. Denormalization is common in OLAP (analytics) databases, data warehouses, and high-traffic web applications. It should be a deliberate, documented decision made after profiling, not a habit.

An index is a database structure (separate from the table data) that allows the database engine to find rows quickly without scanning the entire table. Analogous to a book's index — instead of reading every page to find a topic, you check the index for the page number. Without an index, MySQL performs a full table scan (O(n)). With a B-tree index, lookups are O(log n). Creating an index: CREATE INDEX idx_email ON users (email);. MySQL automatically creates an index on PRIMARY KEY and UNIQUE columns. Indexes speed up: SELECT WHERE clauses, JOIN conditions, ORDER BY and GROUP BY (can avoid sort), and covering queries (all needed columns are in the index). Indexes slow down: INSERT, UPDATE, DELETE (the index must be maintained). Storage cost: indexes take additional disk space. Types in MySQL: B-tree (default, most common), FULLTEXT (for text search), SPATIAL (for geometry data), HASH (only in Memory engine). Choosing which columns to index: frequently queried columns, JOIN columns, columns in ORDER BY, columns with high cardinality (many unique values — indexing gender with M/F is wasteful, indexing email is beneficial).

A clustered index determines the physical order in which data is stored on disk. The table data IS the index — the leaf nodes of the B-tree contain the actual row data. There can be only one clustered index per table (because data can only be sorted in one way). In MySQL InnoDB, the primary key is always the clustered index. If you define no primary key, InnoDB picks the first UNIQUE NOT NULL column; if none exists, it creates a hidden 6-byte row ID. Non-clustered index (secondary index) is a separate structure from the table data. Leaf nodes contain the indexed column values plus a pointer to the actual row data. In InnoDB, that pointer is the primary key value (not a physical row pointer) — so a secondary index lookup requires two lookups: first find the primary key in the secondary index, then look up the row data in the clustered (primary key) index. This is called a "double lookup" or "bookmark lookup." There can be many non-clustered indexes per table. MySQL terminology doesn't use "clustered/non-clustered" explicitly — InnoDB always uses the primary key as the clustered index, and all other indexes are secondary (non-clustered).

A VIEW is a named, stored SQL query that appears as a virtual table — it has no physical data of its own but presents data from one or more underlying tables. Think of it as a saved query with a name. Creating a view: CREATE VIEW active_users AS SELECT id, name, email FROM users WHERE status = "active";. Query it like a table: SELECT * FROM active_users WHERE name LIKE "John%";. Benefits: (1) Simplify complex queries: hide JOINs and filters behind a simple name; (2) Security: expose only specific columns/rows to users, hiding sensitive data (e.g., expose user names but not passwords); (3) Abstraction: decouple application code from table structure — rename tables without changing application queries; (4) Reusability: define once, use in many queries. Limitations: views are generally not faster than the underlying query (MySQL executes the view's SELECT each time — no query result caching). Updatable views: simple views (single table, no DISTINCT/GROUP BY/aggregates) can have INSERT/UPDATE/DELETE. MySQL supports materialized views indirectly via scheduled refresh to a real table.

A stored procedure is a named set of SQL statements stored in the database and executed on the server side. It can contain SQL queries, flow control (IF/ELSE, WHILE, LOOP), variables, and error handling. Creating a procedure: DELIMITER // CREATE PROCEDURE GetUserOrders(IN userId INT) BEGIN SELECT * FROM orders WHERE user_id = userId; END // DELIMITER ;. Call it: CALL GetUserOrders(5);. Benefits: (1) Performance — parsed and compiled once, cached execution plan; (2) Reduced network traffic — one call executes multiple statements server-side; (3) Security — grant EXECUTE permission on the procedure without granting table access; (4) Reusability — encapsulate complex business logic once. Parameter types: IN (input — passed by value), OUT (output — returns a value to caller), INOUT (both). Limitations: harder to version control than application code, database-specific syntax (not portable), debugging is more complex. Modern applications often prefer to handle business logic in the application layer (Node.js, Laravel) and use the database purely for storage.

A trigger is a database object that automatically executes a specified SQL statement in response to certain events on a table: INSERT, UPDATE, or DELETE. Triggers fire either BEFORE or AFTER the event, giving six combinations: BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, AFTER DELETE. Inside a trigger, NEW refers to the new row data (available in INSERT and UPDATE), and OLD refers to the old row data (available in UPDATE and DELETE). Example — automatically set updated_at on update: CREATE TRIGGER before_user_update BEFORE UPDATE ON users FOR EACH ROW SET NEW.updated_at = NOW();. Use cases: audit logging (log changes to a separate audit table), enforcing business rules (prevent a salary from exceeding a limit), maintaining denormalized counts (update a counter table when a row is inserted). Limitations: triggers execute invisibly — they can cause unexpected behavior and make debugging harder; they can slow down DML operations; complex business logic in triggers is hard to test. Best practice: use triggers sparingly and document them clearly.

A transaction is a sequence of one or more SQL operations treated as a single logical unit of work. Either ALL operations succeed (COMMIT) or ALL are undone (ROLLBACK), ensuring the database remains in a consistent state. Classic example: bank transfer — debit one account and credit another. If the credit fails after the debit, the debit must be rolled back. Transaction control: START TRANSACTION; (or BEGIN;) — begins the transaction; COMMIT; — saves all changes permanently; ROLLBACK; — reverts all changes made since the transaction started; SAVEPOINT name; — creates a checkpoint within a transaction; ROLLBACK TO SAVEPOINT name; — rolls back to the savepoint without undoing the entire transaction. Auto-commit: MySQL auto-commits each statement by default (each statement is its own transaction). Disable with SET autocommit = 0; or use explicit START TRANSACTION. Transactions are only supported by transactional storage engines — InnoDB supports transactions; MyISAM does not. In application code, always wrap multi-step operations that must be atomic in a transaction.

ACID is an acronym for the four properties that guarantee reliable database transactions: Atomicity: A transaction is treated as a single indivisible unit — either ALL changes succeed (COMMIT) or NONE are applied (ROLLBACK). There is no partial transaction. If step 3 of a 5-step transaction fails, steps 1 and 2 are automatically undone. Consistency: A transaction brings the database from one valid state to another valid state, honoring all defined rules (constraints, triggers, cascades). A transaction cannot violate database integrity constraints. Isolation: Concurrent transactions execute independently — one transaction's intermediate state is not visible to other transactions. The degree of isolation is configurable (isolation levels). Even if 100 transactions run simultaneously, each behaves as if it were the only transaction. Durability: Once a transaction is COMMITTED, it is permanently saved — even if the system crashes immediately after. Committed data survives server failures, power outages, and crashes (achieved through write-ahead logging/WAL). ACID compliance is a hallmark of relational databases like MySQL InnoDB, PostgreSQL, and Oracle.

MySQL (InnoDB) supports four isolation levels that control how transactions interact with each other, trading consistency for concurrency: READ UNCOMMITTED: can read uncommitted changes from other transactions (dirty reads — reading data that may be rolled back). Highest concurrency, lowest consistency. Rarely used. READ COMMITTED: can only read committed data. Prevents dirty reads but allows non-repeatable reads (reading the same row twice in the same transaction may yield different results if another transaction committed between the two reads). Used by PostgreSQL and Oracle as default. REPEATABLE READ (MySQL InnoDB default): a transaction sees a consistent snapshot of the data as it was when the transaction started. Prevents dirty and non-repeatable reads. Phantom reads are prevented in InnoDB (but not in the SQL standard definition of this level) via gap locks. SERIALIZABLE: transactions are fully isolated as if executed serially. Prevents all concurrency anomalies but lowest throughput (locks all reads). Set isolation level: SET TRANSACTION ISOLATION LEVEL READ COMMITTED;.

MySQL supports multiple storage engines. InnoDB and MyISAM were historically the two main choices. InnoDB (default since MySQL 5.5): supports ACID transactions (COMMIT/ROLLBACK); row-level locking (fine-grained, high concurrency); foreign key constraints and referential integrity; crash recovery via redo logs; MVCC for non-locking reads; better performance for mixed read/write workloads. MyISAM (legacy): no transaction support; table-level locking (one write locks the whole table — poor concurrency); no foreign keys; faster for read-heavy workloads without writes; supports FULLTEXT indexing (InnoDB added FULLTEXT in MySQL 5.6+); table data is stored more simply (can be faster to count rows as it stores the count). Verdict: Always use InnoDB for new applications. The only remaining use cases for MyISAM are legacy systems or extremely specific read-only analytical workloads. MySQL 8.0 still supports MyISAM but it is essentially deprecated for new development. For analytics, consider columnar engines like ClickHouse or MySQL's NDB Cluster for specialized workloads.

AUTO_INCREMENT is a MySQL attribute that automatically generates a unique, incrementing integer value for a column when a new row is inserted, typically used for primary keys. Declaration: id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY. On each INSERT without specifying the id column (or inserting NULL/0), MySQL assigns the next available integer. The current AUTO_INCREMENT value can be seen in SHOW TABLE STATUS LIKE "tablename";. Key behaviors: the counter only increases — deleting rows does not reuse their IDs; if you insert a specific value that is larger than the current counter, the counter jumps to that value; the counter is not reset on ROLLBACK of a transaction (gaps in IDs are normal and expected). Reset the counter: ALTER TABLE tablename AUTO_INCREMENT = 1; (only works if the new value is greater than the current max ID). For a 32-bit INT UNSIGNED, max is ~4.29 billion; use BIGINT UNSIGNED for very large tables (max ~18.4 quintillion). Gap behavior: don't rely on AUTO_INCREMENT IDs being consecutive — gaps are normal due to failed inserts, rollbacks, and explicit gaps.

EXPLAIN is a MySQL command that shows the execution plan for a SELECT, INSERT, UPDATE, or DELETE query — how MySQL intends to execute it, including which indexes it will use, estimated row counts, and join types. Usage: EXPLAIN SELECT * FROM users WHERE email = "alice@example.com";. Key columns in output: type — join type/access method (best to worst: system > const > eq_ref > ref > range > index > ALL); ALL means full table scan — almost always bad for large tables; possible_keys — indexes MySQL could potentially use; key — the index MySQL actually chose; rows — estimated number of rows MySQL expects to examine (lower is better); Extra — additional information: "Using index" (covering index, no table lookup), "Using filesort" (must sort in memory/disk — expensive), "Using temporary" (temporary table created — expensive), "Using where" (filtered after table read). EXPLAIN FORMAT=JSON provides more detail. EXPLAIN ANALYZE (MySQL 8.0+) actually executes the query and shows actual timing. Use EXPLAIN to diagnose slow queries and verify indexes are being used.

UNION combines results from two or more SELECT statements and automatically removes duplicate rows from the combined result — it performs a DISTINCT operation on the entire result set, which requires sorting or hashing to detect duplicates. UNION ALL also combines results but keeps ALL rows including duplicates, with no deduplication step. UNION ALL is always faster than UNION because it skips the expensive deduplication. When to use each: use UNION ALL when you know the queries return different data (different tables, different WHERE conditions that guarantee no overlap), or when duplicates are acceptable; use UNION when you need to eliminate exact duplicate rows. Example: SELECT name FROM employees WHERE department = "Sales" UNION SELECT name FROM employees WHERE department = "Marketing"; — if an employee is in both departments (shouldn't happen but theoretically), UNION removes the duplicate name. UNION ALL would return the name twice. Both require the same number of columns and compatible types. Both support ORDER BY on the final combined result (not within individual SELECT statements in MySQL without subqueries).

Window functions perform calculations across a set of rows related to the current row (a "window") without collapsing rows like GROUP BY. They are applied with the OVER() clause. Unlike aggregates (which produce one row per group), window functions return a value for every row while still being able to look at related rows. Key window functions: ROW_NUMBER() — assigns a unique sequential number to each row within the partition (1, 2, 3...); RANK() — like ROW_NUMBER but ties get the same rank and the next rank is skipped (1, 1, 3); DENSE_RANK() — ties get the same rank, next rank is not skipped (1, 1, 2); LAG(col, n) / LEAD(col, n) — access the value of a column n rows before/after the current row; SUM() OVER(), AVG() OVER() — running totals/averages; NTILE(n) — divides rows into n equal buckets; FIRST_VALUE() / LAST_VALUE(). OVER() syntax: ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC). MySQL 8.0+ supports window functions fully.

A CTE (Common Table Expression) is a named temporary result set defined within a single SQL statement using the WITH clause. It exists only for the duration of the query. Syntax: WITH cte_name AS ( SELECT ... FROM ... WHERE ... ) SELECT * FROM cte_name WHERE ...;. Multiple CTEs: WITH cte1 AS (...), cte2 AS (SELECT * FROM cte1 WHERE ...) SELECT * FROM cte2;. Benefits over subqueries: (1) Readability — complex queries become modular and named; (2) Reusability — reference the same CTE multiple times in the query (a subquery would be duplicated); (3) Recursion — recursive CTEs solve hierarchical data problems (trees, org charts, graphs). Recursive CTE example (employee hierarchy): WITH RECURSIVE org 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, org.level + 1 FROM employees e JOIN org ON e.manager_id = org.id ) SELECT * FROM org;. MySQL 8.0+ supports CTEs. Performance: CTEs in MySQL are not materialized by default in older versions — they may be inlined (re-evaluated each time) rather than cached.

Query optimization is the process of making SQL queries execute faster. Key techniques: (1) Use indexes: add indexes on columns in WHERE, JOIN ON, ORDER BY, GROUP BY — verify with EXPLAIN that the index is used; (2) Avoid SELECT *: specify only needed columns — reduces data transfer and can enable covering indexes; (3) Avoid functions on indexed columns in WHERE: WHERE YEAR(created_at) = 2024 can't use index on created_at — rewrite as WHERE created_at BETWEEN "2024-01-01" AND "2024-12-31"; (4) Use covering indexes: an index that contains all columns needed by the query — MySQL can satisfy the query from the index without touching the table data (EXPLAIN shows "Using index"); (5) Limit result sets: use LIMIT, filter early; (6) Optimize JOINs: ensure JOIN columns are indexed; (7) Avoid correlated subqueries: rewrite as JOINs or use EXISTS; (8) Use pagination wisely: keyset pagination instead of OFFSET for large pages; (9) Analyze query plans: use EXPLAIN ANALYZE in MySQL 8.0+; (10) Profile queries: SET profiling = 1; then SHOW PROFILE;; (11) Rewrite OR as UNION: OR conditions can prevent index use.

OLTP (Online Transaction Processing) databases are optimized for high-volume, short, fast transactions — typical operational database workloads. Characteristics: many concurrent users; queries touch few rows at a time (lookup by PK or small ranges); primarily INSERT/UPDATE/DELETE with reads; highly normalized schema (3NF+) to avoid update anomalies; optimized for write throughput and low latency; examples: MySQL, PostgreSQL, Oracle — production databases powering web applications. OLAP (Online Analytical Processing) databases are optimized for complex analytical queries over large amounts of historical data — business intelligence and reporting workloads. Characteristics: few users running complex queries; queries scan millions of rows with aggregations; primarily read-only; denormalized schema (star or snowflake schema) with dimension and fact tables for query performance; optimized for read throughput and analytical query speed; examples: Snowflake, Google BigQuery, Amazon Redshift, ClickHouse, Apache Druid. Key differences: OLTP has many small transactions; OLAP has few large scans. OLTP is normalized; OLAP is denormalized. Row-based storage vs. columnar storage. A common architecture: operational MySQL database → ETL process → data warehouse for analytics.

Sharding is a horizontal scaling technique that partitions a large database across multiple servers (shards), with each shard holding a subset of the data. Each shard is a separate database instance that can run on its own server, so the total capacity scales with the number of shards. Sharding strategies: (1) Range-based: shard by a value range (user IDs 1-1M on shard 1, 1M-2M on shard 2) — simple but can cause hot spots if one range gets all activity; (2) Hash-based: apply a hash function to the shard key to determine the shard — distributes data evenly, but range queries require hitting all shards; (3) Directory-based: maintain a lookup table mapping records to shards — flexible but the directory becomes a bottleneck. Challenges: cross-shard JOINs are impossible or very complex; transactions across shards require distributed transaction protocols (two-phase commit); rebalancing when adding shards is complex. Sharding adds significant operational complexity. Try vertical scaling, read replicas, caching, and query optimization before sharding. Tools: Vitess (sharding for MySQL, used by YouTube), ProxySQL. MongoDB and Cassandra have built-in sharding support.

A covering index is an index that contains all the columns needed to satisfy a query — the query can be answered entirely from the index without accessing the actual table rows. This is highly efficient because it avoids the secondary lookup from the index to the table. EXPLAIN shows "Using index" in the Extra column when a covering index is used. Example: SELECT email, name FROM users WHERE status = "active"; — if you create INDEX idx_status_email_name (status, email, name), MySQL can get all three columns (status for filtering, email and name for returning) from the index alone, never touching the table. Rules for covering index design: the index must include: (1) columns in the WHERE clause (leftmost), (2) columns used in JOIN ON, (3) columns in SELECT. Covering indexes are most impactful for read-heavy queries on large tables. Trade-off: larger indexes consume more disk space and slow down writes. Always measure with EXPLAIN before and after. For InnoDB, remember that secondary indexes implicitly include the primary key — INDEX (email) actually stores (email, id), so queries that also need id are covered.

Replication is MySQL's mechanism for copying data from one server (source/primary/master) to one or more servers (replicas/slaves) in near real-time, creating identical copies of the database. How it works: the primary writes all changes to the binary log (binlog); replicas connect to the primary, read the binlog, and replay the changes in the same order. Replication uses: (1) Read scaling: route read queries to replicas, writes to the primary — distributes read load horizontally; (2) High availability: if the primary fails, promote a replica to primary (failover); (3) Backup: take backups from a replica without impacting the production primary; (4) Geographic distribution: put replicas closer to users in different regions. Replication types: asynchronous (default — primary doesn't wait for replica to confirm, possible data loss on failover), semi-synchronous (primary waits for at least one replica to acknowledge before committing — safer), synchronous (all replicas must confirm — very slow). Replication lag: replicas can fall behind under heavy write load — monitor with SHOW SLAVE STATUS\G, check Seconds_Behind_Master. MySQL Group Replication provides multi-primary replication.

Hard delete permanently removes a row from the database using DELETE. The data is gone and cannot be recovered (without backups). Soft delete marks a row as deleted without actually removing it, typically by adding a deleted_at TIMESTAMP NULL or is_deleted TINYINT(1) DEFAULT 0 column. The row remains in the database but is excluded from application queries. Implementation: UPDATE users SET deleted_at = NOW() WHERE id = ?;. All queries must then filter: WHERE deleted_at IS NULL. Benefits of soft delete: (1) Data recovery — "undelete" by setting deleted_at to NULL; (2) Audit trail — know what was deleted and when; (3) Referential integrity — related data (orders for a deleted user) remains intact; (4) Analytics — deleted records contribute to historical reports. Drawbacks: (1) Every query must include the soft-delete filter (easy to forget — can expose deleted data); (2) Table grows indefinitely — need archival strategy; (3) UNIQUE constraints still apply to soft-deleted rows (a deleted email blocks reuse — use composite unique on (email, deleted_at) or handle in application). Frameworks like Laravel's SoftDeletes trait automate this pattern.

MySQL provides many built-in string functions for manipulating text data: CONCAT(s1, s2, ...) — concatenate strings: CONCAT(first_name, " ", last_name); CONCAT_WS(sep, s1, s2) — concatenate with separator, skips NULLs; LENGTH(s) — byte length; CHAR_LENGTH(s) — character count (use this for multibyte characters); UPPER(s) / LOWER(s) — change case; TRIM(s) — remove leading/trailing spaces; LTRIM(s) / RTRIM(s) — left/right trim; SUBSTRING(s, pos, len) — extract substring: SUBSTRING(email, 1, LOCATE("@", email) - 1); LEFT(s, n) / RIGHT(s, n) — leftmost/rightmost n characters; LOCATE(substr, str) — position of substring (0 if not found); REPLACE(str, from, to) — replace all occurrences; LPAD(s, len, pad) / RPAD() — pad string to length; REVERSE(s); FORMAT(n, d) — format number with commas: FORMAT(1234567.89, 2) → "1,234,567.89"; REGEXP_REPLACE() (MySQL 8.0+) — replace using regex.

MySQL provides rich date/time functions: NOW() — current datetime as DATETIME; CURDATE() / CURRENT_DATE() — current date only; CURTIME() — current time only; UTC_TIMESTAMP() — current datetime in UTC; DATE(datetime_expr) — extract the date part; TIME(datetime_expr) — extract the time part; YEAR(d), MONTH(d), DAY(d), HOUR(t), MINUTE(t), SECOND(t) — extract parts; DATE_FORMAT(d, format) — format a date: DATE_FORMAT(created_at, "%Y-%m-%d"); DATE_ADD(d, INTERVAL n unit) / DATE_SUB() — arithmetic: DATE_ADD(NOW(), INTERVAL 7 DAY); DATEDIFF(d1, d2) — difference in days; TIMESTAMPDIFF(unit, d1, d2) — difference in specified unit (YEAR, MONTH, DAY, HOUR); UNIX_TIMESTAMP() — seconds since epoch; FROM_UNIXTIME(ts) — convert epoch to DATETIME; DAYOFWEEK(d) — 1=Sunday; WEEKDAY(d) — 0=Monday; LAST_DAY(d) — last day of the month; STR_TO_DATE(str, format) — parse string to date.

The CASE expression provides conditional logic in SQL queries, similar to if-else statements in programming. Two forms: Simple CASE: CASE column WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE default_result END. Searched CASE: CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default_result END — more flexible, allows different conditions each branch. Examples: (1) Label salary ranges: SELECT name, salary, CASE WHEN salary < 30000 THEN "Junior" WHEN salary < 60000 THEN "Mid" ELSE "Senior" END AS level FROM employees;; (2) Conditional aggregation: SELECT SUM(CASE WHEN status = "completed" THEN amount ELSE 0 END) AS completed_revenue FROM orders; — pivot-like aggregation; (3) Handle NULLs: CASE WHEN phone IS NULL THEN "Not provided" ELSE phone END. CASE can be used in SELECT, WHERE, ORDER BY, GROUP BY, and HAVING. The ELSE clause is optional — without it, unmatched conditions return NULL. Functions COALESCE, NULLIF, and IF/IFNULL are shorthand for common CASE patterns.

Locking prevents concurrent transactions from corrupting shared data. Pessimistic locking assumes conflicts will happen — it locks the data before reading/modifying, preventing other transactions from accessing it until the lock is released. In MySQL: SELECT * FROM orders WHERE id = 1 FOR UPDATE; — acquires an exclusive row lock. Other transactions block until this transaction COMMITs or ROLLBACKs. Safe but reduces concurrency. Best for: high-contention data (bank account balances, inventory counts where oversell is unacceptable). Optimistic locking assumes conflicts are rare — it doesn't lock data during reads. Instead, it detects conflicts at write time by checking if the data has changed since it was read. Typically implemented with a version column (integer or timestamp): read version=3, make changes, then UPDATE with WHERE id=1 AND version=3 — if another transaction already incremented version to 4, the UPDATE affects 0 rows, and the application retries. Better concurrency, more application complexity. Best for: low-contention data, web applications where users rarely edit the same records simultaneously. Most ORMs support optimistic locking natively.

Partitioning divides a single large table into multiple smaller physical partitions while appearing as one logical table to queries. MySQL handles routing to the correct partition transparently. Unlike sharding (across multiple servers), partitioning happens within a single MySQL instance. Types: RANGE: rows assigned based on column value ranges — PARTITION BY RANGE (YEAR(created_at)) — common for date-based data; LIST: rows assigned based on specific discrete values — PARTITION BY LIST (region_id); HASH: rows distributed evenly across N partitions based on a hash of a column — PARTITION BY HASH(user_id) PARTITIONS 8; KEY: similar to HASH but uses MySQL's internal hash function. Benefits: (1) Partition pruning — queries with WHERE on the partition key only scan relevant partitions, not the whole table; (2) Faster DELETE of old data — drop entire partitions: ALTER TABLE logs DROP PARTITION p_2022;; (3) Parallelism. Limitations: partition expressions have restrictions; foreign keys not supported on partitioned tables; max 8192 partitions per table. Best for: time-series data with archival needs, very large tables with predictable query patterns.

Both store variable-length string data, but with important differences: VARCHAR(n): stores up to n characters (max ~65,535 bytes per row limit); stored inline in the table row; can be fully indexed (entire value can be indexed); supports index prefix length; can have a DEFAULT value; supports UNIQUE constraint without prefix. Best for: strings with a known max length where indexing is needed (names, emails, slugs). TEXT (and TINYTEXT, MEDIUMTEXT, LONGTEXT): no length specified in column definition; stored out-of-row in separate storage (affects performance differently); TEXT columns cannot be fully indexed — only with a prefix: INDEX (description(100)); cannot have DEFAULT values; cannot be used in MEMORY engine tables. TEXT variants: TINYTEXT (255B), TEXT (64KB), MEDIUMTEXT (16MB), LONGTEXT (4GB). Recommendation: use VARCHAR for shorter strings (up to a few hundred characters) that you need to index, sort, or use in WHERE clauses; use TEXT for long bodies of text (blog posts, descriptions, comments) that don't need full indexing. MySQL stores values ≤255 bytes of VARCHAR in the row just like CHAR; longer values go to overflow pages like TEXT.

MySQL had a built-in query cache that cached the complete result set of SELECT queries. On subsequent identical queries (exact same SQL and database state), MySQL returned cached results without re-executing. However, the query cache was removed in MySQL 8.0 because it was a scalability bottleneck: (1) The cache required a global mutex lock on every write, causing contention on write-heavy workloads; (2) Any write to any table in the query invalidated all cached queries involving that table; (3) Modern hardware with fast SSDs and efficient buffer pools made the query cache less beneficial. Alternatives for caching: (1) Application-level cache: use Redis or Memcached to cache query results in your application code — more control over invalidation logic and expiry; (2) InnoDB Buffer Pool: MySQL's own page cache (default 128MB, should be 70-80% of available RAM for dedicated servers) caches disk pages in memory — properly sizing this is more impactful than query cache; (3) ProxySQL Query Cache — proxy layer that caches query results; (4) Read replicas reduce primary write contention. Always cache at the application layer for production systems.

These three forms count rows differently: COUNT(*) counts every row in the result set, including rows with NULL values in any column. It is the standard, most readable form and the recommended way to count rows. COUNT(1) counts the number of 1s (a non-NULL constant) in the result — effectively equivalent to COUNT(*) in all practical cases. Some people mistakenly believe COUNT(1) is faster than COUNT(*); in modern MySQL (and virtually all databases), the optimizer treats them identically. Use COUNT(*) for clarity. COUNT(column) counts the number of non-NULL values in that specific column. This is different — it excludes NULL values: SELECT COUNT(phone) FROM users counts only users who have a phone number. This is useful for counting how many rows have a value in an optional column. Summary: COUNT(*) = total rows; COUNT(col) = non-NULL values in that column; COUNT(DISTINCT col) = unique non-NULL values. For InnoDB tables, COUNT(*) without a WHERE clause requires a table scan (InnoDB doesn't store row counts like MyISAM) — this is why COUNT(*) can be slow on large tables. Solution: maintain a counter table or use approximate counts.

Prepared statements are pre-compiled SQL templates with parameter placeholders that are separated from data values. Two-step execution: (1) Prepare: send the SQL template to the server for parsing and compilation; (2) Execute: send only the parameter values — the server fills in the placeholders and executes. Syntax in MySQL: PREPARE stmt FROM "SELECT * FROM users WHERE email = ? AND status = ?"; SET @email = "alice@example.com"; SET @status = "active"; EXECUTE stmt USING @email, @status; DEALLOCATE PREPARE stmt;. Benefits: (1) SQL Injection prevention: parameter values are never interpreted as SQL — the structure is fixed at prepare time. Even if a user inputs "1; DROP TABLE users", it's treated as a literal string value; (2) Performance: for queries executed many times with different parameters, parsing/compilation happens once; (3) Type safety: parameters are typed correctly. In application code, use parameterized queries via your database library (PDO with ? placeholders, Node.js mysql2 with ?, Mongoose parameterized queries). NEVER concatenate user input into SQL strings — always use prepared statements or ORMs.

The binary log (binlog) is a MySQL log file that records all changes to the database (INSERT, UPDATE, DELETE, DDL like CREATE TABLE) as ordered events. It does NOT log SELECT queries (no data changes). The binlog enables two critical features: (1) Replication: replicas read the primary's binlog and replay the changes to stay synchronized — replication would not be possible without the binlog; (2) Point-in-time recovery (PITR): take a backup, then replay binlog events up to a specific point in time to recover from data loss or corruption after the backup was taken. Binlog formats: STATEMENT (logs the SQL statements — small files but some statements are non-deterministic like NOW()); ROW (logs actual row changes — larger but deterministic, recommended); MIXED (statement for safe statements, row for others). Enable: log_bin = /var/log/mysql/mysql-bin.log in my.cnf. Useful commands: mysqlbinlog utility to read/replay binlog events; SHOW BINARY LOGS;; PURGE BINARY LOGS TO "mysql-bin.000123";. The binlog is also used by CDC (Change Data Capture) tools like Debezium to stream database changes to Kafka.

The slow query log records SQL queries that take longer than a configurable threshold to execute, helping identify performance bottlenecks. Enable it: slow_query_log = ON, slow_query_log_file = /var/log/mysql/slow.log, long_query_time = 1 (log queries taking more than 1 second — adjust to your needs; use 0.1 for busy systems). Also log queries that don't use indexes: log_queries_not_using_indexes = ON (use carefully — can log many queries). You can enable/disable at runtime: SET GLOBAL slow_query_log = ON;. Reading the slow query log: use mysqldumpslow (built-in) to summarize: mysqldumpslow -s t -t 10 /var/log/mysql/slow.log (top 10 by total time). Or use pt-query-digest (Percona Toolkit) for richer analysis including query fingerprinting, execution count, and percentile timing. Workflow: enable slow log → let it collect for a period → analyze with pt-query-digest → take the worst queries → EXPLAIN them → add indexes or rewrite queries → verify improvement.

FULLTEXT search in MySQL provides natural language text search capabilities, much more powerful than LIKE for searching text columns. It works on CHAR, VARCHAR, and TEXT columns. Creating a FULLTEXT index: ALTER TABLE articles ADD FULLTEXT INDEX ft_content (title, body);. Querying with MATCH...AGAINST: Natural language mode (default): SELECT *, MATCH(title, body) AGAINST ("database optimization" IN NATURAL LANGUAGE MODE) AS score FROM articles WHERE MATCH(title, body) AGAINST ("database optimization") ORDER BY score DESC; — returns a relevance score; stop words are ignored; results ranked by relevance. Boolean mode: AGAINST ("+mysql -oracle" IN BOOLEAN MODE) — supports operators: + (must include), - (must exclude), * (wildcard), " " (phrase), ~ (reduce relevance). Query expansion mode: first search, then expand with related terms from the initial results. Minimum word length: MyISAM ignores words shorter than 4 chars (ft_min_word_len), InnoDB ignores shorter than 3 (innodb_ft_min_token_size). For production full-text search needs, consider Elasticsearch or MeiliSearch — they provide better relevance ranking, faceted search, and handling of typos.

Both store date and time values, but with important differences: DATETIME: stores date and time literally as entered: "YYYY-MM-DD HH:MM:SS"; range: 1000-01-01 to 9999-12-31; 8 bytes; NOT affected by timezone — stores what you give it, returns what it stored. If you insert "2024-01-15 10:00:00" in New York timezone, it stores and returns exactly that, regardless of the server's timezone. TIMESTAMP: stores as UTC internally (Unix timestamp) and converts to/from the current session timezone on read/write; range: 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC (the 2038 problem!); 4 bytes (more space-efficient); supports automatic initialization and update: created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. Use TIMESTAMP when: you need timezone-aware storage (display different times to users in different zones); recording when something happened. Use DATETIME when: you store user-facing calendar dates/times that should not change with timezone (appointment slots, event dates); you need dates outside TIMESTAMP's 1970-2038 range. Always store times in UTC in your application layer for maximum clarity.

Connection pooling maintains a cache of reusable database connections rather than creating a new connection for every query. Creating a MySQL connection involves: TCP handshake, authentication, session initialization — typically 10-100ms overhead. With pooling, connections are established at startup and reused. Most application frameworks and database libraries implement pooling automatically. Configuration considerations: pool size — too small: requests queue up waiting for connections, increasing latency; too large: overwhelming the MySQL server (MySQL has a max_connections limit, default 151). Optimal pool size is rarely more than 10-20 connections per application instance — MySQL can handle ~200 concurrent queries efficiently. Formula: pool_size ≈ (core_count * 2) + effective_spindle_count. Monitoring: watch for "Too many connections" errors (hit max_connections), slow query time (pool exhaustion), and connection wait time. MySQL-specific considerations: wait_timeout (how long MySQL keeps an idle connection — default 28800s = 8h); your pool must handle connection drops due to timeout with reconnect logic. Connection pools also reduce overhead on MySQL from frequent TCP/auth setup.

B-tree index (default): a balanced tree data structure where all leaf nodes are at the same depth. Supports: equality lookups (=), range queries (BETWEEN, >, <), prefix LIKE ("abc%"), and ORDER BY (can avoid filesort). Supports composite indexes with leftmost prefix rule. Used by virtually all MySQL index types (InnoDB, MyISAM, Memory engine). Hash index: applies a hash function to the indexed column value to directly locate the data. Supports only exact equality lookups (=, <=>). Does NOT support range queries, ORDER BY, or prefix matching. Much faster for exact lookups (O(1) vs O(log n) for B-tree) when data fits in memory. In MySQL, the Memory storage engine supports hash indexes. InnoDB has an Adaptive Hash Index (AHI): it automatically builds an in-memory hash index on hot B-tree pages when it detects the same pages being accessed repeatedly — you cannot manually create hash indexes in InnoDB. NDB Cluster (MySQL Cluster) supports explicit hash indexes. For most purposes, B-tree indexes are the right choice because of their versatility. If you need O(1) exact lookups and your dataset fits in memory, consider Redis instead.

MVCC (Multi-Version Concurrency Control) is InnoDB's mechanism for providing consistent reads without acquiring read locks, enabling high concurrency. Instead of locking rows for reads, InnoDB maintains multiple versions of each row. When a transaction reads a row, it sees the version that was committed as of the transaction's start time — not necessarily the current version. How it works: every InnoDB row has two hidden columns: DB_TRX_ID (ID of the transaction that last modified the row) and DB_ROLL_PTR (pointer to the undo log entry). When a row is updated, the old version is moved to the undo log, and the new version is written with the current transaction ID. A reader finds the version visible to its snapshot by following the undo log chain. This means: readers don't block writers, and writers don't block readers — massive concurrency improvement. The consistent snapshot is taken at the START of a REPEATABLE READ transaction (or at the start of each statement in READ COMMITTED). Undo logs are purged by the InnoDB purge thread when no transaction needs them. Large, long-running transactions prevent purge, causing the undo log (and ibdata1) to grow unboundedly — a common production issue.

The InnoDB Buffer Pool is the most important MySQL memory component — it caches table data pages and index pages in RAM, dramatically reducing disk I/O. It works as a modified LRU (Least Recently Used) cache: recently accessed pages stay in memory; when the pool is full and a new page needs to be loaded, the least recently used page is evicted. InnoDB uses a midpoint insertion strategy: new pages are inserted at the midpoint (3/8 from the tail) rather than the head, protecting frequently accessed "hot" pages from being evicted by a large table scan. The pool consists of 16KB pages organized into a linked list. Buffer pool hit ratio (should be >99%): SHOW STATUS LIKE "Innodb_buffer_pool_read%"; — ratio = (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests. If pages are often evicted (low hit ratio), increase buffer_pool_size. Multiple buffer pool instances: set innodb_buffer_pool_instances = 8 (one per GB, reduces contention from multiple threads accessing the single pool mutex). Buffer Pool Warming: MySQL can save and restore the buffer pool state on restart (innodb_buffer_pool_dump_at_shutdown) to avoid cold-start performance degradation.

A deadlock occurs when two or more transactions are each waiting for a lock held by the other, creating a circular dependency — neither can proceed. InnoDB detects deadlocks and automatically rolls back one of the transactions (the "victim" — typically the one with the fewest changes, making it cheaper to roll back). The application must handle the error and retry the transaction. Viewing deadlock info: SHOW ENGINE INNODB STATUS\G — shows the last detected deadlock. Prevention strategies: (1) Consistent lock ordering: always acquire locks in the same order across all transactions — if transaction A locks users then orders, transaction B must do the same; (2) Keep transactions short: long transactions hold locks longer, increasing collision probability; (3) Use lower isolation levels: READ COMMITTED acquires fewer locks than REPEATABLE READ; (4) Use SELECT ... FOR UPDATE only when necessary; (5) Process rows in a consistent order: ORDER BY in SELECT...FOR UPDATE queries; (6) Avoid user interaction inside transactions; (7) Use SELECT ... SKIP LOCKED (MySQL 8.0+) for queue processing to avoid competing for locked rows; (8) Application retry logic: catch deadlock errors (MySQL error 1213) and retry with exponential backoff.

The query optimizer is MySQL's component that determines the most efficient way to execute a query. It analyzes the SQL, considers available indexes, table statistics, and join orders, then produces an execution plan — the sequence of operations to retrieve the data. MySQL uses a cost-based optimizer: it estimates the cost (I/O and CPU) of various execution strategies and chooses the one with the lowest estimated cost. The optimizer considers: which indexes to use (or whether to scan the table), join order (for queries joining multiple tables — it evaluates permutations, but caps at 61 tables for performance), index selectivity (high-cardinality indexes are preferred), table row counts (from statistics). Table statistics are crucial for the optimizer — it uses ANALYZE TABLE to update statistics. Stale statistics cause suboptimal plans. Viewing the plan: EXPLAIN (estimated), EXPLAIN ANALYZE (actual execution, MySQL 8.0+). Optimizer hints (MySQL 8.0+): SELECT /*+ INDEX(users idx_email) */ * — force a specific index; /*+ JOIN_ORDER(users, orders) */ — force join order. Optimizer switches: SET optimizer_switch = "index_merge=off";. Statistics for optimizer: SHOW INDEX FROM tablename\G — shows cardinality estimates.

Optimistic locking detects conflicts at write time by verifying the data hasn't changed since it was read. Two common implementations: Version number (integer counter): add a version INT NOT NULL DEFAULT 0 column. On read, fetch the version. On update: UPDATE products SET stock = stock - 1, version = version + 1 WHERE id = ? AND version = ? — check affected rows; 0 means conflict (another transaction already updated and incremented version). Increment version atomically. Advantages: no issues with precision or time synchronization; works reliably even on the same millisecond. Timestamp: add an updated_at DATETIME(6) column (microsecond precision). On update: UPDATE products SET stock = ?, updated_at = NOW(6) WHERE id = ? AND updated_at = ?. Disadvantages: requires microsecond precision to avoid conflicts when two updates happen in the same millisecond (still possible on fast hardware); clock synchronization issues in distributed systems; TIMESTAMP resolution matters. Recommendation: use integer version numbers — they are simpler, unambiguous, and not subject to clock issues. Version-based optimistic locking is natively supported by ORMs like Hibernate, TypeORM (@VersionColumn), and Sequelize (version: true).

Common SQL anti-patterns that cause performance, correctness, or maintainability problems: (1) SELECT *: fetches unused columns, prevents covering indexes, breaks code when columns are added/removed; (2) N+1 queries: one query to get N records then N queries for related data — use JOINs or eager loading; (3) No indexes on foreign keys: every JOIN becomes slow; (4) Functions on indexed columns in WHERE: WHERE YEAR(date) = 2024 defeats the index — rewrite as range; (5) Leading wildcard LIKE: WHERE name LIKE "%smith" — full table scan; (6) Storing comma-separated values in one column: violates 1NF, makes querying and joining impossible; (7) Not using transactions for multi-step operations; (8) Using OFFSET for deep pagination: OFFSET 10000 scans and discards 10,000 rows — use keyset pagination; (9) COUNT(*) without LIMIT on large tables; (10) Blind DELETE without transaction and test SELECT first; (11) Ignoring NULL semantics: comparing with = NULL instead of IS NULL; (12) Magic numbers: WHERE status = 1 — use meaningful values or ENUMs; (13) varchar for numbers or dates: prevents proper sorting and comparison; (14) Not normalizing at all (or over-normalizing for analytics).

Change Data Capture (CDC) is a technique for tracking and capturing data changes (inserts, updates, deletes) in a database as they happen, and streaming those changes to other systems in real-time. In MySQL, CDC is typically implemented by reading the binary log (binlog), which records all data modifications. Popular CDC tools for MySQL: (1) Debezium: open-source, streams binlog events to Apache Kafka as structured change events; supports MySQL, PostgreSQL, MongoDB. The Kafka topic gets an event for every row change with before/after values; (2) AWS DMS (Database Migration Service): managed CDC for cloud migrations and ongoing replication; (3) Maxwell's Daemon: MySQL binlog to Kafka/Kinesis/stdout; (4) Tungsten Replicator. Requirements: MySQL binlog must be enabled, format set to ROW, and the CDC tool given REPLICATION SLAVE and REPLICATION CLIENT privileges. Use cases: (1) Syncing data to Elasticsearch for search; (2) Updating Redis cache when DB changes; (3) Building event-driven microservices that react to data changes; (4) Audit logs; (5) Real-time analytics pipelines; (6) Zero-downtime database migrations. CDC is more reliable than polling (SELECT WHERE updated_at > last_check) because soft deletes and timing gaps can miss changes.

Two main pagination approaches, with very different performance characteristics: OFFSET-based (traditional): SELECT * FROM posts ORDER BY id DESC LIMIT 10 OFFSET 990; — page 100 with 10 per page. MySQL must scan and discard 990 rows before returning 10. For page 10,000: 99,990 discarded rows — extremely slow on large tables even with indexes, because MySQL reads all skipped rows. Keyset/Cursor pagination (recommended for large datasets): instead of OFFSET, filter based on the last seen value: SELECT * FROM posts WHERE id < :last_seen_id ORDER BY id DESC LIMIT 10; — uses the index directly, O(log n) regardless of page. Client receives the last id in the response and sends it for the next page. Advantages: consistent performance at any depth; stable results (OFFSET can skip or duplicate rows if new records are inserted between pages). Disadvantages: cannot jump to arbitrary pages; requires a sortable unique column. Deferred join (optimization for OFFSET when unavoidable): SELECT t.* FROM posts t JOIN (SELECT id FROM posts ORDER BY id DESC LIMIT 10 OFFSET 990) tmp ON t.id = tmp.id; — the subquery uses a covering index scan to get 10 IDs, then joins to fetch full rows for only those 10 — much faster than scanning full rows for 1000 records.

MySQL's Event Scheduler is a built-in job scheduler that executes SQL statements or stored procedures on a scheduled basis — similar to cron jobs but running entirely within MySQL. Enable it: SET GLOBAL event_scheduler = ON; or in my.cnf: event_scheduler = ON. Creating events: CREATE EVENT purge_old_logs ON SCHEDULE EVERY 1 DAY STARTS "2024-01-01 03:00:00" DO DELETE FROM audit_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);. One-time event: ON SCHEDULE AT NOW() + INTERVAL 1 HOUR. View events: SHOW EVENTS;. Modify: ALTER EVENT name DISABLE;. Drop: DROP EVENT name;. Use cases: (1) Archiving old data to a history table; (2) Purging expired sessions or tokens; (3) Refreshing summary/aggregate tables for reporting; (4) Sending scheduled notifications; (5) Database maintenance tasks (ANALYZE TABLE, OPTIMIZE TABLE). Advantages over application-level cron: runs even if the application is down; no external scheduler to maintain. Disadvantages: event code lives in the database (harder to version control); failures are less visible (check mysql.event_errors); not suitable for complex logic better handled in application code. Always log event execution for monitoring.

The Performance Schema (P_S) is a built-in MySQL database (performance_schema) that provides a low-level monitoring capability for MySQL server execution. It instruments server internals to collect performance data with minimal overhead. Enabled by default in MySQL 5.6+. Key tables: events_statements_* — SQL statement statistics (total time, rows examined, temporary tables); events_stages_* — internal processing stages per statement; events_waits_* — wait events (mutex waits, I/O waits, lock waits); table_io_waits_summary_by_table — I/O activity per table; table_lock_waits_summary_by_table — lock waits per table; file_summary_by_event_name — file I/O. Useful queries: find top slow queries: SELECT DIGEST_TEXT, AVG_TIMER_WAIT/1e12 AS avg_sec, COUNT_STAR FROM performance_schema.events_statements_summary_by_digest ORDER BY avg_sec DESC LIMIT 10;. The sys schema (MySQL 5.7.7+) provides user-friendly views on top of performance_schema: SELECT * FROM sys.statements_with_full_table_scans ORDER BY no_index_used_count DESC;. Enable specific instruments: UPDATE performance_schema.setup_instruments SET ENABLED="YES" WHERE NAME LIKE "statement/%";

ProxySQL is a high-performance, open-source MySQL proxy that sits between application servers and MySQL servers, providing several advanced features: (1) Read/Write splitting: automatically routes SELECT queries to read replicas and writes to the primary — no application code changes needed; (2) Connection pooling: multiplexes thousands of application connections into a smaller pool of MySQL connections, reducing MySQL connection overhead; (3) Query routing: route specific queries to specific servers based on regex rules — send reporting queries to a dedicated replica; (4) Query caching: cache query results in ProxySQL memory; (5) Query rewriting: modify queries on the fly (add limits, change parameters) without changing application code; (6) Failover handling: redirect queries away from failed servers automatically; (7) Query mirroring: send a copy of production traffic to a shadow server for testing; (8) Rate limiting and firewall: block or limit specific query patterns; (9) Connection multiplexing: allow 10,000 app connections sharing 200 MySQL connections. ProxySQL is configured via its own admin interface (mysql -u admin -padmin -h 127.0.0.1 -P 6032) and stores config in SQLite. It is used by many high-traffic MySQL deployments (GitHub uses it) and is compatible with Galera Cluster and Group Replication.

Generated columns (also called computed or virtual columns, introduced in MySQL 5.7) are columns whose values are derived from an expression based on other columns in the same table, rather than stored directly. Two types: VIRTUAL (default): the value is computed on the fly each time it is read — no storage used (except for index entries); STORED: the computed value is calculated and stored on disk at insert/update time — takes storage but can be faster to read for expensive computations. Syntax: ALTER TABLE orders ADD COLUMN total DECIMAL(10,2) AS (quantity * unit_price) STORED;. You can create indexes on generated columns — extremely useful for computed values used in WHERE clauses: ALTER TABLE users ADD COLUMN email_domain VARCHAR(255) AS (SUBSTRING_INDEX(email, "@", -1)) STORED, ADD INDEX idx_email_domain (email_domain); — enables fast lookup by email domain without modifying the email column. Use cases: extracting JSON fields for indexing: ADD COLUMN city VARCHAR(100) AS (data->>"$.address.city") STORED, ADD INDEX (city);; denormalizing computed values for performance; creating function-based index equivalents. Generated columns cannot reference other generated columns or non-deterministic functions (NOW(), RAND()).

Stored Function: returns exactly one value; can be called inside SQL expressions (SELECT, WHERE, etc.); declared with RETURNS datatype and must have a RETURN statement; cannot include transaction control (COMMIT/ROLLBACK); cannot call stored procedures that modify data (in most contexts); used for encapsulating computations: CREATE FUNCTION get_age(dob DATE) RETURNS INT DETERMINISTIC RETURN TIMESTAMPDIFF(YEAR, dob, CURDATE()); — call: SELECT name, get_age(birth_date) FROM users;. Stored Procedure: does not return a value via RETURN (uses OUT parameters or result sets); called with CALL, not inside expressions; can execute any SQL including DDL, transactions, and dynamic SQL (PREPARE/EXECUTE); can return multiple result sets; better for complex business workflows. Key differences: functions can be used in SELECT/WHERE/HAVING; procedures cannot. Procedures can modify data and manage transactions freely; functions have restrictions (DETERMINISTIC declaration required for replication safety). In practice: use functions for reusable computations used in queries; use procedures for complex multi-step workflows, batch operations, or when you need to return multiple result sets. Both support IN/OUT parameters, though functions primarily use IN.