MySQL Interview Questions

Use BLOB data type for the database field.

MySQL is an open-source relational database management system (RDBMS) that uses SQL. Key features include:
• Cross-platform compatibility
• ACID compliance
• Support for multiple storage engines
• Replication and clustering
• High performance and scalability
• Strong security features

DELETE: Removes specific rows based on WHERE condition, can be rolled back
DROP: Removes entire table structure and data permanently
TRUNCATE: Removes all rows but keeps table structure, faster than DELETE, cannot be rolled back

Example:
DELETE FROM users WHERE age < 18;
TRUNCATE TABLE temp_data;
DROP TABLE old_table;

INNER JOIN: Returns matching records from both tables
LEFT JOIN: Returns all records from left table and matching from right
RIGHT JOIN: Returns all records from right table and matching from left
FULL OUTER JOIN: Returns all records when there is a match in either table

Example:
SELECT u.name, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

Primary Key: Uniquely identifies each record in a table, cannot be NULL
Foreign Key: Links two tables together, references primary key of another table

Example:
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CHAR:
• Fixed-length string
• Pads with spaces if shorter
• Faster for fixed-size data
• Maximum 255 characters

VARCHAR:
• Variable-length string
• Uses only required space
• Better for variable-size data
• Maximum 65,535 characters

Example:
name CHAR(10) -- Always uses 10 bytes
email VARCHAR(100) -- Uses actual length + 1-2 bytes

An INDEX is a data structure that improves query performance by creating shortcuts to data.

Benefits:
• Faster SELECT queries
• Faster WHERE, ORDER BY, GROUP BY
• Unique constraints

Types:
• PRIMARY (unique, not null)
• UNIQUE (unique values)
• INDEX (regular index)
• FULLTEXT (text searching)

Example:
CREATE INDEX idx_email ON users(email);
CREATE UNIQUE INDEX idx_username ON users(username);

Storage engines determine how MySQL stores and retrieves data.

Common engines:
• InnoDB: Default, supports transactions, foreign keys, row-level locking
• MyISAM: Fast reads, table-level locking, no transactions
• MEMORY: Stores data in RAM, very fast but volatile
• ARCHIVE: Compressed storage for archival data

Example:
CREATE TABLE users (...) ENGINE=InnoDB;
ALTER TABLE logs ENGINE=MyISAM;

Atomicity: Transaction is all-or-nothing
Consistency: Database remains in valid state
Isolation: Concurrent transactions don't interfere
Durability: Committed changes are permanent

Example:
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- Both updates succeed or both fail

WHERE:
• Filters rows before grouping
• Cannot use aggregate functions
• Applied to individual rows

HAVING:
• Filters groups after GROUP BY
• Can use aggregate functions
• Applied to grouped results

Example:
SELECT department, COUNT(*)
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 5;

Aggregate functions perform calculations on multiple rows and return a single value.

Common functions:
• COUNT() - counts rows
• SUM() - adds values
• AVG() - calculates average
• MAX() - finds maximum
• MIN() - finds minimum

Example:
SELECT COUNT(*) as total_users,
       AVG(age) as avg_age,
       MAX(salary) as max_salary
FROM employees;

AUTO_INCREMENT automatically generates unique sequential numbers for new records.

Features:
• Starts from 1 by default
• Increments by 1 for each new row
• Only one AUTO_INCREMENT column per table
• Must be indexed (usually PRIMARY KEY)

Example:
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

INSERT INTO users (name) VALUES ('John'); -- id = 1
INSERT INTO users (name) VALUES ('Jane'); -- id = 2

Normalization is organizing data to reduce redundancy and improve data integrity.

Benefits:
• Eliminates data redundancy
• Reduces storage space
• Prevents update anomalies
• Improves data consistency

Normal Forms:
• 1NF: Atomic values, no repeating groups
• 2NF: 1NF + no partial dependencies
• 3NF: 2NF + no transitive dependencies

Example: Separate customer info from orders to avoid repeating customer data.

MySQL replication copies data from master to slave servers for backup and load distribution.

