Top 100 MySQL Interview Questions [With Sample Answers]: 2025 Edition

MySQL is an open-source database that arranges data in relational tables. It is highly demanded for its speed, reliability, and user-friendly tools. Many developers trust it for everything from web apps to analytics.

This blog covers 100 MySQL interview questions, from core basics like creating tables and querying data to advanced areas such as indexing, replication, and high-level optimizations. You will see a clear breakdown of beginner, intermediate, and advanced questions so that you know exactly where to focus.

42 Beginner-level MySQL Interview Questions

You will find 42 MySQL interview questions and answers here that focus on basic tasks in MySQL, from installing and configuring the database to writing essential queries. They guide you through table creation, CRUD operations, data types, and simple constraints.

1. What is MySQL, and how does it differ from other relational databases?

MySQL is an open-source relational database management system. It offers speed and a straightforward setup process. Thanks to its large community and robust support, it is widely used for web apps. 

It differs from other systems like PostgreSQL or Oracle because it often feels simpler to install and maintain. Those others might have extra features such as built-in analytics, whereas MySQL focuses on being fast, reliable, and easy to use.

Also Read: PostgreSQL vs MySQL: Explore Key Differences

If you’re an absolute beginner, you must check out upGrad’s free tutorial, Creating MySQL Database Using Different Methods. 

2. What does the SQL in MySQL stand for?

SQL stands for Structured Query Language. It is the core language for creating, reading, updating, or deleting data in relational databases.

Also Read: SQL Vs MySQL: Difference Between SQL and MySQL

3. What do you mean by databases?

A database is a structured collection of information. It allows consistent storage, retrieval, and control of records without resorting to random file searches. Data is organized so that queries can run efficiently while maintaining integrity.

4. What is the difference between a database and a table?

A database holds many tables under one structure. Each table concentrates on a single category of data arranged in rows and columns. The database can also include user access rules, backup settings, and relationships that link multiple tables.

Info on key differences:

5. What is DBMS, and how is it different from RDBMS?

A DBMS is software designed to store and retrieve data. An RDBMS adopts the concept of tables with primary and foreign keys. This setup keeps information linked and minimizes redundancy. MySQL is a popular example of an RDBMS.

Info on key differences:

6. In which language(s) has MySQL been written?

Its core parts are written in C and C++. Some parsing components involve other resources, but C and C++ dominate the codebase.

7. What are the main data types available in MySQL?

MySQL data types cover numeric, character-based, date/time, and specialized formats. Each category is suited for different kinds of information:

• Numeric: It includes INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT for integer values, as well as DECIMAL, FLOAT, and DOUBLE for decimals.
• String: It has CHAR (fixed length), VARCHAR (variable length), and larger text fields such as TEXT and MEDIUMTEXT. For binary data, BLOB types exist.
• Date/Time: It offers DATE, TIME, DATETIME, TIMESTAMP, and YEAR for handling temporal data.
• JSON: It allows storing semi-structured data in JSON format, which can be queried with JSON-specific functions.

Also Read: How to Open a JSON File? A Complete Guide to Creating and Managing JSON Files

8. How do you install MySQL?

MySQL can be installed in several ways. Most systems rely on official packages or installer tools:

• Installer: On Windows or macOS, we download the official MySQL installer, run it, and configure credentials.
• Package Manager: On Linux, we use apt-get install mysql-server or yum install mysql-server to fetch and install the server.
• Configuration: Upon completion, we set a root password, enable the service to start at boot, and confirm the port or socket paths.

9. How do you check the MySQL version?

A few methods show the installed version:

• SQL Query: Run SELECT VERSION(); inside the MySQL client.
• Command Line: Execute mysql --version in a terminal or command prompt.
• GUI Tools: MySQL Workbench or other administration software often displays the version in the connection info.

10. How do you connect to a MySQL database?

It helps to use the MySQL client and basic credentials. Steps may differ slightly across systems, but they typically involve:

• Opening Terminal or Command Prompt: Start a shell session.
• Running the mysql Command: For instance, mysql -u root -p prompts for the root user’s password.
• Providing Credentials: Enter the password if prompted, and MySQL will grant access to the server.
• Choosing a Database: Use USE database_name; to work with a specific database.

11. Why do we use the MySQL database server?

Many teams pick MySQL because it simplifies data handling and stays stable under heavier loads. It serves as a go-to for various web or app projects that need relational data storage.

• Open Source: Free to download, and the codebase can be reviewed or extended by developers.
• Performance: Manages queries efficiently with minimal resource overhead.
• Reliability: Has ACID compliance (through InnoDB) for consistent and safe data transactions.
• Community Support: Offers plenty of tutorials and forum discussions for troubleshooting.

12. What are some advantages of using MySQL?

MySQL consistently appears on lists of recommended databases thanks to several benefits:

• Easy Setup: New users can install it quickly on most operating systems.
• Broad Compatibility: Works well with many programming languages and frameworks.
• Security Features: Includes user privileges and SSL support to keep data safe.
• Scalability: Grows from small prototypes to large production environments.

13. How do you create a database in MySQL?

A single SQL command can set up a new database. 

Specific steps often look like this:

