MySQL Optimization Practice Exam

Question 1 MySQL Optimization

How can auto-increment contention appear in high-write workloads?

Multiple concurrent inserts into the same table can contend on the auto-increment lock or hot index pages. Over time, page splits and deletes can leave space underutilized and make I/O less efficient. It acquires row locks even for reads, increasing contention and blocking other writers/readers. A helper schema with views and procedures that summarize performance_schema and information_schema data for easier analysis.

Question 2 MySQL Optimization

What is the impact of timezone handling on performance?

Analytical queries can monopolize CPU/IO and buffer pool, degrading latency for short OLTP transactions. Frequent conversions with functions like CONVERT_TZ can be expensive and may block index usage on datetime columns. Rebuilding tables/indexes with `OPTIMIZE TABLE` or `ALTER TABLE ... ENGINE=InnoDB`. To identify the most expensive queries and prioritize optimization efforts where they have the biggest impact.

Question 3 MySQL Optimization

What is a good pattern for OLAP workloads if you must use MySQL?

When columns have highly skewed distributions where simple cardinality statistics are not accurate. They prevent the use of normal index ranges, forcing scans, because the function must be applied to every row. Keep transactions as small and short-lived as possible while still being logically consistent. Run them on dedicated replicas or a separate cluster tuned for large scans and aggregations.

Question 4 MySQL Optimization

What are the main areas involved in MySQL performance optimization?

High throughput with bad latency still hurts user experience; both dimensions matter. It stores parsed and optimized query plans; prepared statements can reuse cached plans, reducing planning overhead. Numeric types are smaller, faster to compare, and index more efficiently than variable-length strings. Schema design, indexing, query writing, configuration tuning, hardware resources, and monitoring.

Question 5 MySQL Optimization

What is `table_definition_cache`?

A correlated subquery references columns from the outer query and may run per row; a non-correlated does not. To hold rows for joins that cannot use indexes efficiently, especially for block nested loop joins. A cache for table metadata (definitions) to avoid disk reads and parsing of .frm or data dictionary entries. ALL (a full table scan).

Question 6 MySQL Optimization

Which replication mode is generally more reliable and predictable for complex queries?

