MySQL Interview Questions and Answers (2026 Edition)

MySQL Fundamentals & SQL Basics

1. What is MySQL, and what is its relationship with SQL?

MySQL is an open-source Relational Database Management System (RDBMS). It's widely used for web applications and is known for its speed, reliability, and ease of use. SQL (Structured Query Language) is the standard language used to communicate with and manage relational databases, including MySQL. MySQL interprets and executes SQL commands to perform operations like creating tables, inserting data, querying information, and modifying database structures. Without SQL, interacting with a MySQL database would be impractical.

2. Explain the ACID properties in the context of database transactions.

ACID is an acronym representing four key properties that guarantee reliable transaction processing in databases: Atomicity, Consistency, Isolation, and Durability.

• Atomicity: A transaction is treated as a single, indivisible unit. Either all its operations are completed successfully, or none are.
• Consistency: A transaction brings the database from one valid state to another, preserving all defined rules and constraints.
• Isolation: Concurrent transactions execute independently, appearing as if they are processed sequentially, preventing interference.
• Durability: Once a transaction is committed, its changes are permanent and survive system failures. These properties ensure data integrity and reliability.

3. Describe the different types of relationships between tables in a database.

Relational databases model real-world entities and their connections through table relationships.

• One-to-One: A single record in Table A is related to a single record in Table B. This is less common but can be used to split a wide table or secure sensitive data.
• One-to-Many (or Many-to-One): A single record in Table A can be related to multiple records in Table B, but a record in Table B is related to only one record in Table A. This is the most common relationship type.
• Many-to-Many: Multiple records in Table A can be related to multiple records in Table B. This requires an intermediate "junction" or "associative" table that contains foreign keys from both tables.

4. What is Normalization? Explain the first three normal forms.

Normalization is a database design technique to organize tables efficiently, reduce data redundancy, and improve data integrity.

• 1NF (First Normal Form): Each column must contain atomic (indivisible) values, and there are no repeating groups of columns.
• 2NF (Second Normal Form): Must be in 1NF and all non-key attributes must be fully dependent on the primary key. This applies primarily to composite primary keys.
• 3NF (Third Normal Form): Must be in 2NF and all non-key attributes must not depend on other non-key attributes (i.e., no transitive dependencies). Higher normal forms (BCNF, 4NF, 5NF) exist but 3NF is often sufficient for practical applications.

5. Differentiate between DDL, DML, DCL, and TCL commands with examples.

These categories classify SQL commands based on their function:

• DDL (Data Definition Language): Defines and manages database structures. Examples: CREATE, ALTER, DROP.
  sql

  CREATE TABLE Products (
      ProductID INT PRIMARY KEY,
      ProductName VARCHAR(100)
  );
  

• DML (Data Manipulation Language): Manages data within database objects. Examples: INSERT, UPDATE, DELETE, SELECT.
  sql

  INSERT INTO Products (ProductID, ProductName) VALUES (1, 'Laptop');
  

• DCL (Data Control Language): Manages permissions and access control. Examples: GRANT, REVOKE.
  sql

  GRANT SELECT ON Products TO 'app_user'@'localhost';
  

• TCL (Transaction Control Language): Manages transactions. Examples: COMMIT, ROLLBACK, SAVEPOINT.
  sql

  START TRANSACTION;
  UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
  COMMIT;
  

6. Explain the difference between DELETE, TRUNCATE, and DROP statements.

-- DELETE example
DELETE FROM Customers WHERE CustomerID = 101;

-- TRUNCATE example
TRUNCATE TABLE Orders;

-- DROP example
DROP TABLE Products;

Indexing & Query Optimization

7. What is an index in MySQL, and why is it used?

An index in MySQL is a special lookup table that the database search engine can use to speed up data retrieval operations on a database table. Think of it like an index in a book; instead of reading every page, you look up the topic in the index to find relevant pages quickly. Indexes are created on one or more columns of a table. While they improve query performance for SELECT statements, they can slow down INSERT, UPDATE, and DELETE operations because the index itself must also be updated.

8. Explain the different types of indexes in MySQL (Primary, Unique, B-Tree).