• Opening the MySQL Client: Launch the command-line interface or use a GUI tool such as MySQL Workbench.
• Executing CREATE DATABASE: For instance, CREATE DATABASE my_new_db;
• Confirming Creation: Run SHOW DATABASES; to confirm that my_new_db is listed.
• Switching to That Database: Use USE my_new_db; before creating tables or inserting data.

14. How do you drop (delete) a database in MySQL?

The command ‘DROP DATABASE sample_db’ removes that database and its contents completely. It is wise to check SHOW DATABASES; to confirm removal. Since this action is final, backups should be performed if the data is valuable.

15. How do you view all databases in MySQL?

We can run SHOW DATABASES; inside the MySQL client. That command displays every database the current user has permission to see. However, if permissions are restricted, some databases may not appear.

16. How can you create a table in MySQL?

It’s a straightforward process:

• Run a statement like CREATE TABLE employees (id INT, name VARCHAR(100));.
• Specify each column's name, data type, and any constraints such as PRIMARY KEY (id).
• Afterward, use SHOW TABLES; or DESCRIBE employees; to confirm that the table exists and see its structure.

17. How do you create a database in MySQL Workbench?

We have to follow a few steps to do so:

• Connect to a MySQL instance within MySQL Workbench.
• Go to the “Schemas” tab on the left panel.
• Right-click anywhere in the blank space and choose “Create Schema.”
• Enter a schema name and click “Apply” to finalize creation.
• The schema appears in the list once the action completes.

Also Read: MySQL Workbench Installation and Configuration

18. How do you insert data into a MySQL table?

It can be done by using the INSERT statement. Here’s an example:

INSERT INTO employees (id, name) 
VALUES (1, 'Aditi');

It’s also necessary to:

• List columns explicitly to avoid confusion if new columns get added to the table.
• Ensure that data types match the columns’ definitions.

19. How do you delete data from a table?

We rely on the DELETE statement with a WHERE condition to select specific rows. 

Here’s an example:

DELETE FROM employees 
WHERE id = 1;

If WHERE is omitted, every row in the table is removed, so caution is necessary.

20. How do you update specific rows in a MySQL table?

It’s done by using the UPDATE statement with SET and a WHERE clause. 

Here’s an example:

UPDATE employees 
SET name = 'Bob' 
WHERE id = 1;

Only rows matching the condition will be changed. The rest remain intact.

21. What is the difference between DELETE, TRUNCATE, and DROP?

DELETE removes selected rows from a table. It supports a WHERE clause, which allows partial data deletion and logs each row removal. 

TRUNCATE instantly clears every row in a table. It reclaims storage and finishes more quickly than DELETE but cannot be rolled back.  

DROP removes the entire table definition (or database) along with all of its data. Once DROP runs, the table no longer exists.

22. What is a primary key, and how do you remove it in MySQL?

A primary key uniquely identifies each row in a table. It disallows duplicates or NULL values.

To drop an existing primary key, it’s common to use a command similar to this:

ALTER TABLE students
DROP PRIMARY KEY;

That statement deletes the key and no longer enforces uniqueness on that set of columns.

Also Read: Primary Key in SQL Database: What is, Advantages & How to Choose

23. What is a foreign key, and how is it used?

A foreign key is a column in one table that references the primary key of another table. This setup enforces referential integrity, so any value in that foreign key column must match an existing row in the referenced table. 

In a “classes” and “enrollments” scenario, each row in “enrollments” might have a class_id column that points to the primary key from “classes.” MySQL also lets the designer choose actions like ON DELETE CASCADE or ON UPDATE RESTRICT, controlling how related rows change when the referenced row is removed or updated. 

This is a short example:

CREATE TABLE Students (
  student_id INT PRIMARY KEY,
  student_name VARCHAR(50)
);

CREATE TABLE Enrollments (
  enrollment_id INT PRIMARY KEY,
  student_id INT,
  FOREIGN KEY (student_id) REFERENCES Students(student_id) ON DELETE CASCADE
);

Here, Enrollments relies on Students to ensure that any new enrollment references an existing student. If a matching student row is removed, ON DELETE CASCADE automatically removes related rows in Enrollments.

24. What is a UNIQUE key in MySQL?

A UNIQUE key enforces that no two rows have identical values in that column, though it accepts a single NULL value if allowed otherwise. This prevents unwanted duplicates but does not serve as the main record identifier like a primary key does.

Also Read: What Are The Types of Keys in DBMS? Examples, Usage, and Benefits

25. What is an index in MySQL, and how does it improve query performance?

An index is a supplementary data structure that MySQL uses to find rows faster. When a query filters or sorts on a particular column, MySQL can look up matching entries in the index instead of scanning every row in the table. This advantage grows as the table becomes large because the index lets the server perform fewer comparisons. 

For instance, imagine a table with thousands of records:

CREATE TABLE Employees (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(100),
  city VARCHAR(50)
);

CREATE INDEX idx_emp_name ON Employees(emp_name);

With that index, queries – such as this – complete in fewer steps.:

SELECT *
FROM Employees
WHERE emp_name = 'Sudhir';

The server checks the index for 'Sudhir' instead of reading every row. When new rows are inserted or updated, MySQL also updates the index, which adds a small overhead for write operations. This trade-off is acceptable in most cases because read queries run considerably faster.

Also Read: Create Index in MySQL: MySQL Index Tutorial [2024]