Types:
• Master-Slave: One master, multiple slaves (read-only)
• Master-Master: Multiple masters (bidirectional)
• Statement-based: Replicates SQL statements
• Row-based: Replicates actual data changes
• Mixed: Combines both approaches

Configuration:
server-id=1
log-bin=mysql-bin
binlog-format=ROW

READ UNCOMMITTED: Allows dirty reads
READ COMMITTED: Prevents dirty reads
REPEATABLE READ: Prevents dirty and non-repeatable reads (MySQL default)
SERIALIZABLE: Highest isolation, prevents all phenomena

Example:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1;
-- Other transactions can't modify this row
COMMIT;

Optimization techniques:
• Use appropriate indexes
• Avoid SELECT *
• Use LIMIT for large datasets
• Optimize WHERE clauses
• Use EXPLAIN to analyze queries
• Normalize database design
• Use connection pooling

Example:
EXPLAIN SELECT name FROM users WHERE email = 'john@example.com';
-- Shows execution plan and index usage

Stored procedures are precompiled SQL code stored in the database.

Advantages:
• Better performance (precompiled)
• Reduced network traffic
• Code reusability
• Enhanced security
• Centralized business logic

Example:
DELIMITER //
CREATE PROCEDURE GetUserOrders(IN user_id INT)
BEGIN
  SELECT * FROM orders WHERE user_id = user_id;
END //
DELIMITER ;

CALL GetUserOrders(123);

Partitioning divides large tables into smaller, manageable pieces.

Types:
• RANGE: Based on value ranges
• LIST: Based on specific values
• HASH: Based on hash function
• KEY: Similar to HASH but uses MySQL function

Example:
CREATE TABLE sales (
  id INT,
  sale_date DATE
) PARTITION BY RANGE (YEAR(sale_date)) (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025)
);

Triggers are special stored procedures that automatically execute in response to database events.

Types:
• BEFORE INSERT/UPDATE/DELETE
• AFTER INSERT/UPDATE/DELETE

Example:
CREATE TRIGGER update_modified_time
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  SET NEW.updated_at = NOW();
END;

-- Automatically updates timestamp on every user update

Clustered Index:
• Physical order of data matches index order
• One per table (usually PRIMARY KEY)
• Data pages stored in order of key values
• Faster for range queries

Non-clustered Index:
• Separate structure pointing to data rows
• Multiple allowed per table
• Contains pointers to actual data
• Additional lookup required

In MySQL InnoDB, PRIMARY KEY is clustered, others are non-clustered.

Deadlock occurs when two transactions wait for each other to release locks.

Prevention strategies:
• Access tables in same order
• Keep transactions short
• Use appropriate isolation levels
• Use SELECT ... FOR UPDATE carefully
• Set innodb_lock_wait_timeout

Example:
-- Transaction 1: locks A then B
-- Transaction 2: locks B then A
-- Results in deadlock

MySQL automatically detects and rolls back one transaction.

EXPLAIN shows how MySQL executes a query and helps optimize performance.

Key columns:
• select_type: Type of SELECT
• table: Table being accessed
• type: Join type (ALL, index, range, ref, eq_ref)
• key: Index used
• rows: Estimated rows examined
• Extra: Additional information

Example:
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- Shows if index is used and query efficiency

Views are virtual tables based on SQL queries that don't store data physically.

Advantages:
• Data security (hide sensitive columns)
• Simplify complex queries
• Data abstraction
• Consistent interface

Example:
CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active';

SELECT * FROM active_users; -- Uses the view

Backup types:
• Logical: mysqldump (SQL statements)
• Physical: File system copy
• Hot: Online backup (MySQL Enterprise)
• Cold: Offline backup

Example:
mysqldump -u root -p database_name > backup.sql
mysql -u root -p database_name < backup.sql

Point-in-time recovery:
Use binary logs to recover to specific time.

InnoDB:
• ACID compliant transactions
• Row-level locking
• Foreign key support
• Crash recovery
• Better for concurrent writes

MyISAM:
• Table-level locking
• No transactions
• Faster for read-heavy workloads
• Smaller disk footprint
• Full-text indexing

