Raju Ginni

wordpress tutorials seo hosting etc

You are here: Home / MySQL Tutorial (create,connect database, update tables, export etc) Documentation & TIPS / mysql interview questions and answers fresher to experience

mysql interview questions and answers fresher to experience

 some commonly asked MySQL interview questions and answers, categorized by difficulty level:

1. Basic MySQL Interview Questions

Q1: What is MySQL?

A: MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) for managing data.

Q2: How do you check the MySQL version?

A: Use one of the following commands:

sql
SELECT VERSION();

or in Linux CLI:

bash
mysql --version

Q3: How do you create a database in MySQL?

A: Use the CREATE DATABASE command:

sql
CREATE DATABASE mydb;

Q4: How do you list all databases?

A: Use:

sql
SHOW DATABASES;

Q5: How do you create a table in MySQL?

A: Example:

sql
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(100) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. Intermediate MySQL Interview Questions

Q6: What are MySQL storage engines?

A: MySQL supports different storage engines:

  • InnoDB – Supports transactions, foreign keys, ACID compliance.
  • MyISAM – Faster reads, but no transactions or foreign keys.
  • Memory – Stores data in RAM for fast access.
  • CSV – Stores data in plain-text CSV files.

Check storage engine:

sql
SHOW ENGINES;

Q7: What is the difference between CHAR and VARCHAR?

Feature CHAR(n) VARCHAR(n)
Storage Fixed-length Variable-length
Speed Faster Slightly slower
Usage Small fixed-size data Variable-length text

Example:

sql
name CHAR(10); -- Always 10 bytes
email VARCHAR(100); -- Only uses required space

Q8: How do you find duplicate records in a table?

A: Using GROUP BY and HAVING:

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

Q9: What is an Index in MySQL?

A: An index improves search speed but slows down writes. Types:

  • Primary Index (Primary Key)
  • Unique Index (UNIQUE)
  • Full-Text Index (for text search)
  • Composite Index (multi-column index)

Example:

sql
CREATE INDEX idx_name ON users(name);

Check indexes:

sql
SHOW INDEX FROM users;

Q10: What is the difference between INNER JOIN, LEFT JOIN, and RIGHT JOIN?

Join Type Description
INNER JOIN Returns matching rows from both tables.
LEFT JOIN Returns all rows from the left table and matching rows from the right.
RIGHT JOIN Returns all rows from the right table and matching rows from the left.

Example:

sql
SELECT users.name, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

3. Advanced MySQL Interview Questions

Q11: What are ACID properties in MySQL?

A: ACID ensures database reliability:

  1. Atomicity – Transactions are all-or-nothing.
  2. Consistency – The database remains valid before and after transactions.
  3. Isolation – Transactions do not interfere with each other.
  4. Durability – Changes persist even after system failure.

InnoDB supports ACID; MyISAM does not.

Q12: What is the difference between DELETE, TRUNCATE, and DROP?

Command Effect
DELETE Deletes specific rows (can use WHERE).
TRUNCATE Removes all rows but keeps the table structure.
DROP Deletes the entire table (schema + data).

Example:

sql
DELETE FROM users WHERE id = 10; -- Delete one row
TRUNCATE TABLE users; -- Delete all rows, reset auto-increment
DROP TABLE users; -- Remove table permanently

Q13: How do you optimize a slow MySQL query?

A: Optimization techniques:

  • Use EXPLAIN to analyze query execution:
    sql
    EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
  • Add Indexes for faster lookups.
  • Use LIMIT to fetch only required rows.
  • Avoid SELECT *, specify needed columns.
  • Optimize JOINs and avoid unnecessary subqueries.
  • Use caching (Redis, Memcached).

Q14: How do you perform database replication in MySQL?

A: Replication allows automatic copying of data:

  • Master-Slave Replication (one primary, multiple read replicas)
  • Master-Master Replication (both can write)
  • Group Replication (multi-primary setup)

Basic steps:

  1. Enable binary logging on the master (my.cnf):
    ini
    [mysqld]
    log-bin=mysql-bin
    server-id=1
  2. Configure a slave with CHANGE MASTER TO.
  3. Start replication:
    sql
    START SLAVE;

Q15: What is the difference between NOW(), CURDATE(), and CURRENT_TIMESTAMP?

Function Description
NOW() Returns current date and time (YYYY-MM-DD HH:MM:SS).
CURDATE() Returns only the current date (YYYY-MM-DD).
CURRENT_TIMESTAMP Same as NOW(), but for TIMESTAMP fields.

Example:

sql
SELECT NOW(), CURDATE(), CURRENT_TIMESTAMP;

4. MySQL Query Challenges

Q16: How do you find the second-highest salary?

sql
SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Q17: How do you get the nth highest salary?

sql
SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET (n-1);

or using DENSE_RANK():

sql
SELECT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
FROM employees
) as ranked
WHERE rnk = n;

Q18: How do you swap values in two columns without using a third variable?

sql
UPDATE table_name
SET column1 = column2, column2 = column1;