26. What is the difference between CHAR and VARCHAR in MySQL?

CHAR uses a fixed length and pads shorter strings with trailing spaces. It’s suitable for data fields where the size remains consistent. 

VARCHAR adapts to the actual length of stored strings and only consumes the space needed for each value plus a small overhead. This trait makes VARCHAR handy for text fields of unpredictable length.

27. How are INT and DECIMAL different?

INT holds whole numbers like 10 or 5000, so it works for IDs or counts without decimals. DECIMAL allows precise handling of digits to the right of the decimal point, which is key in finance or scenarios that need exact fractional values. 

28. How are DATE and DATETIME different?

DATE only stores the calendar portion (YYYY-MM-DD), while DATETIME includes hours, minutes, and seconds (YYYY-MM-DD HH:MM:SS). DATE is enough for something like birthdays, whereas DATETIME suits data logs that need a precise timestamp.

29. What is the difference between WHERE and HAVING clauses in SQL?

WHERE filters rows before grouping or aggregation and cannot directly handle aggregate functions. HAVING applies conditions after grouping is complete. An example would be grouping employees by department and then using HAVING to exclude departments with fewer than five employees.

30. What is the DISTINCT keyword, and how is it used?

DISTINCT removes duplicate results from a query. If a table of student records has names that appear many times, adding DISTINCT to the SELECT statement returns only one row per name, helping to limit output to unique values. 

Also Read: SQL DISTINCT: A Comprehensive Guide

31. What is the REGEXP operator in MySQL?

REGEXP searches columns using patterns. The statement column_name REGEXP 'pattern' locates rows matching that pattern. An example would be finding students with names starting with “R,” using student_name REGEXP '^R'.

32. What is a stored procedure in MySQL, and how do you create/use one?

A stored procedure is a set of SQL statements saved under a name. Creation typically involves a CREATE PROCEDURE statement with parameters, and usage involves calling it by name. 

For instance, we create one with something like:

DELIMITER $$
CREATE PROCEDURE AddStudent(IN stuId INT, IN stuName VARCHAR(50))
BEGIN
  INSERT INTO Students (student_id, student_name)
  VALUES (stuId, stuName);
END $$
DELIMITER ;

Then we call it with:

CALL AddStudent(101, 'Jaskaran');

That runs all contained statements in one go.

33. What are triggers in MySQL, and how many types of triggers are available?

A trigger is code that runs automatically when insert, update, or delete operations happen on a table. 

MySQL supports six trigger types: Before Insert, After Insert, Before Update, After Update, Before Delete, and After Delete. They help maintain constraints or log activities without manual intervention.

34. What is a transaction in MySQL, and how do you manage it?

A transaction is a series of operations that proceed together or roll back entirely. To manage one, the session starts with START TRANSACTION, performs updates or inserts, and either ends with COMMIT if everything is correct or ROLLBACK if a problem arises.

35. Explain the ACID properties in the context of MySQL transactions.

ACID refers to Atomicity, Consistency, Isolation, and Durability.

• Atomicity: Every operation in the transaction completes or else all are undone.
• Consistency: The database remains valid before and after the transaction, preventing broken foreign keys or invalid data.
• Isolation: Running transactions in parallel won’t cause them to interfere with each other’s uncommitted changes.
• Durability: Once a transaction is committed, it remains stored, even if the server crashes afterward.

36. How do you optimize basic MySQL queries?

Indexes are often the first step since they speed up row lookups. Limiting columns in SELECT, using EXPLAIN to review query plans, and sticking to the smallest suitable data types also help. 

Normalizing the schema prevents redundant lookups, and using the right storage engine for the workload can further refine performance.

37. How do you specify or change the default storage engine in MySQL?

It can be set in the MySQL configuration file under [mysqld] by assigning something like default-storage-engine=InnoDB. Changing it at runtime can involve a SET default_storage_engine=InnoDB; statement, though that only applies to the current session. 

Once the engine is specified, new tables default to that choice unless otherwise stated.

38. How do you handle BLOB data or images in MySQL tables? 

BLOB columns store binary information such as images. The LOAD_FILE function can insert a file’s content directly into such a column, and a standard SELECT retrieves it. 

Some prefer storing only file paths in the table and placing large files on disk for performance reasons, but BLOB columns remain an option when direct binary storage is desired. 

For instance:

INSERT INTO Pictures (pic_id, photo)
VALUES (1, LOAD_FILE('/path/to/AkshayPhoto.jpg'));

This command saves the binary file data in the photo column for quick access or transfer. 

39. What is data collation in MySQL?

Data collation refers to the rules that define how string comparisons and sorting are done. 

For example, a particular collation might treat uppercase and lowercase letters as different or treat accented characters in a specific way. In MySQL, every character set has one or more collations. Choosing an appropriate collation ensures consistent sorting and matching in queries.

40. What is data integrity, and how is it enforced in MySQL?

Data integrity ensures that information remains accurate and consistent throughout the database. 

MySQL enforces it in several ways:

• Constraints: Primary keys, foreign keys, and unique constraints block invalid entries or duplicates.
• Triggers: Automatic routines can validate or transform data before it’s written to the table.
• Transactions: If an operation violates constraints, MySQL rolls back the changes, preventing partial updates from corrupting the data.

41. What is the difference between COUNT(*) and COUNT(column_name)?