InnoDB is default and recommended for most applications.

Option A is the right answer

Query cache stores SELECT statement results in memory for faster retrieval.

Configuration:
query_cache_type = 1
query_cache_size = 256M

Optimization tips:
• Use identical queries
• Avoid non-deterministic functions
• Monitor cache hit ratio
• Consider disabling for write-heavy workloads

Example:
SHOW STATUS LIKE 'Qcache%';
-- Shows cache statistics and hit ratio

MVCC allows multiple transactions to access same data simultaneously without locking.

How it works:
• Each row has multiple versions
• Transactions see consistent snapshot
• Old versions kept in undo log
• Readers don't block writers

Benefits:
• High concurrency
• Consistent reads
• No read locks needed

Used in InnoDB with REPEATABLE READ isolation level.

Binary logs record all changes for replication and point-in-time recovery.

Formats:
• STATEMENT: Logs SQL statements
• ROW: Logs actual row changes
• MIXED: Automatically chooses format

Configuration:
log-bin=mysql-bin
binlog-format=ROW
expire_logs_days=7

Example:
SHOW BINARY LOGS;
mysqlbinlog mysql-bin.000001

Performance Schema:
• Runtime performance monitoring
• Low-level instrumentation data
• Memory, I/O, lock statistics

Sys Schema:
• User-friendly views of Performance Schema
• Pre-built queries for common tasks
• Easier performance analysis

Example:
SELECT * FROM sys.schema_table_statistics;
SELECT * FROM performance_schema.events_waits_summary_global_by_event_name;

Group Replication provides highly available distributed MySQL service.

Features:
• Multi-master replication
• Automatic failover
• Conflict detection and resolution
• Elastic scaling

Modes:
• Single-primary (one writer)
• Multi-primary (multiple writers)

Configuration requires group_replication plugin and proper network setup for consensus-based replication.

Buffer pool caches table and index data in memory for faster access.

Optimization:
innodb_buffer_pool_size = 70-80% of RAM
innodb_buffer_pool_instances = 8-16

Monitoring:
SHOW ENGINE INNODB STATUS;
SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS;

Key metrics:
• Buffer pool hit ratio (>99%)
• Pages read/written
• Free pages available

Adaptive hash index is automatically created by InnoDB for frequently accessed pages.

Characteristics:
• Built automatically on B-tree indexes
• Uses hash function for O(1) lookups
• Only for equality searches
• Cannot be manually created

Benefits:
• Faster point queries
• Reduced CPU usage
• Automatic optimization

Controlled by innodb_adaptive_hash_index parameter.

Online DDL allows schema changes without blocking DML operations.

Algorithms:
• INSTANT: Metadata only changes
• INPLACE: No table copy required
• COPY: Full table rebuild

Example:
ALTER TABLE users ADD COLUMN phone VARCHAR(20), ALGORITHM=INPLACE;

Limitations:
• Some operations still require table copy
• Increased disk space during operation
• Performance impact on concurrent queries

Connection pooling reuses database connections to reduce overhead.

Benefits:
• Reduced connection establishment time
• Lower memory usage
• Better resource utilization
• Improved scalability

Configuration:
max_connections = 1000
thread_cache_size = 100

Tools:
• ProxySQL
• MySQL Router
• Application-level pools (HikariCP, c3p0)

Invisible indexes are not used by the optimizer but maintained by DML operations.

Use cases:
• Test index effectiveness before dropping
• Gradual index deployment
• A/B testing query performance

Example:
CREATE INDEX idx_email ON users(email) INVISIBLE;
ALTER INDEX idx_email VISIBLE;

Query: SELECT * FROM users WHERE email = 'test@example.com';
-- Won't use invisible index unless forced

CTEs (Common Table Expressions):
WITH RECURSIVE cte AS (
  SELECT 1 as n UNION ALL
  SELECT n+1 FROM cte WHERE n<10
) SELECT * FROM cte;

Window Functions:
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as rank FROM employees;

JSON improvements:
• JSON_TABLE() function
• Multi-valued indexes
• JSON aggregation functions