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?
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?
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?
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?
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.