• Primary Key Index: A special UNIQUE index that identifies each row in a table. A table can have only one primary key, and its columns must be NOT NULL. It ensures entity integrity.
• Unique Index: Ensures that all values in the indexed column(s) are distinct. Unlike a primary key, a table can have multiple unique indexes, and unique columns can contain NULL values (though usually only one NULL is allowed).
• B-Tree Index (Balanced Tree): The most common index type in MySQL for InnoDB and MyISAM. It's efficient for a wide range of queries, including equality searches, range searches (<, >, BETWEEN), and ORDER BY clauses. It organizes data in a tree structure, allowing quick traversal to find desired values.

-- Creating a Primary Key index
CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50) UNIQUE
);

-- Creating a Unique index
ALTER TABLE Users ADD UNIQUE INDEX idx_username (Username);

-- Creating a B-Tree index (default for many types)
CREATE INDEX idx_lastname ON Customers (LastName);

9. What is the purpose of EXPLAIN in MySQL? How do you interpret its output?

The EXPLAIN statement provides information about how MySQL executes a SELECT statement. It's a crucial tool for query optimization. Its output describes the query execution plan, including:

• id: The query identifier.
• select_type: Type of SELECT query (e.g., SIMPLE, SUBQUERY, DERIVED).
• table: The table being accessed.
• type: The join type (e.g., ALL for full table scan, ref for index lookup, const for primary key lookup). This is vital for performance analysis.
• possible_keys: Indexes MySQL could use.
• key: The index MySQL actually chose.
• rows: Estimated number of rows MySQL must examine.
• Extra: Additional information (e.g., Using filesort, Using temporary, Using index). Understanding EXPLAIN helps identify bottlenecks and improve query efficiency.

EXPLAIN SELECT * FROM Products WHERE Price > 100 ORDER BY ProductName;

10. How can you optimize a slow query in MySQL? List common strategies.

Optimizing slow queries involves identifying bottlenecks and applying appropriate strategies:

1. Use EXPLAIN: Analyze the query execution plan to understand why it's slow.
2. Add/Optimize Indexes: Ensure relevant columns in WHERE, JOIN, ORDER BY, and GROUP BY clauses have appropriate indexes. Avoid over-indexing.
3. Rewrite Queries: Simplify complex joins, use JOIN instead of subqueries where appropriate, avoid SELECT *.
4. Avoid Full Table Scans: Ensure WHERE clauses can utilize indexes effectively.
5. Limit Results: Use LIMIT with ORDER BY for pagination.
6. Optimize JOIN clauses: Ensure JOIN conditions are indexed.
7. Consider GROUP BY and ORDER BY: These can be costly; ensure indexes support them or add covering indexes.
8. Avoid LIKE %value: Leading wildcards (%) prevent index usage.
9. Denormalization (cautiously): For read-heavy systems, strategically duplicating data can sometimes improve query performance by reducing joins.
10. Database Configuration: Tune server parameters like innodb_buffer_pool_size.

11. What is a covering index, and why is it beneficial?

A covering index is an index that includes all the columns required by a query, meaning the database can retrieve all necessary data directly from the index without needing to access the actual table data. This is incredibly beneficial because it avoids reading data from the data pages, which is often a disk I/O operation. By staying entirely within the index, covering indexes significantly reduce I/O and improve query performance, especially for queries that select only a few columns.

-- Query needing ProductPrice and CategoryID
SELECT ProductPrice, CategoryID FROM Products WHERE ProductID = 123;

-- Covering index for the above query
CREATE INDEX idx_product_price_category ON Products (ProductID, ProductPrice, CategoryID);

Data Integrity & Concurrency

12. Explain different types of constraints in MySQL (NOT NULL, UNIQUE, DEFAULT, CHECK, FOREIGN KEY).

Constraints are rules enforced on data columns to limit the type of data that can go into a table, ensuring data accuracy and reliability.

• NOT NULL: Ensures that a column cannot store NULL values.
• UNIQUE: Ensures that all values in a column (or set of columns) are different.
• PRIMARY KEY: A combination of NOT NULL and UNIQUE, identifying each record uniquely.
• FOREIGN KEY: Establishes a link between two tables, ensuring referential integrity by linking a column (or columns) in one table to the primary key of another table.
• DEFAULT: Provides a default value for a column when no value is specified during an INSERT.
• CHECK: Ensures that all values in a column satisfy a specific condition.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATE DEFAULT CURRENT_DATE,
    Quantity INT CHECK (Quantity > 0),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

13. Describe the concept of transactions and their importance.