COUNT(*) checks every row in the table, including those with NULL values, and returns the number of rows. 

COUNT(column_name) counts only the rows where column_name is not NULL. If that column can be null, the two results might differ because COUNT(column_name) ignores rows with nulls in that column.

42. What are DDL, DML, and DCL commands in SQL?

DDL stands for Data Definition Language, involving commands like CREATE, DROP, and ALTER that define or change table structures. 

DML stands for Data Manipulation Language, covering statements like INSERT, UPDATE, and DELETE, which modify table contents. 

DCL stands for Data Control Language, where commands like GRANT and REVOKE manage permissions and user access.

41 Intermediate-level MySQL Interview Questions for 5 Years Experience Candidates

Professionals with 5 years or more of MySQL experience often tackle tasks that go beyond table creation and simple queries. This section gathers 41 MySQL interview questions aimed at mid-senior level roles, touching on schema alterations, indexing strategies, partitioning, performance tuning, and more detailed security topics.

43. What are MySQL clients and utilities?

A MySQL client is a program that connects to the database for queries or administrative tasks, while a utility is a specialized tool for handling operations like backups or table repairs. 

The main interactive client is mysql, which allows direct typing of queries. Useful utilities include:

• mysqldump for exporting and backing up data
• mysqladmin for various administrative actions like creating users
• mysqlcheck for checking and repairing tables

These tools cover routine maintenance, migrations, and other key administrative tasks.

44. How do you run MySQL in batch mode?

Batch mode involves feeding commands from a file or standard input rather than typing each one interactively. 

For instance:

mysql -u root -p < commands.sql

This command connects to the server and executes all SQL statements in commands.sql. This approach is useful for running maintenance scripts or large sets of queries non-interactively.

45. How do you import/export databases in MySQL?

A common export tool is mysqldump, which creates text files containing SQL statements needed to reconstruct a database. For example:

mysqldump -u root -p sample_db > backup_file.sql

To import, we run:

mysql -u root -p sample_db < backup_file.sql

This command processes every statement in backup_file.sql to restore all tables, data, and routines.

46. How do you check the size of a database or table in MySQL?

One way is to query the information_schema.tables table.

A statement might look like:

SELECT table_name,
       (data_length + index_length) / 1024 / 1024 AS size_in_mb
FROM information_schema.tables
WHERE table_schema = 'sample_db'
ORDER BY size_in_mb DESC;

This query reports the approximate size of each table in megabytes.

47. How do you create an empty table using the structure of another table?

A typical approach uses CREATE TABLE ... SELECT with a false condition. 

For instance:

CREATE TABLE new_students
AS
SELECT *
FROM students
WHERE 1=2;

This command copies all column definitions but selects no rows because WHERE 1=2 is never true, resulting in an empty table with matching structure.

48. How do you create/drop columns with ALTER TABLE?

Columns can be added with statements like:

ALTER TABLE Employees
ADD COLUMN department_id INT;

Dropping a column might look like this:

ALTER TABLE Employees
DROP COLUMN department_id;

These adjustments let a table evolve alongside changing requirements.

49. How do you rename columns and tables in MySQL?

Renaming columns often uses ALTER TABLE with CHANGE or MODIFY. 

For example:

ALTER TABLE Employees
CHANGE COLUMN emp_name full_name VARCHAR(100);

Renaming an entire table can be done in two ways:

Way 1: RENAME TABLE OldTable TO NewTable;

Way 2: ALTER TABLE OldTable RENAME TO NewTable;

50. What are temporary tables, and how do you use them?

Temporary tables store data only for the duration of a session. 

They are created with a simple command:

CREATE TEMPORARY TABLE temp_sales (
   sale_id INT,
   amount DECIMAL(10,2)
);

They vanish automatically once the session ends or if explicitly dropped. They’re helpful for intermediate data calculations that don’t need to persist.

51. How do you insert DATE values in MySQL?

A typical INSERT specifies date columns in YYYY-MM-DD format. 

For example:

INSERT INTO Attendance (student_id, attendance_date)
VALUES (101, '2023-06-15');

If columns accept date/time defaults, some use NOW() or CURRENT_DATE() to store the current date.

52. How do you get the top N rows from a table?

A SELECT query often includes LIMIT to return a certain number of rows, for instance:

SELECT *
FROM Employees
ORDER BY salary DESC
LIMIT 5;

This statement fetches the top five employees with the highest salaries. The ORDER BY clause determines which rows appear first.

53. How do you add foreign keys in MySQL?

A foreign key links rows in one table to the primary key of another, enforcing referential integrity. The typical approach involves an ALTER TABLE statement. 

For example:

ALTER TABLE Enrollments
ADD CONSTRAINT fk_student
FOREIGN KEY (student_id) REFERENCES Students(student_id);

The foreign key constraint ensures that any new enrollment row refers to an existing student row in Students.

54. Why do indexes make queries faster internally?

Indexes act like a lookup directory. When a query runs, MySQL uses the index to locate relevant rows without scanning every record. This speeds up filters and sorts that rely on the indexed columns. Instead of checking each row in a table, MySQL references the index’s organized structure, which leads to quicker access times.

55. What are the different ways to create indexes in MySQL?

One option is to include the index in the CREATE TABLE statement, for instance:

