MySQL Quiz ( Advanced ) - All Questions

This advanced MySQL quiz is designed for experienced developers and database engineers preparing for senior-level interviews. It focuses on query optimization, indexing strategies, transactions, isolation levels, locking, execution plans, scalability, and real-world production scenarios.

Question 1: What is the primary purpose of the MySQL query optimizer?

Validate SQL syntax
Choose the most efficient execution plan
Cache query results
Create indexes automatically

Explanation: The optimizer selects the most efficient execution plan.

Question 2: Which command is used to analyze how MySQL executes a query?

DESCRIBE
ANALYZE
EXPLAIN
PROFILE

Explanation: EXPLAIN shows the query execution plan.

Question 3: What does a full table scan indicate?

Efficient index usage
Missing or unusable index
Query cache hit
Temporary table usage

Explanation: A full table scan usually indicates missing indexes.

Question 4: Which index type is best for range queries?

HASH
BTREE
FULLTEXT
SPATIAL

Explanation: BTREE indexes are optimized for range queries.

Question 5: Why can over-indexing be harmful?

Indexes slow down reads
Indexes increase write overhead
Indexes break joins
Indexes reduce data integrity

Explanation: Indexes add overhead to insert, update, and delete operations.

Question 6: Which composite index rule is critical for MySQL?

Index order does not matter
Leftmost prefix rule
Only two columns allowed
Primary key must be included

Explanation: MySQL uses the leftmost prefix of composite indexes.

Question 7: What happens if a query skips the first column of a composite index?

Index is fully used
Index is partially used
Index is ignored
Query fails

Explanation: Skipping the first column prevents index usage.

Question 8: Which index strategy benefits covering queries?

Single-column index
Covering index
Unique index
Hash index

Explanation: Covering indexes allow queries to be served entirely from the index.

Question 9: What does 'Using temporary' in EXPLAIN indicate?

Query cache usage
Temporary table creation
Index-only scan
Lock escalation

Explanation: MySQL creates a temporary table for processing.

Question 10: What does 'Using filesort' mean?

Disk-based table scan
External sorting operation
Index corruption
Slow network IO

Explanation: MySQL performs an extra sorting operation.

Question 11: Which isolation level is default in InnoDB?

READ COMMITTED
READ UNCOMMITTED
REPEATABLE READ
SERIALIZABLE

Explanation: InnoDB defaults to REPEATABLE READ.

Question 12: Which isolation level prevents dirty reads?

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

Explanation: READ COMMITTED prevents dirty reads.

Question 13: Which isolation level prevents phantom reads completely?

READ COMMITTED
REPEATABLE READ
SERIALIZABLE
READ UNCOMMITTED

Explanation: SERIALIZABLE prevents phantom reads.

Question 14: What is a phantom read?

Reading uncommitted data
Seeing new rows added by another transaction
Repeated reads returning different values
Deadlock condition

Explanation: Phantom reads occur when new rows appear in repeated queries.

Question 15: Which locking mechanism does InnoDB primarily use?

Table-level locking
Row-level locking
Database-level locking
File-level locking

Explanation: InnoDB uses row-level locking.

Question 16: What causes a deadlock?

Index corruption
Circular wait between transactions
Long-running queries
Table scans

Explanation: Deadlocks occur when transactions wait on each other.

Question 17: How does MySQL handle deadlocks?

Waits indefinitely
Kills the longest query
Rolls back one transaction
Restarts the server

Explanation: MySQL automatically rolls back one transaction.

Question 18: What is MVCC in InnoDB?

Multiple Version Concurrency Control
Multi Value Column Cache
Managed Virtual Connection Control
Multi View Column Control

Explanation: MVCC allows consistent reads without blocking.

Question 19: Which operation benefits most from MVCC?

INSERT
UPDATE
SELECT
DELETE

Explanation: SELECT queries benefit from non-blocking reads.

Question 20: What is the purpose of redo logs?

Rollback transactions
Crash recovery
Query optimization
Data replication

Explanation: Redo logs help recover committed transactions.

Question 21: What is the role of undo logs?

Store backups
Support rollbacks and MVCC
Optimize joins
Maintain indexes

Explanation: Undo logs support rollback and consistent reads.

Question 22: Which MySQL feature enables horizontal scaling?

Indexes
Replication
Normalization
Transactions

