MySQL Interview Master

MySQL is an open-source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). It is one of the most popular databases in the ...

Primary features of MySQL include: 1. **Relational Database System**: Stores data in tables with rows and columns. 2. **High Performance**: Optimized for fast r...

SQL (Structured Query Language) is a standard language used to interact with databases. MySQL is an actual software project (an RDBMS) that implements SQL. - *...

The default port for MySQL is **3306**.

mysql -h localhost -P 3306 -u root -p

In MySQL, a database is a container that holds a collection of related tables, views, stored procedures, and other database objects. You use the `CREATE DATABAS...

CREATE DATABASE interview_db;

MySQL supports several categories of data types: 1. **Numeric**: INT, DECIMAL, FLOAT, DOUBLE. 2. **String**: CHAR, VARCHAR, TEXT, BLOB. 3. **Date and Time**: DA...

**CHAR** is fixed-length. If you define CHAR(10) and store 'ABC', it will use 10 bytes and pad the rest with spaces. **VARCHAR** is variable-length. If you defi...

You use the `CREATE TABLE` statement followed by the table name and column definitions.

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

A Primary Key is a column (or set of columns) that uniquely identifies each row in a table. - It cannot contain NULL values. - It must be unique. - A table can...

A Foreign Key is a column that creates a link between two tables. It refers to the Primary Key of another table, ensuring referential integrity by preventing or...

- **DELETE**: A DML command. It deletes rows one by one and can be filtered with a WHERE clause. It is slower and records each row deletion in the transaction l...

A Unique Constraint ensures that all values in a column are distinct. Unlike Primary Keys, a table can have multiple Unique Constraints, and they *can* accept o...

You use the `ALTER TABLE` statement with the `ADD` clause.

ALTER TABLE users ADD phone_number VARCHAR(15);

The `AUTO_INCREMENT` attribute allows a unique number to be generated automatically when a new record is inserted into a table. This is often used for the Prima...

You use the `INSERT INTO` statement.

INSERT INTO users (username, email) 
VALUES ('john_doe', 'john@example.com');

The `WHERE` clause is used to filter records that fulfill a specified condition in SELECT, UPDATE, or DELETE statements.

SELECT * FROM users WHERE username = 'john_doe';

The `LIMIT` clause is used to specify the number of records to return. This is useful for pagination or limiting the load on the database.

SELECT * FROM users LIMIT 10 OFFSET 20;

You use the `ORDER BY` clause, followed by `ASC` (ascending) or `DESC` (descending).

SELECT * FROM users ORDER BY created_at DESC;

Aggregate functions perform a calculation on a set of values and return a single value. Common ones include: `COUNT()`, `SUM()`, `AVG()`, `MAX()`, and `MIN()`.

The `DISTINCT` keyword is used to return only unique (different) values from a column.

SELECT DISTINCT country FROM users;

A NULL value represents missing or unknown data. It is *not* the same as zero or an empty string. You test for it using `IS NULL` or `IS NOT NULL`.

You use the `UPDATE` statement. Always use a WHERE clause unless you want to update all rows!

UPDATE users 
SET email = 'new_email@example.com' 
WHERE id = 1;

You use the `DROP` command. This is permanent and cannot be undone.

DROP TABLE users;
DROP DATABASE interview_db;

The `LIKE` operator is used in a WHERE clause to search for a specified pattern in a column. - `%` represents zero, one, or multiple characters. - `_` represen...

SELECT * FROM users WHERE username LIKE 'j%';

Since MySQL 5.5, the default storage engine is **InnoDB**. It supports ACID transactions, row-level locking, and foreign keys.

An Inner Join returns rows only when there is at least one match in both tables. It is used to combine related data that exists in multiple tables based on a co...

SELECT orders.id, users.username 
FROM orders 
INNER JOIN users ON orders.user_id = users.id;

- **LEFT JOIN**: Returns all rows from the left table and matched rows from the right table. If no match is found, NULL values are returned for columns of the r...

A Cross Join returns the Cartesian product of the two tables, meaning it joins every row from the first table with every row from the second table. This is gene...

The `GROUP BY` clause is used to group rows that have the same values into summary rows, like "find the number of users in each country". It's often used with a...

SELECT country, COUNT(id) 
FROM users 
GROUP BY country;

- **WHERE**: Filters rows *before* they are grouped. - **HAVING**: Filters groups *after* they are formed. It is specifically used with aggregate functions.

An index is a data structure used to quickly locate and access data in a table without scanning the entire table. It works like an index in a book. While it spe...

1. **Primary Index**: Automatically created on the primary key. 2. **Unique Index**: Ensures column values are unique. 3. **Full-Text Index**: Used for text-bas...

A View is a virtual table based on the result-set of an SQL statement. It does not store data itself but provides a simplified or restricted way to look at exis...

CREATE VIEW active_users AS 
SELECT id, username FROM users WHERE status = 'active';

A Stored Procedure is a prepared SQL code that you can save and reuse. It's useful for complex logic, reduces network traffic, and can improve performance as th...

A Trigger is a set of SQL statements that automatically execute or 'fire' when an event (INSERT, UPDATE, or DELETE) occurs on a specific table.

CREATE TRIGGER after_user_insert 
AFTER INSERT ON users 
FOR EACH ROW 
INSERT INTO logs (message) VALUES ('New user created');

A transaction is a unit of work that contains one or more SQL statements. MySQL transactions follow **ACID** properties: Atomicity, Consistency, Isolation, and ...

- **COMMIT**: Saves the changes made during a transaction permanently to the database. - **ROLLBACK**: Undoes all changes made during the current transaction if...

- **Functions**: Must return a single value, can be used in SELECT statements, and only allow input parameters. - **Stored Procedures**: Can return multiple val...

You can use the `SHOW PROCESSLIST` command to see all active connections and their current queries.

SHOW PROCESSLIST;

Full-text search (FTS) allows you to search for words or phrases in large text columns efficiently using a specialized index. You use `MATCH()` and `AGAINST()` ...

SELECT * FROM posts 
WHERE MATCH(title, content) AGAINST('database');

Query optimization is the process of writing SQL in a way that uses minimal resources (CPU, Memory) and executes as fast as possible. This often involves using ...

The `EXPLAIN` keyword is used before a query to see how MySQL intends to execute it. It reveals if indexes are being used and how many rows are being scanned.

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

A subquery is a query nested inside another SQL statement. It's often used in WHERE or FROM clauses to filter data based on the results of another query.

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

- **UNION**: Combines the results of two SELECT statements and removes duplicate rows. - **UNION ALL**: Combines results but includes duplicates. It is faster b...

The `COALESCE()` function returns the first non-null value in a list. It is very useful for providing default values when data might be NULL.

SELECT COALESCE(phone, 'No Phone Provided') FROM users;

A composite index is an index on multiple columns. It is useful for queries that filter by more than one column in a specific order.

You can use subqueries or the `LIMIT` clause with an offset.

SELECT DISTINCT salary 
FROM employees 
ORDER BY salary DESC 
LIMIT 1 OFFSET 1;

Referential integrity is a database concept that ensures the relationship between tables remains consistent. For example, you cannot delete a row from a 'users'...

These are rules for foreign keys: - **ON DELETE CASCADE**: If a parent row is deleted, automatically delete all child rows. - **ON UPDATE CASCADE**: If a parent...

- **NOW()**: Returns the current date and time (YYYY-MM-DD HH:MM:SS). - **CURDATE()**: Returns only the current date (YYYY-MM-DD).

MySQL (InnoDB) supports four isolation levels: 1. **READ UNCOMMITTED**: Lowest isolation, allows dirty reads. 2. **READ COMMITTED**: Prevents dirty reads, but a...

- **Atomicity**: All operations in a transaction succeed or none do (All or nothing). - **Consistency**: A transaction transforms the database from one valid st...

A deadlock occurs when two or more transactions mutually hold locks that the others need, causing them to wait forever. InnoDB automatically detects deadlocks a...

- **Pessimistic Locking**: Assumes a conflict will happen and locks the records before updating (`SELECT ... FOR UPDATE`). - **Optimistic Locking**: Assumes no ...

The binary log contains 'events' that describe database changes such as table creation operations or changes to table data. It is crucial for **Replication** an...

MySQL replication typically uses a Source-Replica model: 1. The Source writes changes to its **Binary Log**. 2. The Replica connects to the Source and reads the...

- **Statement-Based**: The actual SQL query is sent to replicas. It is efficient but can cause issues with non-deterministic functions like `NOW()`. - **Row-Bas...

- **Vertical**: Adding more resources (CPU, RAM, SSD) to a single server. - **Horizontal**: Adding more servers to handle the load, often using Read Replicas or...

Sharding is the process of splitting a large database into smaller, faster, more easily managed parts called shards. Each shard is a separate database stored on...

Query cache stores the exact text of a SELECT statement together with the corresponding result. If an identical statement is received later, the server retrieve...

The buffer pool is a memory area where InnoDB caches table and index data as it is accessed. It is the most important setting for MySQL performance. A large buf...

Invisible columns are columns that are not visible to `SELECT *` queries unless explicitly named. They are useful for adding infrastructure columns (like timest...

- **InnoDB**: Supports transactions, row-level locking, foreign keys, and is crash-resilient. - **MyISAM**: Does not support transactions, only uses table-level...

Common methods include: 1. **mysqldump**: Logical backup (generates SQL file). 2. **MySQL Shell (util.dumpInstance)**: Faster, multi-threaded logical backup. 3....

mysqldump -u root -p database_name > backup.sql

The slow query log consists of SQL statements that take more than `long_query_time` seconds to execute. It is the primary tool for identifying performance bottl...

Window functions perform calculations across a set of table rows that are related to the current row. Unlike aggregate functions, they do not group rows into a ...

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) as rank FROM employees;

A CTE is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It makes complex queries more readable.

WITH RegionalSales AS (
  SELECT region, SUM(amount) AS total FROM sales GROUP BY region
)
SELECT * FROM RegionalSales WHERE total > 10000;

MySQL (since 5.7) provides a JSON data type that allows for efficient storage and querying of JSON documents. It includes functions like `JSON_EXTRACT()` and su...

SELECT info->'$.customer_name' FROM orders;

A covering index is an index that includes all the columns requested in a query. In this case, MySQL can fulfill the request by reading the index only, without ...

The Performance Schema is a tool for monitoring MySQL server execution at a very low level. It collects statistics on events, resource usage, and internal wait ...

An Upsert (Update or Insert) is performed in MySQL using the `INSERT ... ON DUPLICATE KEY UPDATE` syntax. If a record with the same unique key exists, it update...

INSERT INTO stats (id, count) VALUES (1, 1) 
ON DUPLICATE KEY UPDATE count = count + 1;

1. Run `mysql_secure_installation`. 2. Use strong passwords. 3. Restrict host access for the 'root' user. 4. Enable SSL for connections. 5. Follow the 'Principl...

- **Temporary Table**: Exists only for the duration of the current session and is visible only to that session. It can be referenced multiple times. - **Derived...

A phantom read occurs when a transaction reads a set of rows that satisfy a condition, but another transaction inserts a new row that also satisfies the conditi...

Adaptive Hash Index (AHI) is an InnoDB feature that automatically creates hash indexes for frequently accessed pages in the Buffer Pool. This allows B-Tree page...

You can use `GROUP BY` and `HAVING` to find values that appear more than once.

SELECT email, COUNT(email) 
FROM users 
GROUP BY email 
HAVING COUNT(email) > 1;

One way is to use a `JOIN` on the same table and delete rows with a higher ID.

DELETE u1 FROM users u1
INNER JOIN users u2 
WHERE u1.id > u2.id AND u1.email = u2.email;

You can use the `LIMIT` clause with an offset (n-1).

SELECT DISTINCT salary 
FROM employees 
ORDER BY salary DESC 
LIMIT 1 OFFSET 4; -- 5th highest

Use `DATE_SUB()` or `INTERVAL` arithmetic.

SELECT * FROM orders 
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY);

You can use a Regular Expression.

SELECT * FROM table 
WHERE column_name REGEXP '^[0-9]+$';

1. Check `SHOW PROCESSLIST` for stuck/long-running queries. 2. Examine the **Slow Query Log**. 3. Check server metrics (CPU, IOPS, Memory/Buffer Pool usage). 4....

In MySQL, you can simply set them equal to each other in one line.

UPDATE table SET col1 = col2, col2 = col1;

Use `INSERT INTO ... SELECT`.

INSERT INTO archive_users (id, name) 
SELECT id, name FROM users WHERE active = 0;

Use the `MAX()` and `LENGTH()` functions.

SELECT MAX(LENGTH(description)) FROM products;

Common patterns include: 1. **Adjacency List**: Each row has a `parent_id` (Simple, but hard to query deep trees). 2. **Nested Sets**: Uses `lft` and `rgt` valu...

Use the `LEFT()` or `SUBSTRING()` function.

SELECT LEFT(username, 3) FROM users;

Use the `CONCAT()` function.

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

Use the `DATEDIFF()` function.

SELECT DATEDIFF('2026-12-31', '2026-01-01');

1. Check if the user account is locked or expired in `mysql.user`. 2. Verify there are no 'max_connections' or 'max_user_connections' limits being hit. 3. Check...

Use the `LAST_INSERT_ID()` function immediately after the insert.

SELECT LAST_INSERT_ID();

Use `ORDER BY RAND() LIMIT 1`. **Warning**: This is slow on large tables as it requires a full table scan.

SELECT * FROM users ORDER BY RAND() LIMIT 1;

Use `UPPER()` or `UCASE()`.

SELECT UPPER(username) FROM users;

Use the `VERSION()` function or `SELECT @@version;`.

SELECT VERSION();

1. Use a **Unique Constraint** on a composite of product_id and timestamp (if timestamp has enough precision). 2. Use a **Transaction** with `SERIALIZABLE` isol...

Use `SHOW TABLES;` or query `information_schema.tables`.

SHOW TABLES;

Use the `RENAME TABLE` statement.

RENAME TABLE old_name TO new_name;

Use the `GRANT` statement.

GRANT ALL PRIVILEGES ON db_name.* TO 'user'@'localhost';

Use `DESCRIBE` or `SHOW CREATE TABLE`.

DESCRIBE users;

1. If you are in a transaction and haven't committed, use `ROLLBACK`. 2. If not, you must restore from a **Backup**. 3. If **Binary Logging** is enabled, you ca...

Use `TRUNCATE TABLE`.

TRUNCATE TABLE logs;