CREATE TABLE Students (
  student_id INT PRIMARY KEY,
  name VARCHAR(50),
  INDEX (name)
);

Alternatively, we can also use ALTER TABLE or CREATE INDEX after the table is made:

ALTER TABLE Students ADD INDEX idx_name (name);

Or:

CREATE INDEX idx_name ON Students (name);

All approaches improve query performance if the name column is part of frequent searches.

56. What’s the difference between clustered and non-clustered indexes?

In MySQL’s context, InnoDB uses a clustered index for the primary key, storing row data in that index’s order. This keeps the actual data organized by the primary key. A non-clustered index sits in a separate structure, storing index keys with row pointers, leaving the row data in the clustered index or table pages. 

The performance benefit is that a clustered index can speed up primary key lookups, while a non-clustered index helps with other frequently searched columns.

57. How does indexing work internally in MySQL?

MySQL generally uses B-tree or similar data structures. When rows are inserted or updated, MySQL also updates the index nodes so that the index remains sorted. 

During a query, the server traverses this tree to find matching key values quickly instead of scanning the entire table. This approach delivers a significant gain in read performance but requires additional overhead when writing to the indexed columns.

58. How do we store product weight and price effectively?

Weight might be FLOAT or DECIMAL, depending on whether approximate or exact precision is needed. FLOAT stores approximate values, while DECIMAL stores precise digits. Price is typically DECIMAL(precision, scale) to keep monetary calculations accurate. 

A common definition might be DECIMAL(10,2), allowing two digits after the decimal point for currency.

59. What are the various relationships in MySQL?

There are three main types:

• A one-to-one relationship links exactly one row in one table to exactly one row in another. 
• A one-to-many relationship connects a single row in one table to multiple rows in another. 
• A many-to-many relationship allows rows in one table to link to many rows in another, typically managed by a junction table that holds foreign keys referencing both tables.

60. What is a JOIN, and what are its different types?

A JOIN combines data from two or more tables based on common columns. It has four major types:

• INNER JOIN returns rows that match in both tables. 
• LEFT JOIN brings all rows from the left table along with matching rows from the right. 
• RIGHT JOIN does the opposite, including all rows from the right plus matches from the left. 
• FULL JOIN returns all rows from both sides if they match or not, though MySQL typically simulates FULL JOIN with a combination of LEFT and RIGHT or by other logical constructs since it’s not natively supported in all versions.

61. How do you join three or more tables in MySQL?

Additional tables are simply chained using consecutive JOIN clauses. 

The statement might look like this:

SELECT e.employee_name, d.department_name, l.location_name
FROM Employees e
JOIN Departments d ON e.department_id = d.department_id
JOIN Locations l ON d.location_id = l.location_id;

Each JOIN must specify matching columns so that MySQL can find related rows among the tables.

62. How do you find duplicate rows in MySQL?

A typical solution is to GROUP BY the fields that should be unique. 

For instance:

SELECT name, COUNT(*) AS count
FROM Students
GROUP BY name
HAVING count > 1;

Rows where count > 1 represent duplicates. The query can be extended to list other columns if needed.

63. How do you define an alias in SQL?

An alias is a temporary name for a column or table. 

For a column, the syntax is:

SELECT column_name AS alias_name
FROM table_name;

Aliases can remove confusion when columns share similar names or display friendlier names in results.

For a table, something like Employees e helps shorten references throughout the query.

Also Read: Mastering SQL Alias: Simplify Queries and Enhance Readability

64. What is a recursive stored procedure?

A recursive stored procedure calls itself until it meets a boundary condition. This pattern solves problems where the solution depends on smaller instances of the same problem. 

For example, a procedure might traverse hierarchical data (like a family tree) by repeatedly invoking itself for child nodes. Special care is needed to avoid infinite loops and to manage performance, especially when recursion gets deep.

65. What are user-defined functions, and how do you create them?

User-defined functions return a single value based on input parameters. They are created with CREATE FUNCTION, specifying a return type and any logic inside a BEGIN…END block. 

For instance:

DELIMITER $$
CREATE FUNCTION CalculateTax(price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
BEGIN
  RETURN price * 0.05;
END $$
DELIMITER ;

A call to SELECT CalculateTax(500.00); would then yield a computed tax. These differ from stored procedures because they can be embedded in SQL statements and must return exactly one value.

66. How do you create and implement triggers beyond the basic concept?

Triggers can include complex conditions or reference old and new values in update operations. For example, a BEFORE UPDATE trigger might check NEW.quantity against OLD.quantity and reject changes if the difference seems invalid. 

In addition, ON DELETE triggers can initiate cascading actions or log changes. Advanced triggers often combine these references to keep business rules enforced at the database level.

67. What is a SAVEPOINT, and how do you use it?

A SAVEPOINT is a marker set within a transaction. The statements that follow can be rolled back to that marker without undoing all prior statements in the transaction. 

One might start a transaction, execute some queries, then issue SAVEPOINT my_mark; before more queries. If something goes wrong, only the changes after that point revert with ROLLBACK TO my_mark; while earlier actions stay intact.

68. How do you optimize queries further for better performance?

Indexing and proper schema design remain crucial. It also helps to measure queries using EXPLAIN and rewrite them if a full table scan appears. Sometimes, rewriting subqueries as joins yields better plans. 

Picking the best storage engine for the workload can also reduce overhead. Additional advanced techniques might include splitting large tables into partitions or using caching layers.

69. What is the concept of correlated subqueries with respect to performance?

A correlated subquery references a value from the outer query, so it runs once per row of the main query. This makes it more demanding since each matching row triggers the inner query again. 

When performance becomes a concern, many convert correlated subqueries to JOIN-based approaches or temporarily store intermediate data to avoid repeated calculations on each row.

70. What is the logical architecture of MySQL?

MySQL’s structure includes a connection layer, an SQL layer for parsing and optimization, and a layer for storage engines such as InnoDB. 

The parser interprets queries, the optimizer plans how to retrieve data, and the storage engine handles row-level operations. This layered design lets different engines handle data management behind a consistent SQL interface.

upGrad’s Exclusive Software and Tech Webinar for you –

SAAS Business – What is So Different?

71. How do you add new users and manage their privileges in MySQL?

A new user is typically created with this command:

CREATE USER 'dhruv'@'localhost' IDENTIFIED BY 'password123';

Then, GRANT statements control access, for example:

GRANT SELECT, INSERT ON school_db.* TO 'dhruv'@'localhost';

These privileges become active right away, though a FLUSH PRIVILEGES; might be required in older MySQL versions. Revoking rights or dropping the user follows a similar pattern.

72. How do you repair corrupted tables in MySQL?

Tools such as mysqlcheck or myisamchk can check and repair MyISAM tables. InnoDB tables might self-recover at startup if the server detects corruption, but if that fails, a restore from backups is sometimes the only option. 

This command is used:

mysqlcheck -u root -p --repair my_database

It examines and repairs tables in the specified database. Because each engine handles data differently, the approach can vary.

73. How do you handle the 'secure-file-priv' option in MySQL?

This setting restricts import and export operations (like LOAD DATA INFILE or SELECT … INTO OUTFILE) to a specific directory. If a file operation triggers an error, adjusting secure-file-priv in the MySQL configuration file is necessary. 

Many set it to an empty value (on non-production systems) or define a secure path so that reading and writing large files is allowed only in that folder.

74. What is the role of redo logs for crash recovery?

Redo logs record changes made to data pages, ensuring that transactions can be replayed if the server crashes before writing data to disk. During a restart, MySQL replays incomplete transactions from the redo logs if they were committed but not yet flushed to the data files. This process upholds ACID guarantees and protects against partial writes.

75. How do you import a CSV file into MySQL?

LOAD DATA INFILE is often the quickest method:

LOAD DATA INFILE '/path/to/CityData.csv'
INTO TABLE Cities
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
(city_id, city_name, population);

If secure-file-priv is set, the CSV must reside in the allowed directory. Alternatively, one might parse the file with a client tool or script, issuing multiple INSERT statements.

76. What is the concept of Multi-Version Concurrency Control (MVCC) in MySQL?

It’s a way for the server to allow multiple concurrent reads and writes without locking rows unnecessarily. Each transaction sees a snapshot of the data as it existed at the start of the transaction. 

InnoDB maintains row versions, so if another user updates a row in the middle of a session, those changes stay hidden until the current session commits or restarts. This design prevents many blocking conflicts.

77. How can you create an empty table that has the same structure as another?

A common solution is using a SELECT statement that retrieves no rows. 

For instance:

CREATE TABLE new_students
AS
SELECT *
FROM students
WHERE 1=0;

This copies column definitions from students but returns zero rows, leaving new_students empty.

78. How do you store and retrieve large objects in MySQL?

BLOB or TEXT fields hold big files such as documents or images. 

An example insert might look like this:

INSERT INTO Documents (doc_id, content)
VALUES (1, LOAD_FILE('/path/to/AparnaDoc.pdf'));

Applications can then retrieve the file using a regular SELECT. However, big BLOB data may reduce performance and bloat backups, so many prefer storing only file paths in the database.

79. How do you create and manage partitions in MySQL?

Partitioning splits a large table into smaller parts but treats them as one logical table. 

A RANGE partition might look like this:

CREATE TABLE Sales (
  sale_id INT,
  sale_date DATE
)
PARTITION BY RANGE (YEAR(sale_date)) (
  PARTITION p2019 VALUES LESS THAN (2020),
  PARTITION p2020 VALUES LESS THAN (2021),
  PARTITION p2021 VALUES LESS THAN (2022)
);

Each partition holds rows fitting its range. This structure can speed up queries if they focus on one partition. Maintenance, such as adding or removing partitions, uses ALTER TABLE commands.”

80. What is a CROSS JOIN, and how does it differ from other joins?

A CROSS JOIN returns the Cartesian product of two tables, producing every combination of rows. It does not require matching columns, unlike INNER or LEFT JOIN. For instance, if there are four rows in one table and five in the other, a CROSS JOIN yields 20 combined rows. It can be rarely used unless all possible row pairs are relevant.

81. What is a Self-Join, and when would you use it?

A Self-Join uses the same table as both source and target. One example is finding an employee’s manager if they’re in the same table. 

The query might alias the table under two names:

SELECT e.name AS Employee, m.name AS Manager
FROM Employee e
JOIN Employee m ON e.manager_id = m.id;

This helps link rows within the table that contain hierarchical or relational data.

82. How do you clear the screen in MySQL’s command-line client?

Older MySQL versions on Windows may not accept a direct clear command. On some systems, SYSTEM CLS or SYSTEM CLEAR works. Another workaround is to exit and reopen the client. 

For Linux or macOS, \! clear can do the trick, sending a clear command to the shell

83. What are federated tables, and how do they work?

Federated tables connect to a remote MySQL server instead of storing rows locally. The local CREATE TABLE statement includes a CONNECTION string that references the remote host, database, and table. 

When a query runs, the local server sends commands to the remote server, which processes them and returns rows. This design lets multiple servers share data seamlessly while keeping each table’s storage in its original location.

17 Advanced MySQL Interview Question and Answers for Senior-Level Roles

Seasoned MySQL developers often tackle larger data volumes and more intricate tasks. These advanced topics include replication methods, high availability solutions, partitioning strategies, detailed transaction handling, and performance tuning at scale.

The 17 MySQL interview questions here highlight deeper architectural insights and complex scenarios that come up in demanding environments. They focus on the challenges senior roles encounter, such as fine-tuning concurrency, handling complex backups, and crafting robust clustering solutions.

84. What is Sharding in MySQL, and what challenges does it introduce?

Sharding splits large tables across multiple servers, often by a key such as region or user group. This reduces the load on any single machine and keeps queries fast for each shard. 

However, it complicates certain operations, including cross-shard joins, global aggregations, and transactions that must span multiple shards. It also adds overhead for data routing logic since queries must be directed to the right shard.

85. What is Scaling in MySQL, and how do you approach it?

Scaling refers to handling greater loads or data volumes. Vertical scaling means upgrading to stronger hardware (more RAM, faster CPU). Horizontal scaling means distributing the data or queries across multiple MySQL instances. 

• Horizontal strategies can include replication, sharding, or load-balanced clusters.
• Vertical scaling is simpler but limited by hardware caps, while horizontal scaling can continue expanding, though it may require rethinking the schema or architecture.

86. How do you thoroughly analyze query execution using EXPLAIN to identify bottlenecks?

EXPLAIN describes the query plan that MySQL chooses. It shows the chosen index for each table, how many rows might be scanned, and the join order. 

A typical step is to look for ‘ALL’ in the ‘type’ column, which signals a full table scan. If that happens unexpectedly, indexing or rewriting the query might help. 

The ‘Extra’ field can show if MySQL is using ‘Using temporary’ or ‘Using filesort,’ which often points to areas for optimization.

87. How do you approach advanced query optimization?

One might rewrite correlated subqueries as derived tables or incorporate window functions (in supported versions). Periodically caching complex joins to create a materialized view can reduce repeated heavy computations. 

Multi-column indexes or covering indexes might speed up queries that filter and sort on multiple fields. Also, reviewing the execution plan regularly helps refine these strategies

88. How does MySQL handle transaction isolation levels, and what are the different levels?

MySQL relies on isolation settings to ensure that concurrent transactions don’t conflict. There are four primary levels:

• READ UNCOMMITTED: Allows dirty reads, so one transaction might view uncommitted changes from another.
• READ COMMITTED: Prevents dirty reads, but repeated queries in the same transaction might see updated values.
• REPEATABLE READ: Ensures that all reads within a transaction see a consistent snapshot. MySQL uses this by default in InnoDB.
• SERIALIZABLE: Forces each transaction to appear as though it runs in sequence, often through additional locking.

89. How do you handle deadlocks in MySQL?

Deadlocks occur when two transactions hold locks that the other needs. MySQL chooses one transaction as a victim and rolls it back. 

The typical fix is keeping transactions short, always locating tables in a consistent order, or reducing isolation where possible. Periodically reviewing logs and using the ‘SHOW ENGINE INNODB STATUS’ can reveal deadlock details.

90. How does MySQL implement Multi-Version Concurrency Control (MVCC)?

InnoDB stores row versions so that each transaction sees a consistent snapshot of the database. Writes create new versions of rows rather than blocking readers. Once a transaction is committed, other sessions can see the changes. This design lowers lock contention among concurrent transactions.

91. What are transaction storage engines, and how do they differ from non-transactional engines?

Transaction engines (like InnoDB) support COMMIT, ROLLBACK, and crash recovery. They keep logs to ensure that partial writes are undone if necessary. Non-transactional engines (like MyISAM) emphasize speed and simplicity but lack full ACID guarantees. Updates in non-transactional engines become final as soon as they’re written.

Info on key differences:

92. How does InnoDB handle data pages, extents, or buffer pools for advanced optimization?

InnoDB manages data in pages (often 16KB) and groups those pages into extents. These pages are cached in the buffer pool, which is memory set aside for frequently accessed data. This approach speeds up reads, as the system avoids going to disk for repeated queries. 

Fine-tuning the buffer pool size can significantly boost performance if it matches the working data set.

93. How do you handle MySQL replication?

MySQL replication generally involves a primary node writing changes to its binary log while one or more replicas read and apply those changes. 

Three common replication modes can be considered:

• Asynchronous Replication: The primary node logs every challenge and moves on without waiting for confirmation from replicas. This method is straightforward, but the replicas can lag behind, which risks data loss if the primary crashes.
• Semi-Synchronous Replication: At least one replica confirms receipt of the log events before the primary commits a transaction. This approach reduces data loss at the cost of added latency.
• Group Replication: A multi-master solution that forms a replication group. Every node processes writes, and the group maintains consistency through consensus. This allows any node to act like a primary but introduces more network overhead and configuration steps.

94. How can you utilize partitioning strategies effectively in MySQL for large datasets?

Partitioning breaks a large table into smaller parts, which can speed up certain queries and maintenance tasks. 

There are many strategies that can be used:

• RANGE partitions split rows based on numeric or date ranges
• LIST targets enumerated sets of values
• HASH uses a hashing function on a column to spread rows evenly
• KEY is similar but uses MySQL’s own hashing. 

Choosing a strategy depends on how the data is queried. For date-oriented data, RANGE is often the simplest. For uniform distribution, HASH or KEY can keep partitions balanced.

95. How do you handle advanced partition pruning for big queries?

Partition pruning eliminates partitions that a query doesn’t need to scan, improving speed. The server checks conditions in the WHERE clause and decides which partitions might contain matching rows.

Having a partition key that aligns closely with frequent filtering columns (like a date range) is crucial because MySQL can skip entire partitions if it sees that all rows in those partitions would be out of range.

96. How do you configure MySQL for replication and high availability?

Replication usually involves setting up a primary server that logs changes in its binary log, while replicas use the log to apply those changes locally. 

The steps include:

• Enabling binary logging (log_bin) on the primary
• Assigning a server ID
• Using CHANGE MASTER TO on the replica. 

Multiple replicas can be deployed for high availability, and a failover mechanism can redirect traffic if the primary fails. Tools like mysqlfailover or third-party solutions can automate that process.

97. How would you implement clustering or Galera Cluster in MySQL for advanced HA scenarios?

A Galera Cluster involves multiple nodes that replicate synchronously. Each node reads and writes the same dataset in real time, so any node can act as the primary. 

Configuration requires installing the Galera plugin and setting parameters such as wsrep_cluster_address. Because writes are synchronous across nodes, conflicts are resolved via optimistic concurrency. This delivers strong consistency at the cost of some overhead if nodes are spread geographically.

98. How can you handle advanced user privilege management and row-based security?

Advanced privilege management allows granular control, for example limiting users to certain columns or sets of commands. 

We can execute statements like GRANT SELECT, UPDATE ON db_name.table_name TO 'user'@'host'; to define privileges precisely. Row-based security isn’t built in by default, though some implement logical row filters at the application level. 

Alternatively, MySQL 8 introduced partial support for resource groups, but for row-level rules, many rely on a combination of triggers, views, or application logic.

99. How do you tune MySQL server configuration in large-scale environments?

Tuning begins with InnoDB’s buffer pool size, which ideally should fit the active dataset in RAM. The query cache is less relevant in newer MySQL versions, but key buffers (for MyISAM) and log file sizes for InnoDB can also matter. 

• Monitoring tools, such as the MySQL Performance Schema or third-party dashboards, help spot bottlenecks. 
• Adjust settings like innodb_buffer_pool_size or innodb_log_file_size incrementally, checking the effect on throughput and memory usage.

Over-allocating can lead to swapping, which undermines performance.

100. How would you approach advanced backups and PITR (Point-In-Time Recovery)?

Advanced backups often combine full dumps with incremental binlog backups. A full backup captures the entire state of the server at a moment in time, while binlog archiving allows step-by-step replay of transactions.

If a mistake occurs, we restore the full backup and replay binlogs up to a specific time or transaction, resulting in point-in-time recovery. Tools like mysqlbinlog help parse the logs.

Commercial or open-source solutions may automate this flow, but the core strategy remains: store complete snapshots plus continuous updates,

Conclusion

Whether you're in search of beginner-level MySQL questions or intermediate-level or advanced questions for senior roles, the 100 MySQL interview questions and answers in this blog will surely help you shine through in your next interview. 

You can flare even better if you’ll stick to the following interview tips:

• Use Real Examples: Whenever possible, describe actual situations where you handled queries, indexes, or replication.
• Focus on Performance Insights: Be ready to explain how to detect and resolve bottlenecks, including the use of EXPLAIN and indexing strategies.
• Know Your Storage Engines: Show that you can pick the right engine and understand transactions or crash recovery when needed.
• Emphasize Data Integrity: Interviewers often look for a robust grasp of constraints, triggers, and transaction isolation.
• Stay Confident in Fundamentals: Even senior roles rely on solid knowledge of simple queries, backups, and user privileges.

If you are curious to learn about SQL and full-stack development, check out IIIT-B & upGrad’s Executive PG Program in Full Stack Software Development program which is created for working professionals and teaches through 7 case studies & projects. For any career-related guidance, book a free demo call with upGrad’s career counseling experts. 

Boost your career with our popular Software Engineering courses, offering hands-on training and expert guidance to turn you into a skilled software developer.

Explore our Popular Software Engineering Courses

Master in-demand Software Development skills like coding, system design, DevOps, and agile methodologies to excel in today’s competitive tech industry.

In-Demand Software Development Skills

Stay informed with our widely-read Software Development articles, covering everything from coding techniques to the latest advancements in software engineering.

Read our Popular Articles related to Software