Skip to main content

MySQL interview for 10+ years of experience

For a MySQL interview for 10+ years of experience, we can expect questions on performance optimization, indexing, query tuning, replication, sharding, transactions, security, and architecture. Here are some key regularly asked questions with answers:


1. MySQL Performance Optimization

Question 1. How do you optimize a slow query in MySQL?

First, let's Check the execution Plan. We can use EXPLAIN to analyze query execution.

EXPLAIN SELECT * FROM orders WHERE status = 'pending';


Indexes: Ensure proper indexes on frequently searched columns.


Fetching Columns: Fetch only necessary columns, do not use select *.


Optimize Joins: Use indexed columns for joins for fast fetching.


Use Query Caching: Enable MySQL query cache.


Partition Large Tables: Use partitioning for high-volume data.


Denormalization: Reduce joins by adding redundant data when necessary.


Optimize WHERE Conditions: Avoid LIKE '%term%' as it prevents index usage.


2. Indexing & Query Execution

Question 2. What are the different types of indexes in MySQL?

Primary Key Index: Unique and clustered index for the primary key.

Unique Index: Ensures unique values in a column.

Full-text Index: Used for text searching (MATCH() AGAINST()).

Composite Index: Index on multiple columns.

Spatial Index: For spatial (GIS) data.

B-Tree Index: Default index type for most engines.

Hash Index: Used in MEMORY tables for fast lookups.


3. Transactions & Concurrency

Question 3. What are ACID properties in MySQL?


Atomicity: Transactions are all or nothing.

Consistency: Ensures data integrity before and after transactions.

Isolation: Transactions operate independently (depends on isolation level).

Durability: Committed transactions persist even after system failure.


You can explain it in detail as below.

ACID is a set of properties that guarantee database transactions are processed reliably. MySQL supports ACID compliance through its various storage engines, most notably InnoDB (which is the default storage engine since MySQL 5.5).


Atomicity

Ensures that all operations in a transaction are completed successfully; if any operation fails, the entire transaction is rolled back

In MySQL, statements like COMMIT and ROLLBACK help manage transaction atomicity

Consistency

Ensures that a transaction can only bring the database from one valid state to another

Maintains database integrity constraints, cascades, triggers, and any defined rules

Isolation

Ensures that concurrent execution of transactions leaves the database in the same state as if the transactions were executed sequentially

MySQL offers different isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (default), and SERIALIZABLE

Durability

Ensures that once a transaction is committed, it remains committed even in case of system failure.

MySQL achieves this through write-ahead logging and data flushing techniques.

MySQL have different storage engines and they have different ACID compliance characteristics:


InnoDB is  Fully ACID-compliant. MyISAM is not ACID-compliant (lacks transaction support).MEMORY and ARCHIVE  Not ACID-compliant 

To use ACID transactions in MySQL, you should follow the syntax:


            START TRANSACTION; 


            -- SQL operations here


            COMMIT; -- or ROLLBACK if something fails 


Question 4. What are the different transaction isolation levels in MySQL?


READ UNCOMMITTED: No isolation, dirty reads possible.


READ COMMITTED: No dirty reads, but non-repeatable reads can occur.


REPEATABLE READ (Default in MySQL): No dirty or non-repeatable reads.


SERIALIZABLE: Highest isolation, full locking, worst performance.


4. MySQL Replication & High Availability

Question 5. What are different MySQL replication types?

 

Asynchronous Replication: Default MySQL replication (Master-Slave).


Semi-Synchronous Replication: Slower but guarantees at least one slave has the latest data.


Group Replication: Multi-master replication for high availability.


GTID (Global Transaction ID) Replication: Ensures reliable replication.


Q6. How do you set up Master-Slave replication in MySQL?

A:


Enable Binary Logging on Master:

 ini


[mysqld]


log-bin=mysql-bin


server-id=1



Create a Replication User:

 

CREATE USER 'replica'@'%' IDENTIFIED WITH mysql_native_password BY 'password';


GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';



Get Master's Current Log Position:

 

SHOW MASTER STATUS;



Configure Slave Server:

 

[mysqld]


server-id=2



Start Replication on Slave:


CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='replica', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;


START SLAVE;


SHOW SLAVE STATUS\G;



5. MySQL Partitioning & Sharding

Q7. What are different types of partitioning in MySQL?

A:


RANGE Partitioning: Based on value ranges.

 

PARTITION BY RANGE (year(order_date)) (


    PARTITION p0 VALUES LESS THAN (2020),


    PARTITION p1 VALUES LESS THAN (2023)


);



LIST Partitioning: Based on a predefined list.


HASH Partitioning: Distributes rows across partitions using a hash function.


KEY Partitioning: Like hash but uses MySQL’s internal hashing.


Q8. What is database sharding, and how is it different from partitioning?

A:


Partitioning: Splitting a single table into multiple physical partitions but managed within one MySQL instance.


Sharding: Distributing data across multiple MySQL instances (databases). Each shard is an independent database, and queries are directed based on data distribution.


6. MySQL Security

Q9. How do you secure a MySQL database?

A:


Disable remote root login:

 

UPDATE mysql.user SET host='localhost' WHERE user='root';



Use Strong Passwords & Authentication: Enforce mysql_native_password or caching_sha2_password.


Grant Least Privilege: Use GRANT instead of giving full privileges.

 

GRANT SELECT, INSERT ON mydb.* TO 'user'@'host';



Enable Firewall & IP Whitelisting.


Encrypt Connections (SSL/TLS).


Enable Audit Logging: Use MySQL Enterprise Audit.


7. MySQL Storage Engines