It writes pages twice (first to a special area, then to their final location) to protect against partial page writes. They let you simulate dropping an index (optimizer won't use it) without losing the index, so you can safely observe performance impact. Better read scaling but more replication traffic and potential lag; each replica adds overhead on the primary. Row-based replication.

Question 7 MySQL Optimization

How can Group Replication impact write performance?

On modern storage with high parallelism (e.g. SSD/NVMe) and heavy concurrent workloads. Incorrect rules can change semantics or introduce subtle bugs and performance regressions. Multiple concurrent inserts into the same table can contend on the auto-increment lock or hot index pages. Consensus and conflict checks add coordination overhead, which can increase write latency.

Question 8 MySQL Optimization

How can excessive read-ahead hurt performance?

By pulling in many pages that are never used, wasting IO and cache space. You create long-running transactions that can hold locks, block purge, and cause contention and bloat. A paging technique that uses the last seen key value in a WHERE condition (e.g. `id > ?`) instead of OFFSET, enabling index-friendly scans. They catch drift from new features or data growth early and keep optimization as an ongoing discipline rather than a one-off event.

Question 9 MySQL Optimization

How can you design idempotent upsert logic in MySQL?

EXPLAIN Use INSERT ... ON DUPLICATE KEY UPDATE or REPLACE carefully so repeated executions leave data in a consistent state. Run them on dedicated replicas or a separate cluster tuned for large scans and aggregations. Use consistent data types for literals and columns, and cast parameters in the application instead of in the WHERE clause.

Question 10 MySQL Optimization

How can you make text search on large text columns more efficient than using `LIKE '%word%'`?

ORMS may generate suboptimal SQL with unnecessary joins or selects; hand-tuned queries are often needed for hot spots. Approximately 60% to 75% of system RAM, depending on workload and other needs. You create long-running transactions that can hold locks, block purge, and cause contention and bloat. Use FULLTEXT indexes, external search engines, or inverted index structures.

Question 11 MySQL Optimization

What tradeoff does `skip-name-resolve` introduce?

They keep old versions alive in the undo log, growing it and preventing purge from reclaiming space. Faster connections but you must grant privileges using IP addresses instead of hostnames. It may give little benefit because many rows match, and the optimizer may prefer a table scan instead. Use sharded counters, batch updates, or approximate counting instead of incrementing a single row on every event.

Question 12 MySQL Optimization

What is a common performance pitfall when using DISTINCT?

To inspect low-level handler counters that indicate how MySQL accesses table rows and indexes. They prevent the use of normal index ranges, forcing scans, because the function must be applied to every row. ALGORITHM and LOCK clauses in ALTER TABLE (e.g. ALGORITHM=INPLACE, LOCK=NONE). Using DISTINCT to mask duplicate-creating joins rather than fixing the join logic, leading to unnecessary sorting and deduplication.

Question 13 MySQL Optimization

Why is it important to test performance under concurrency, not just single-user load?

Create an index on (user_id, created_at). It reduces shared tablespace bloat and allows per-table operations like shrinking and moving to faster storage. Contention, locking, and resource saturation often appear only when many users or threads run queries simultaneously. The number of distinct values in an indexed column or column set, affecting how selective and useful the index is.

Question 14 MySQL Optimization

What is semisynchronous replication?

The index must match the ORDER BY columns in the correct order and direction, and the query must be compatible with index-only ordering. InnoDB may issue more IO than the storage can handle smoothly, causing latency spikes and contention. A feature that lets you assign threads to groups with specific CPU limits/priorities to control resource usage. A mode where the primary waits for at least one replica to acknowledge receiving a transaction before committing.

Question 15 MySQL Optimization

What is a typical sign that your database is becoming IO-bound?

Set optimizer_trace to "enabled=on", run the query, then read the trace from information_schema.OPTIMIZER_TRACE. A lock on a range between index records, used to prevent phantom rows in certain isolation levels. High disk utilization, increasing read/write latencies, and CPU underutilization despite slow queries. NULL values can reduce index selectivity and can complicate index usage and cardinality estimates.

Question 16 MySQL Optimization

Why might window functions affect performance?

They require additional sorting or buffering to compute per-row aggregates over partitions and orders. They reveal tail latencies and outliers that averages hide, showing real-world worst-case user experiences. Schema design, indexing, query writing, configuration tuning, hardware resources, and monitoring. Use BIGINT for primary keys, providing a much larger ID space.

Question 17 MySQL Optimization

Why is it better to fix query patterns than just increasing global buffers endlessly?

A column whose value is computed from an expression based on other columns, either virtual or stored. Optimize GROUP BY and ORDER BY, add appropriate indexes, and tune tmp_table_size and max_heap_table_size. slow_query_log Bigger buffers have diminishing returns and can create memory pressure; better query shapes reduce the need for large buffers.

Question 18 MySQL Optimization

What is the general recommendation for transaction size?

On highly concurrent or mixed workloads where AHI causes latch contention or poor cache behavior. Keep transactions as small and short-lived as possible while still being logically consistent. A correlated subquery references columns from the outer query and may run per row; a non-correlated does not. Application-level caching, result caching in external stores (e.g. Redis), and optimizing queries and indexes.

Question 19 MySQL Optimization

What is cardinality in the context of indexes?

The number of distinct values in an indexed column or column set, affecting how selective and useful the index is. They keep old versions alive in the undo log, growing it and preventing purge from reclaiming space. Dirty reads (seeing uncommitted changes from other transactions). EXPLAIN

Question 20 MySQL Optimization

What is the purpose of buffer pool instances in InnoDB?

They may be executed repeatedly for each outer row instead of once, increasing total work. Unindexed foreign keys cause full scans on parent/child tables for checks, badly hurting insert/update/delete performance. To split the buffer pool into multiple instances, reducing contention on internal buffer pool locks under high concurrency. It reads all columns, preventing index-only scans, increasing I/O, and possibly transferring unnecessary data.

Question 21 MySQL Optimization

What general principle should guide all MySQL optimization work?

Approximately 60% to 75% of system RAM, depending on workload and other needs. Run them on dedicated replicas or a separate cluster tuned for large scans and aggregations. Measure, change one thing at a time, verify with metrics, and iterate based on real workload behavior. They reduce the number of rows MySQL must scan by allowing direct or more selective access to matching rows.

Question 22 MySQL Optimization

What is a slow query log in MySQL?

They prevent the use of normal index prefix search and usually force full scans. A log that records queries that exceed a configurable execution time threshold or do not use indexes, helping identify performance problems. When read-heavy workloads frequently require expensive joins, duplicating some data can reduce joins at the cost of extra storage and write complexity. A lock on a range between index records, used to prevent phantom rows in certain isolation levels.

Question 23 MySQL Optimization

What is one way to mitigate NUMA issues for MySQL?

It reads all columns, preventing index-only scans, increasing I/O, and possibly transferring unnecessary data. Bind MySQL to specific CPUs and memory nodes or configure the OS for interleaved allocation so memory access is more uniform. ORMS may generate suboptimal SQL with unnecessary joins or selects; hand-tuned queries are often needed for hot spots. They match a small fraction of rows, allowing MySQL to quickly narrow down to few rows rather than scanning many.

Question 24 MySQL Optimization

How can semisynchronous replication affect performance?

Old, rarely accessed data bloats tables and indexes, slowing queries and increasing storage/backup costs. performance_schema tables like `data_locks`, `data_lock_waits`, and related views in the `sys` schema. New versions often include optimizer improvements, better defaults, and performance enhancements. It increases commit latency but can reduce data loss risk during primary failure.

Question 25 MySQL Optimization

What is a safe strategy for rolling out a major MySQL upgrade?

Reusing existing database connections instead of creating new ones for each request, reducing connection overhead. Test on staging with production-like data, run dual-write or shadow-read tests if possible, and upgrade a subset of replicas before the primary. When queries frequently filter on a range of values, such as dates (e.g. partition by year or month). They can indicate many full table scans or inefficient index usage.

Question 26 MySQL Optimization

Why is it important to coordinate MySQL version upgrades with performance testing?

Optimizer changes and new defaults can alter query plans; testing catches regressions before production rollout. Over time, page splits and deletes can leave space underutilized and make I/O less efficient. Running heavy jobs during peak times can overload the database and degrade user-facing performance. A synchronous replication technology providing a fault-tolerant, multi-primary or single-primary cluster with automatic failover.

Question 27 MySQL Optimization

Why is idempotency important for background jobs that touch MySQL?

It allows safe retries without double-applying changes, which is critical when timeouts or partial failures occur. Stored procedures and functions can hide complex logic that may not scale well or may bypass some optimizer capabilities. Reusing existing database connections instead of creating new ones for each request, reducing connection overhead. They have different locking and crash recovery behaviors; MyISAM's table locks and lack of crash safety are usually bad for performance and reliability.

Question 28 MySQL Optimization

What is the difference between non-repeatable reads and phantom reads?

Too many partitions increase overhead, slow metadata operations, and complicate maintenance. The index must match the ORDER BY columns in the correct order and direction, and the query must be compatible with index-only ordering. Non-repeatable reads involve changed existing rows; phantom reads involve new or removed rows matching a query. It allows safe retries without double-applying changes, which is critical when timeouts or partial failures occur.

Question 29 MySQL Optimization

How can you work with ORMs to maintain good performance?

To allow effective partition pruning and index use for queries filtering on the partition key. It performs bulk loading with fewer round trips and less per-row overhead. Profile generated SQL, add explicit indexes and query hints where possible, and use raw queries or stored routines for critical paths. A data structure describing the distribution of values in a column, giving the optimizer better selectivity estimates.

Question 30 MySQL Optimization

What is `tmp_table_size` / `max_heap_table_size` used for?

Keep transactions as small and short-lived as possible while still being logically consistent. They set the maximum size of in-memory temporary tables before MySQL converts them to on-disk tables. To prevent a thundering herd of connections during spikes or restarts from overwhelming the database. It spaces out retry attempts, giving the server time to recover instead of amplifying load.

Question 31 MySQL Optimization

Which configuration variable enables the slow query log?

slow_query_log Focusing on user-facing latency, error rates, and cost ensures you optimize where it actually matters, not just abstract benchmarks. Workloads with independent transactions affecting different databases or partitions. Frequent conversions with functions like CONVERT_TZ can be expensive and may block index usage on datetime columns.

Question 32 MySQL Optimization

When does increasing buffer pool instances help?

Access tables and rows in a consistent order, keep transactions short, and avoid unnecessary locking. On large buffer pools and highly concurrent workloads where latch contention is visible. New WHERE/ORDER BY clauses may no longer match existing left-prefix orders, making old composite indexes less effective. By seeing which indexes, join orders, and strategies the optimizer considered or rejected and adjusting schema or queries accordingly.

Question 33 MySQL Optimization

What do `wait_timeout` and `interactive_timeout` control?

How long idle connections are kept open before being closed by the server. Optimize GROUP BY and ORDER BY, add appropriate indexes, and tune tmp_table_size and max_heap_table_size. `LEFT JOIN` may retain unmatched rows, potentially processing more data; unnecessary LEFT JOINs can hurt performance compared to INNER JOINs. So future maintainers know why it exists, reducing the chance of accidental removal or redundant additions.

Question 34 MySQL Optimization

What is a sliding window partitioning strategy?

Set optimizer_trace to "enabled=on", run the query, then read the trace from information_schema.OPTIMIZER_TRACE. By pre-aggregating frequently requested metrics so queries read fewer rows and do less computation. Indexing only the first N characters can drastically shrink index size while still providing good selectivity in many cases. Using partitions for recent time ranges and periodically dropping old partitions to expire data efficiently.

Question 35 MySQL Optimization

Why is it useful to have realistic test data when tuning performance?

Using partitions for recent time ranges and periodically dropping old partitions to expire data efficiently. Because query plans, caching behavior, and contention patterns depend heavily on actual data volumes and distributions. Optimize GROUP BY and ORDER BY, add appropriate indexes, and tune tmp_table_size and max_heap_table_size. Use multi-row INSERTs or `LOAD DATA`, temporarily disable non-critical indexes and foreign keys, and run in larger but controlled transactions.

Question 36 MySQL Optimization

Why is `SELECT *` often bad for performance?

Heavy swapping indicates memory pressure and can cause huge latency spikes as pages move between RAM and disk. It reads all columns, preventing index-only scans, increasing I/O, and possibly transferring unnecessary data. Focusing on user-facing latency, error rates, and cost ensures you optimize where it actually matters, not just abstract benchmarks. They have different locking and crash recovery behaviors; MyISAM's table locks and lack of crash safety are usually bad for performance and reliability.

Question 37 MySQL Optimization

Why is it useful to track changes in execution plans over time?

Schema design, indexing, query writing, configuration tuning, hardware resources, and monitoring. Plan regressions from data growth or upgrades can silently degrade performance; tracking helps detect and fix them. Using partitions for recent time ranges and periodically dropping old partitions to expire data efficiently. They reduce the number of rows MySQL must scan by allowing direct or more selective access to matching rows.

Question 38 MySQL Optimization

Why are correlated subqueries often slower?

They match a small fraction of rows, allowing MySQL to quickly narrow down to few rows rather than scanning many. Unindexed foreign keys cause full scans on parent/child tables for checks, badly hurting insert/update/delete performance. They may be executed repeatedly for each outer row instead of once, increasing total work. On modern storage with high parallelism (e.g. SSD/NVMe) and heavy concurrent workloads.

Question 39 MySQL Optimization

Why must you be careful with replication lag when offloading reads?

Because replicas may be slightly behind the primary, so reads might see stale data. Start with recommended defaults or `sys` schema presets, then selectively enable more detail only where needed. Set optimizer_trace to "enabled=on", run the query, then read the trace from information_schema.OPTIMIZER_TRACE. It removes large scans and aggregations from the OLTP database, keeping it focused on low-latency transactional work.

Question 40 MySQL Optimization

Why should you be careful with random UUID primary keys for clustered indexes?

Old, rarely accessed data bloats tables and indexes, slowing queries and increasing storage/backup costs. By pulling in many pages that are never used, wasting IO and cache space. Higher IO latency and lower IOPS can make even well-optimized queries slow and unpredictable. They cause random inserts in the B-tree, leading to page splits, fragmentation, and worse cache locality.

Question 41 MySQL Optimization

What is a safer choice for busy tables than INT auto-increment?

A high ratio means many rows are scanned or filtered to return few results, indicating poor selectivity or missing indexes. Unindexed foreign keys cause full scans on parent/child tables for checks, badly hurting insert/update/delete performance. Set optimizer_trace to "enabled=on", run the query, then read the trace from information_schema.OPTIMIZER_TRACE. Use BIGINT for primary keys, providing a much larger ID space.

Question 42 MySQL Optimization

How do you create or update histograms in MySQL?

Schema design, indexing, query writing, configuration tuning, hardware resources, and monitoring. Using `ANALYZE TABLE ... UPDATE HISTOGRAM ON column ...`. Automatically rewriting certain queries to more efficient forms or routing them differently without changing application code. To split the buffer pool into multiple instances, reducing contention on internal buffer pool locks under high concurrency.

Question 43 MySQL Optimization

Why can optimizer trace output be large?

It logs many internal optimizer decisions and alternatives, so complex queries can generate verbose JSON traces. Set optimizer_trace to "enabled=on", run the query, then read the trace from information_schema.OPTIMIZER_TRACE. Store timestamps in UTC and convert at the application layer, or use generated columns for common local time views. To minimize storage, improve cache usage, and ensure efficient comparisons and index performance.

Question 44 MySQL Optimization

Why is index fragmentation a concern for InnoDB?

Row-based replication. Frequent conversions with functions like CONVERT_TZ can be expensive and may block index usage on datetime columns. Over time, page splits and deletes can leave space underutilized and make I/O less efficient. Numeric types are smaller, faster to compare, and index more efficiently than variable-length strings.

Question 45 MySQL Optimization

When is it helpful to index a boolean column?

It deletes then re-inserts the row, which can reset non-specified columns and generate extra undo/redo and index work. Too-large batches may overflow buffers or logs, while too-small batches waste overhead; tuning size balances throughput and stability. When combined with other more selective columns in a composite index used by frequent queries. Faster connections but you must grant privileges using IP addresses instead of hostnames.

Question 46 MySQL Optimization

How can you leverage read-only replicas to experiment with new indexes?

Create and test indexes on replicas first, observe performance, and then apply successful ones to the primary. Overcommit can lead to OOM kills or severe swapping when memory usage spikes, crashing or stalling the database. Because replicas may be slightly behind the primary, so reads might see stale data. For read-heavy workloads on IO-bound systems where CPU is not the main bottleneck.

Question 47 MySQL Optimization

Which option disables DNS lookups for client hostnames?

`skip-name-resolve`. It allows safe retries without double-applying changes, which is critical when timeouts or partial failures occur. It forces MySQL to bypass the query cache (on older versions) and measure raw execution cost. A mechanism that temporarily cuts off or limits requests to a failing dependency, preventing cascades and allowing recovery.

Question 48 MySQL Optimization

What is the risk of using very large session-level sort or join buffers?

The number of distinct values in an indexed column or column set, affecting how selective and useful the index is. New features and queries change access patterns; old indexes may become unused while new ones are needed. The workload may not match production, leading to misleading conclusions and suboptimal settings. They can consume huge amounts of memory when many connections are active, causing swapping and performance collapse.

Question 49 MySQL Optimization

Why is proper schema normalization important for performance?

It reduces data duplication and anomalies, makes indexes smaller, and improves consistency, which can improve query performance. Workloads with independent transactions affecting different databases or partitions. Extract frequently queried fields into generated (and indexed) columns rather than querying deep JSON paths. By seeing which indexes, join orders, and strategies the optimizer considered or rejected and adjusting schema or queries accordingly.

Question 50 MySQL Optimization

Why is it important to measure performance before optimizing?

It simplifies identifying which transactions a server has executed, easing promotion and topology changes. `innodb_buffer_pool_dump_at_shutdown` and `innodb_buffer_pool_load_at_startup`. When read-heavy workloads frequently require expensive joins, duplicating some data can reduce joins at the cost of extra storage and write complexity. Because you need a baseline to compare improvements against and to avoid optimizing the wrong parts of the system.