A transaction is a single logical unit of work that performs one or more operations on a database. Its primary importance lies in maintaining data integrity and consistency, especially in environments with concurrent users or complex operations. Transactions ensure that either all changes within the unit of work are successfully applied (committed) or none are (rolled back) if any part fails. This adherence to ACID properties prevents partial updates and inconsistent states, which are critical in applications like banking or inventory management.

14. What are the different isolation levels in MySQL (InnoDB), and how do they impact concurrency and consistency?

InnoDB supports four transaction isolation levels, which define how one transaction's changes are visible to others, impacting concurrency and consistency tradeoffs:

• READ UNCOMMITTED: Lowest isolation. Allows "dirty reads" (reading uncommitted changes). Highest concurrency, lowest consistency.
• READ COMMITTED: Prevents dirty reads. Transactions only see changes that have been committed. Allows "non-repeatable reads" (different results for the same query within a transaction).
• REPEATABLE READ (Default in MySQL): Prevents dirty reads and non-repeatable reads. However, it can suffer from "phantom reads" (new rows appearing in subsequent queries). Uses MVCC for consistency.
• SERIALIZABLE: Highest isolation. Prevents dirty reads, non-repeatable reads, and phantom reads. Transactions execute strictly sequentially. Lowest concurrency, highest consistency.

Choosing the right level balances performance and data integrity needs.

15. Explain row-level locking and table-level locking. When is each used?

• Row-level locking: Locks only the specific rows being accessed or modified. Other transactions can access or modify different rows in the same table concurrently. This significantly increases concurrency and is the default behavior for InnoDB. It's used when fine-grained control is needed, such as in OLTP systems with many concurrent writes.
• Table-level locking: Locks the entire table, preventing any other transaction from accessing or modifying any part of that table until the lock is released. This reduces concurrency but has lower overhead than row-level locking. MyISAM uses table-level locking. It's suitable for read-heavy tables or when performing bulk operations (e.g., ALTER TABLE) where concurrency isn't a primary concern.

Storage Engines & Architecture

16. Compare and contrast InnoDB and MyISAM storage engines. When would you choose one over the other?

Choice:

• InnoDB (Default for MySQL 5.5+): Almost always preferred for transactional applications requiring data integrity, high concurrency, and crash recovery.
• MyISAM: Rarely recommended for new applications unless a specific niche (e.g., purely read-only historical data where full-text search is critical and simplicity is paramount, or spatial indexing prior to InnoDB's robust support) is identified. Its lack of transactions and row-level locking makes it unsuitable for most modern web applications.

17. Describe the main components of MySQL's architecture.

MySQL's architecture generally consists of two main layers:

1. MySQL Server: This is the core daemon (mysqld) that handles all database operations. It comprises:
   • Connection Handler: Manages client connections, authentication, and security.
   • Query Parser: Parses and validates SQL queries.
   • Optimizer: Determines the most efficient execution plan for a query (e.g., which indexes to use, join order).
   • Cache & Buffers: Manages various caches (e.g., query cache, key buffer, InnoDB buffer pool) to reduce disk I/O.
   • Pluggable Storage Engine API: Allows different storage engines (like InnoDB, MyISAM) to be used.
2. Storage Engines: These are responsible for how data is stored on disk and how it's retrieved. Each engine has its own way of managing data, indexing, and transactional features. InnoDB is the default and most widely used.

18. What is replication in MySQL, and why is it important?

Replication in MySQL is the process of copying data from one MySQL database server (the "master" or "source") to one or more other MySQL database servers (the "replicas" or "read replicas"). It's primarily used for:

• High Availability (HA): Provides redundancy. If the master fails, a replica can be promoted.
• Scalability: Distributes read workloads across multiple replicas, reducing the load on the master (read scaling).
• Data Backup/Disaster Recovery: Replicas can serve as backups or be geographically dispersed for disaster recovery.
• Reporting: Run intensive reports on replicas without impacting master performance. Replication typically happens asynchronously using binary logs (binlog).

19. Explain how MySQL replication works (binary log, relay log, SQL thread).

MySQL replication works by the master recording all data changes in its binary log (binlog).

1. Master:
   • The binlog records all DDL and DML statements (or row-based changes) that modify data.
   • An I/O thread on the replica connects to the master and requests the binary log events.
2. Replica:
   • The I/O thread reads the master's binary log events and saves them into a local file called the relay log.
   • The SQL thread reads events from the relay log and executes them on the replica's database, applying the changes. This process ensures that the replica's data eventually becomes consistent with the master's.

-- On Master:
SHOW MASTER STATUS;
-- Look for File and Position

-- On Replica:
CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='replication_user',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='master-bin.000001', -- From master status
    MASTER_LOG_POS=123;                 -- From master status

START SLAVE;
SHOW SLAVE STATUS\G;

Advanced Features & Performance Tuning

20. What are stored procedures and functions in MySQL? What are their advantages?

• Stored Procedure: A named block of SQL statements stored in the database, which can be called and executed multiple times. It can accept input parameters and return output parameters. Procedures perform actions and do not necessarily return a value.
• Stored Function: Similar to a procedure, but it must return a single scalar value. Functions can be used within SQL statements (like SELECT or WHERE clauses). Advantages:

1. Performance: Reduced network traffic (only the call is sent), pre-compiled execution plans.
2. Reusability: Code once, use many times.
3. Security: Grant users permission to execute procedures/functions without direct table access.
4. Data Integrity: Enforce complex business logic consistently.
5. Encapsulation: Hide complex logic from application code.

-- Example Stored Procedure
DELIMITER //
CREATE PROCEDURE GetCustomerOrders(IN cust_id INT)
BEGIN
    SELECT * FROM Orders WHERE CustomerID = cust_id;
END //
DELIMITER ;

CALL GetCustomerOrders(101);

21. Explain the purpose of triggers in MySQL.

A trigger is a special type of stored procedure that is automatically executed (fired) when a specific event occurs on a table. These events include INSERT, UPDATE, or DELETE operations. Triggers can be set to fire before or after the event. Purpose:

• Enforce Complex Business Rules: Beyond what standard constraints can handle.
• Maintain Data Integrity: Automatically update related tables or perform validations.
• Auditing: Log changes to a table (e.g., who changed what, when).
• Synchronization: Keep related data synchronized across tables. However, overuse can lead to hidden logic and debugging difficulties.

-- Example Trigger for auditing
DELIMITER //
CREATE TRIGGER before_product_update
BEFORE UPDATE ON Products
FOR EACH ROW
BEGIN
    INSERT INTO Product_Audit (ProductID, OldPrice, NewPrice, ChangeDate)
    VALUES (OLD.ProductID, OLD.Price, NEW.Price, NOW());
END //
DELIMITER ;

22. What is database partitioning in MySQL? What are its benefits?

Database partitioning is a technique where a large table is divided into smaller, more manageable physical pieces called partitions, based on a defined rule (e.g., range, list, hash, key). While logically a single table, data is stored separately on disk. Benefits:

• Performance: Queries accessing specific partitions can run much faster by scanning less data. DELETE operations on old data become DROP PARTITION, which is very fast.
• Manageability: Easier to manage large tables (e.g., backup/restore individual partitions).
• Scalability: Allows data to be spread across different storage devices.
• Maintenance: Reduces maintenance windows for operations like OPTIMIZE TABLE.
• Availability: A failure in one partition might not affect others.

-- Example Range Partitioning
CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    SaleDate DATE,
    Amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(SaleDate)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

23. Discuss the importance of innodb_buffer_pool_size and how to configure it.

innodb_buffer_pool_size is arguably the most important configuration parameter for InnoDB performance. It defines the size of the memory area where InnoDB caches table and index data. When data is requested, InnoDB first checks the buffer pool. If found, it's a fast in-memory read; otherwise, it must read from disk (much slower). Importance: A larger buffer pool means more data can be held in memory, reducing disk I/O and significantly improving query performance for frequently accessed data. Configuration:

• It should be set to 60-80% of available RAM on a dedicated database server.
• Avoid setting it too high, which could lead to swapping (using disk for virtual memory) and degrade performance.
• Check its current value using SHOW VARIABLES LIKE 'innodb_buffer_pool_size';.
• Modify in my.cnf (or my.ini):
  ini

  [mysqld]
  innodb_buffer_pool_size = 8G
  

  Requires a MySQL server restart.

24. What are common caching strategies for MySQL and when would you use them?

Caching strategies aim to reduce the load on the database by serving frequently requested data from a faster, in-memory store.

1. Application-Level Caching:
   • Mechanism: Cache results in the application's memory or a dedicated caching layer (e.g., Redis, Memcached).
   • When to use: For highly repetitive, read-heavy data that doesn't change frequently. Effective for complex queries or joins that are expensive for MySQL.
2. MySQL Query Cache (Deprecated in MySQL 8.0):
   • Mechanism: MySQL server itself stores query results.
   • When to use: Not recommended for MySQL 8.0 onwards due to contention issues and deprecation. For older versions, it could help with identical, simple SELECT queries on tables with very low write activity.
3. InnoDB Buffer Pool:
   • Mechanism: Internal memory area within InnoDB that caches data and index pages.
   • When to use: Always fundamental. Proper sizing is crucial for overall database performance.
4. Proxy Caching (e.g., ProxySQL):
   • Mechanism: A database proxy can cache query results before they hit the actual MySQL server.
   • When to use: For transparent caching, load balancing, and query routing in a high-traffic environment without changing application code.

Scenario & System Design Questions

25. You have a table with millions of user records. Users frequently search by username and email. How would you optimize this?

To optimize search by username and email for millions of user records:

1. Indexes: Create separate UNIQUE indexes on both username and email columns.
   sql

   ALTER TABLE Users ADD UNIQUE INDEX idx_username (username);
   ALTER TABLE Users ADD UNIQUE INDEX idx_email (email);
   

   This ensures fast lookups by either field. UNIQUE is important to prevent duplicate usernames/emails.
2. Application Logic: Ensure your application uses these fields in WHERE clauses for searches.
3. Case-Insensitive Search: If searches need to be case-insensitive, store username_lower and email_lower columns (or use a case-insensitive collation) and index them, or apply LOWER() function in WHERE clause which often prevents index usage. For performance, storing a lowercase version and indexing it is better.
4. Database Configuration: Ensure innodb_buffer_pool_size is adequately sized to keep frequently accessed index pages in memory.

26. Describe how you would set up a highly available and scalable MySQL solution.

A highly available (HA) and scalable MySQL solution typically involves:

1. Replication: Use asynchronous or semi-synchronous replication (e.g., using GTID-based replication for easier failover) to have multiple read replicas. This scales read operations.
2. Load Balancer/Proxy: Implement a intelligent proxy (e.g., ProxySQL, MaxScale, HAProxy) to distribute read queries across replicas and direct writes to the master. It also handles master failure detection and automatic failover/switchover.
3. Failover Mechanism: Integrate a mechanism like Orchestrator, MHA (Master High Availability), or simple health checks with automated scripts to detect master failures and promote a replica to be the new master.
4. Sharding (for extreme scale): If a single master cannot handle write load, shard the database across multiple independent MySQL instances. This requires application-level logic to route queries to the correct shard.
5. Monitoring & Alerting: Comprehensive monitoring of all instances (CPU, memory, disk I/O, replication lag) with proactive alerting is crucial.
6. Backups: Regular, automated backups (logical and physical) for disaster recovery.

27. How would you handle a sudden spike in read traffic to your MySQL database?

A sudden spike in read traffic can overload the database, leading to slow queries or even outages. Here's how to handle it:

1. Immediate Actions:
   • Scale Read Replicas: If using cloud (AWS RDS, GCP Cloud SQL), quickly spin up more read replicas.
   • Application-Level Caching: If possible, enable or increase the capacity of application-level caches (Redis/Memcached) for high-hit, low-change data.
   • Identify Bottlenecks: Use SHOW PROCESSLIST or performance monitoring tools to find slow queries.
   • Temporary Query Throttling: If extreme, temporarily disable less critical features or implement application-level rate limiting.
2. Longer-Term Solutions (Post-spike analysis):
   • Optimize Slow Queries: Analyze EXPLAIN output, add/improve indexes.
   • Improve Caching Strategy: Implement robust caching for frequently accessed data.
   • Implement Read-Write Split: Ensure read queries consistently go to replicas and writes to the master.
   • Consider Sharding: If traffic patterns suggest future spikes beyond replication capacity, consider sharding.
   • Review Database Configuration: Ensure innodb_buffer_pool_size and other parameters are optimally configured.

28. Your application is experiencing deadlocks. How do you identify and resolve them?

Deadlocks occur when two or more transactions are waiting for each other to release locks, creating a circular dependency. Identification:

1. Error Logs: MySQL's error log (.err file) will contain LATEST DETECTED DEADLOCK information, detailing the transactions, locks, and queries involved.
2. SHOW ENGINE INNODB STATUS\G;: This command provides extensive information about InnoDB's internal state, including deadlock information. Look for the "LATEST DETECTED DEADLOCK" section. Resolution:
3. Analyze Deadlock Log: Understand which queries and tables are involved.
4. Reduce Transaction Duration: Keep transactions short and focused.
5. Standardize Access Order: Ensure transactions access resources (tables/rows) in a consistent order whenever possible. This is the most effective way to prevent deadlocks.
6. Index Optimizations: Missing or inefficient indexes can lead to full table/range scans, acquiring more locks than necessary.
7. Less Aggressive Locking: If possible, use lower isolation levels (e.g., READ COMMITTED instead of REPEATABLE READ), but be aware of consistency trade-offs.
8. FOR UPDATE / FOR SHARE (Explicit Locking): Use explicit locking to ensure order if necessary.
   sql

   SELECT * FROM Accounts WHERE UserID = 1 FOR UPDATE;
   SELECT * FROM Orders WHERE AccountID = 1 FOR UPDATE;
   

   (Ensure both transactions acquire locks in the same sequence.) MySQL automatically detects and rolls back one of the deadlocked transactions, typically the one that has done less work, allowing the other to proceed.

29. How would you monitor the health and performance of a MySQL server?

Effective monitoring is crucial for proactive management and troubleshooting:

1. Key Metrics:
   • OS Metrics: CPU utilization, memory usage (especially swapping), disk I/O (reads/writes, latency), network traffic.
   • MySQL Server Metrics:
     • Connections: Max_used_connections, Threads_connected.
     • Query Performance: Slow_queries, Questions, Qcache_hits (if applicable).
     • InnoDB: Innodb_buffer_pool_read_requests, Innodb_buffer_pool_reads, Innodb_rows_read/inserted/updated/deleted, Innodb_log_writes.
     • Replication: Slave_IO_running, Slave_SQL_running, Seconds_Behind_Master.
     • Locks/Deadlocks: Innodb_row_lock_current_waits, error logs for deadlocks.
2. Tools:
   • Built-in: SHOW STATUS, SHOW VARIABLES, SHOW ENGINE INNODB STATUS, mysqladmin extended-status, EXPLAIN.
   • External:
     • Monitoring Agents: Prometheus/Grafana, Zabbix, Nagios, Datadog, New Relic.
     • Percona Monitoring and Management (PMM): Excellent open-source tool specifically for MySQL performance analysis.
     • Cloud Provider Tools: AWS CloudWatch, GCP Monitoring.
3. Log Analysis: Regularly review MySQL error log, slow query log, and general query log.
4. Alerting: Set up alerts for critical thresholds (e.g., high CPU, high replication lag, disk full, frequent deadlocks).

30. Explain the role of the slow query log and how to configure it.

The slow query log is a vital diagnostic tool in MySQL that records SQL queries that take longer than a specified threshold to execute. Role:

• Identify Performance Bottlenecks: Pinpoints inefficient queries that are consuming significant server resources.
• Optimization Target: Helps prioritize which queries to optimize (e.g., by adding indexes, rewriting).
• Debugging: Aids in understanding application behavior and potential query-related issues. Configuration (in my.cnf or my.ini):

1. slow_query_log = 1: Enables the slow query log.
2. slow_query_log_file = /var/log/mysql/mysql-slow.log: Specifies the log file path.
3. long_query_time = 1: Sets the threshold in seconds. Queries taking longer than 1 second will be logged. Set based on acceptable latency.
4. log_queries_not_using_indexes = 1: (Optional) Logs queries that don't use indexes, even if they are fast. Very useful for identifying potential index optimizations. After configuration, restart the MySQL server for changes to take effect. Tools like pt-query-digest can parse and summarize slow query logs.

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

Quick revision table

How to prepare

1. Practice SQL daily: Write queries for various scenarios. Understand JOIN types, subqueries, and common functions.
2. Master EXPLAIN: This is non-negotiable for anyone serious about database performance. Practice interpreting its output for different queries.
3. Understand Storage Engines: Deeply grasp the differences between InnoDB and MyISAM, focusing on transactions, locking, and use cases.
4. Hands-on with Replication: Set up a basic master-replica setup on your local machine or a cloud instance. Understand binlog, relay log, and Seconds_Behind_Master.
5. Focus on "Why" and "How": Don't just memorize definitions. Be ready to explain why a feature is used, how it works, and when to apply it, especially for performance and design questions.