real-world MySQL scenarios and how to handle them:

1. Handling Millions of Records Efficiently

Scenario:

A large e-commerce website has millions of orders. Searching for a specific order is very slow.

Solution:

  • Use Indexing: Add indexes on frequently searched columns.
    sql
    CREATE INDEX idx_order_id ON orders(order_id);
  • Partition the Table: Use range partitioning by year.
    sql
    CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_date DATE NOT NULL
    ) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024)
    );
  • Use EXPLAIN to Analyze Query Performance:
    sql
    EXPLAIN SELECT * FROM orders WHERE order_id = 12345;

2. Preventing Duplicate Entries

Scenario:

Users accidentally submit the same form twice, creating duplicate records.

Solution:

  • Use UNIQUE constraint:
    sql
    CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE
    );
  • Use INSERT IGNORE to prevent duplicate inserts:
    sql
    INSERT IGNORE INTO users (email) VALUES ('test@example.com');
  • Use ON DUPLICATE KEY UPDATE:
    sql
    INSERT INTO users (email, name)
    VALUES ('test@example.com', 'John')
    ON DUPLICATE KEY UPDATE name = 'John';

3. Recovering Accidentally Deleted Data

Scenario:

A developer accidentally deleted a table’s data without a backup.

Solution:

  • Check MySQL Binary Logs (if enabled):
    sql
    SHOW BINARY LOGS;
  • Recover using mysqlbinlog:
    bash
    mysqlbinlog /var/log/mysql-bin.000001 | mysql -u root -p
  • Enable binlog_format = ROW in MySQL to allow rollback via logs.

4. Managing High Traffic on a Website

Scenario:

A social media website experiences traffic spikes, slowing down the database.

Solution:

  • Use Read Replicas (Replication)
    • Set up Master-Slave Replication to distribute read queries.
    ini
    [mysqld]
    log-bin=mysql-bin
    server-id=1
    • Connect read queries to the slave.
    sql
    SELECT * FROM users WHERE id = 12345 /* Use slave */;
  • Enable Query Caching (Redis or Memcached)
    python
    import redis
    cache = redis.Redis()
    key = "user:123"
    user = cache.get(key)
    if not user:
    user = db.query("SELECT * FROM users WHERE id = 123")
    cache.set(key, user, ex=3600) # Cache for 1 hour
  • Use Connection Pooling (MySQL Pool)
    js
    const mysql = require('mysql2');
    const pool = mysql.createPool({ host: 'localhost', user: 'root', database: 'test', connectionLimit: 10 });

5. Calculating Monthly Active Users (MAU)

Scenario:

You need to generate a monthly active users (MAU) report.

Solution:

Use DISTINCT and DATE_FORMAT():

sql
SELECT DATE_FORMAT(last_login, '%Y-%m') AS month, COUNT(DISTINCT user_id) AS mau
FROM user_activity
WHERE last_login >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
GROUP BY month;

6. Handling Soft Deletions Instead of Hard Deletes

Scenario:

You need to delete users but keep their data for compliance reasons.

Solution:

Use a deleted_at column instead of DELETE:

sql
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL;

UPDATE users SET deleted_at = NOW() WHERE id = 123;

SELECT * FROM users WHERE deleted_at IS NULL;

7. Avoiding Deadlocks in High-Concurrency Transactions

Scenario:

A banking app allows multiple users to transfer money, leading to deadlocks.

Solution:

  • Always Lock in the Same Order:
    sql
    START TRANSACTION;
    SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    COMMIT;
  • Use NOWAIT or SKIP LOCKED to avoid waiting indefinitely:
    sql
    SELECT * FROM orders WHERE status = 'pending' FOR UPDATE SKIP LOCKED;

8. Paginating Large Datasets Efficiently

Scenario:

A news website paginates articles, but OFFSET is slow for large datasets.

Solution:

Use Keyset Pagination instead of OFFSET:

sql
SELECT * FROM articles WHERE id > 500 ORDER BY id ASC LIMIT 10;

Instead of:

sql
SELECT * FROM articles ORDER BY id ASC LIMIT 10 OFFSET 500; -- SLOW

9. Fixing Slow JOIN Queries

Scenario:

A JOIN query between orders and users takes too long.

Solution:

  • Check EXPLAIN output:
    sql
    EXPLAIN SELECT * FROM orders JOIN users ON orders.user_id = users.id;
  • Create an Index on user_id:
    sql
    CREATE INDEX idx_user_id ON orders(user_id);

10. Finding the Most Popular Products

Scenario:

Find the top 5 most sold products in the last 30 days.

Solution:

Use GROUP BY and ORDER BY:

sql
SELECT product_id, COUNT(*) AS sales_count
FROM orders
WHERE order_date >= NOW() - INTERVAL 30 DAY
GROUP BY product_id
ORDER BY sales_count DESC
LIMIT 5;

Final Thoughts

These real-world MySQL scenarios cover performance tuning, data recovery, replication, concurrency, and scalability.