Q10. What are the differences between InnoDB and MyISAM?

Feature


InnoDB


MyISAM


Transactions


✅ Yes (ACID-compliant)


❌ No Transactions


Foreign Keys


✅ Supports Foreign Keys


❌ No Foreign Keys


Locking


✅ Row-level Locking


❌ Table-level Locking


Performance


🚀 Better for Read-Write


🔥 Faster for Read-Heavy Workloads


Crash Recovery


✅ Yes (Uses Redo Logs)


❌ No (Might Corrupt Data)


8. Advanced MySQL Topics

Q11. What is a Common Table Expression (CTE) in MySQL?

A:

 CTEs simplify complex queries using WITH:




WITH recent_orders AS (


   SELECT * FROM orders WHERE order_date > '2024-01-01'


)


SELECT * FROM recent_orders WHERE status = 'pending';



Q12. How does MySQL handle Deadlocks?

A:


A deadlock occurs when two transactions hold locks on resources the other needs.


MySQL automatically detects deadlocks and rolls back one transaction.


Use SHOW ENGINE INNODB STATUS; to debug deadlocks.


9. Backup & Recovery

Q13. How do you take a MySQL backup and restore it?

A:


Backup:

 sh


mysqldump -u root -p mydatabase > backup.sql



Restore:

 sh


mysql -u root -p mydatabase < backup.sql



10. MySQL JSON & NoSQL Features

Q14. How does MySQL handle JSON data?

A:

 MySQL supports JSON with functions like:


sql




SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');



 Can I index non unique columns

Yes, you can index non-unique columns in MySQL. In fact, most indexes are created on non-unique columns to improve query performance.


Types of Indexes on Non-Unique Columns



Single-Column Index (Non-Unique)




Helps speed up searches even if values are repeated.


Example:


CREATE INDEX idx_status ON orders(status);



Improves performance of:


SELECT * FROM orders WHERE status = 'pending';






Composite Index (Multiple Columns, Non-Unique)




Indexing multiple columns together.


Example:

CREATE INDEX idx_status_date ON orders(status, order_date);



Optimized for queries like:

SELECT * FROM orders WHERE status = 'pending' AND order_date > '2024-01-01';






Full-Text Index (For Searching Large Text Fields)




Used in TEXT or VARCHAR columns for searching.


Example:


CREATE FULLTEXT INDEX idx_description ON products(description);



Improves performance of:


SELECT * FROM products WHERE MATCH(description) AGAINST ('laptop');






Covering Index (Helps Avoid Extra Reads from Table)




If a query selects only indexed columns, MySQL fetches data directly from the index instead of reading the table.


Example: 

CREATE INDEX idx_name_age ON users(name, age);

Optimized for:

SELECT name, age FROM users WHERE name = 'John';







Things to Remember


✅ Indexes on non-unique columns improve query performance but do not enforce uniqueness.


✅ Too many indexes slow down writes (INSERT, UPDATE, DELETE) since indexes need to be updated.


✅ Use EXPLAIN to check if the index is being used in queries.


✅ For high-duplicate values (low cardinality), indexes may not be effective. Consider partitioning instead.


Final Thoughts

For a 10+ years experienced role, focus on:


Performance tuning & indexing


Replication & high availability


Transactions & isolation levels


Sharding & partitioning


Security & best practices


Would you like a custom mock interview for practice? 🚀


Comments

Post a Comment

Popular posts from this blog

MySQL's ACID compliance

Mysql acid compliance ACID is an acronym that stands for four key properties of database transactions: Atomicity Ensures that a transaction is treated as a single, indivisible unit of work Either all operations within a transaction are completed successfully, or none are If any part of the transaction fails, the entire transaction is rolled back to its previous state Prevents partial updates that could leave the database in an inconsistent state Consistency Guarantees that a transaction brings the database from one valid state to another valid state All data written to the database must adhere to defined rules, constraints, cascades, triggers, and other database integrity mechanisms Ensures that any transaction will not break the database's predefined rules Isolation Determines how and when changes made by one transaction become visible to other transactions Prevents interference between concurrent transactions MySQL provides different isolation levels: Read Uncommitted Read Commit...
1 comment
Read more

PHP OOPs exercise - Basic Oops

  Here are key PHP OOP (Object-Oriented Programming) exercise questions with solutions: Basic Class and Object Exercise: // Create a simple bank account class class BankAccount {     private $accountNumber;     private $balance;     public function __construct($accountNumber, $initialBalance = 0) {         $this->accountNumber = $accountNumber;         $this->balance = $initialBalance;     }     public function deposit($amount) {         if ($amount > 0) {             $this->balance += $amount;             return true;         }         return false;  ...
Post a Comment
Read more

Interview questions for Senior PHP Developer particle41.com

1.Self Introduction 2.Basic questions on session and cookie. 3.Where is session stored? 4.Difference between Cookie and session. 5.Will there be any session before session start? 6.Post Max execution time.How can we modify it? 7.We have a string, "BJFSJK".Without any php function reverse it with half the string length.   To reverse the string with half the string length without using any PHP functions, you can implement a simple algorithm to achieve the desired result. Here's how you can do it: Initialize two pointers, one at the beginning of the string and the other at the midpoint of the string. Swap characters between these two pointers iteratively, moving the pointers towards each other until they meet or cross each other. Here's the PHP code to implement this algorithm:  <?php $string = "ABC100"; $length = strlen($string); // Calculate the midpoint of the string $midpoint = (int)($length / 2); // Initialize pointers $start = 0; $end = $length - 1; //...
Post a Comment
Read more