Explanation: Replication supports horizontal scaling.

Question 23: What is the main drawback of master-slave replication?

No read scaling
Single point of write failure
High latency
Data corruption

Explanation: Writes are limited to the primary node.

Question 24: Which replication type supports automatic failover?

Statement-based
Row-based
Group Replication
Asynchronous

Explanation: Group Replication supports failover.

Question 25: What problem does sharding primarily solve?

Query optimization
Write scalability
Data normalization
Index maintenance

Explanation: Sharding distributes writes across nodes.

Question 26: What is the biggest challenge with sharding?

Index size
Cross-shard joins
Transaction speed
Schema design

Explanation: Joins across shards are complex.

Question 27: Which MySQL feature supports point-in-time recovery?

Full backup
Binary logs
Redo logs
Slow query logs

Explanation: Binary logs allow point-in-time recovery.

Question 28: What does the slow query log capture?

Syntax errors
Queries exceeding a time threshold
Failed transactions
Deadlocks

Explanation: It logs slow-running queries.

Question 29: Which variable controls slow query threshold?

long_query_time
slow_query_limit
query_timeout
execution_time

Explanation: long_query_time defines slow query threshold.

Question 30: What is the main advantage of prepared statements?

Faster network IO
Reduced parsing overhead and SQL injection protection
Automatic indexing
Better replication

Explanation: Prepared statements improve security and performance.

Question 31: Which storage engine is best for read-heavy workloads?

InnoDB
MyISAM
Memory
CSV

Explanation: MyISAM performs well for read-heavy workloads.

Question 32: Why is MyISAM not recommended for transactions?

No indexing
No foreign key support
No transaction support
Slow writes

Explanation: MyISAM does not support transactions.

Question 33: What is a covering index?

Index with unique values
Index containing all queried columns
Composite primary key
Clustered index

Explanation: Covering indexes satisfy queries without table access.

Question 34: What is a clustered index in InnoDB?

Secondary index
Primary key index storing row data
Hash index
Temporary index

Explanation: InnoDB stores data with the primary key.

Question 35: Why should primary keys be short and immutable?

Faster joins only
Reduced index size and page splits
Better readability
Simpler queries

Explanation: Smaller keys improve performance.

Question 36: What happens if no primary key is defined in InnoDB?

Table creation fails
MySQL creates a hidden row ID
No indexes allowed
Queries are slower only

Explanation: InnoDB creates a hidden clustered index.

Question 37: Which feature helps optimize bulk inserts?

Disable indexes temporarily
Enable triggers
Increase isolation level
Use views

Explanation: Disabling indexes speeds bulk inserts.

Question 38: Why is SELECT * discouraged in production?

Syntax overhead
Network and performance overhead
Index corruption
Security violation

Explanation: Fetching unnecessary columns hurts performance.

Question 39: Which MySQL feature enforces strict data validation?

STRICT SQL mode
Triggers
Views
Indexes

Explanation: Strict mode enforces data correctness.

Question 40: What is the benefit of partitioning tables?

Smaller schema
Improved query performance and manageability
Automatic indexing
Better joins

Explanation: Partitioning improves performance and maintenance.

Question 41: Which partitioning type is best for date-based data?

HASH
KEY
RANGE
LIST

Explanation: RANGE is ideal for date-based partitioning.

Question 42: What is the primary goal of MySQL performance tuning?

Reduce schema size
Optimize resource usage and response time
Avoid joins
Eliminate transactions

Explanation: Performance tuning optimizes efficiency.

Question 43: Which mistake most commonly degrades MySQL performance?

Using joins
Missing or incorrect indexes
Using transactions
Using normalization

Explanation: Poor indexing is the most common issue.

Question 44: Why is denormalization sometimes used in high-traffic systems?

Improve data integrity
Reduce join complexity and latency
Save storage
Simplify schema

Explanation: Denormalization improves read performance.

Question 45: Which metric best indicates database health?

Disk size
Query response time
Table count
Schema complexity

Explanation: Response time reflects overall health.

Question 46: What is the ultimate goal of MySQL system design?

Minimal schema
Scalable, reliable, and performant data storage
Avoid SQL complexity
Reduce table count

Explanation: MySQL aims for scalable and reliable data storage.

Back to programming Quizzes

MySQL Quiz ( Advanced )

Questions

Minutes

Min Limit