Performance Tuning & Interview Masterclass

#Introduction

This chapter brings together everything from the earlier chapters and shows how MySQL is optimized in real enterprise applications.

It covers query optimization, an EXPLAIN deep dive, the slow query log, join optimization, pagination strategies, partitioning, common production problems, the top interview questions, production best practices and a complete revision roadmap — the knowledge expected from an experienced Java backend developer.

#Topic Review

#Query Optimization

Avoid SELECT *

-- Poor: on a 50-million-row table this is extremely expensive
SELECT * FROM employee;

-- Better: retrieve only the required columns
SELECT employee_name, salary FROM employee;

SELECT * causes:

• more disk I/O;
• more memory usage;
• more network traffic and unnecessary data transfer;
• lost covering-index opportunities;
• application code that depends on the table structure.

Enterprise rule: avoid SELECT * in production queries unless every column is genuinely required.

Use WHERE Properly

Filter unnecessary rows as early as possible:

SELECT employee_id, employee_name, salary
FROM employee
WHERE department = 'IT';

Optimize JOINs

SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

Columns used in join conditions should be indexed:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

customers.customer_id is usually already indexed as the primary key. (InnoDB also creates an index on a foreign-key column automatically if none exists.)

Use Composite Indexes

SELECT order_id, order_date, status
FROM orders
WHERE customer_id = 100
  AND order_date > '2026-01-01';

CREATE INDEX idx_customer_order_date ON orders (customer_id, order_date);

Why this order? customer_id is an equality condition and order_date is a range condition. Equality columns go first, the range column last, so the index matches the query's access pattern.

Avoid Functions on Indexed Columns

-- Bad: the function prevents efficient use of an index on order_date
SELECT * FROM orders WHERE YEAR(order_date) = 2026;

-- Better
SELECT *
FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date <  '2027-01-01';

This is also safer than BETWEEN when the column holds date and time values, because the upper boundary is explicit and exclusive. A predicate the optimizer can use an index for is called sargable.

Let the Database Count

-- Bad: fetch every row, then count in Java
SELECT * FROM orders;

-- Better: return only the count
SELECT COUNT(*) FROM orders;

EXISTS vs IN

EXISTS expresses "do matching rows exist?" clearly:

SELECT c.customer_id, c.customer_name
FROM customer c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

IN can be just as efficient in modern MySQL, because the optimizer may transform both into a semi-join. Don't assume EXISTS is always faster than IN — choose by correct semantics (remember the NOT IN + NULL trap), index properly and verify with EXPLAIN / EXPLAIN ANALYZE.

#EXPLAIN Deep Dive

EXPLAIN
SELECT * FROM employee WHERE department = 'IT';

Important type Values

For large tables, investigate an unexpected ALL. A full table scan isn't automatically wrong, but it's expensive when only a few rows are needed.

Important Extra Values

EXPLAIN ANALYZE

MySQL 8.0.18+ supports:

EXPLAIN ANALYZE
SELECT * FROM employee WHERE salary > 70000;

Unlike EXPLAIN, EXPLAIN ANALYZE executes the query and reports actual timings and row counts, so you can compare optimizer estimates with actual execution. It helps find incorrect row estimates, expensive operations, slow joins, unexpected scans and bottlenecks.

#Slow Query Log

MySQL can record slow-running queries:

slow_query_log  = ON
long_query_time = 2      # seconds

Queries slower than the threshold are logged for analysis. Production teams use the slow query log to find frequently slow queries, missing indexes, expensive scans, poor join strategies and queries needing optimization. Tools such as mysqldumpslow or Percona's pt-query-digest aggregate the log by query pattern; log_queries_not_using_indexes can also log unindexed queries.

#Pagination Strategies

LIMIT / OFFSET Pagination

SELECT *
FROM employee
ORDER BY employee_id
LIMIT 10 OFFSET 10000;

The database still has to read and skip the first 10,000 rows before returning the next 10. As the offset grows:

Keyset Pagination (Seek Method)

Instead of a large offset, continue from the last key seen:

SELECT *
FROM employee
WHERE employee_id > 10000
ORDER BY employee_id
LIMIT 10;

Benefits: efficient for large datasets, no scanning and skipping of large offsets, works well for infinite scrolling, and stable when rows are inserted between requests.

OFFSET vs Keyset

#Partitioning

A table with 500 million rows may be partitioned by year, month, region or numeric ranges:

CREATE TABLE orders (
    order_id    BIGINT NOT NULL,
    order_date  DATE   NOT NULL,
    customer_id BIGINT NOT NULL,
    amount      DECIMAL(12, 2),
    PRIMARY KEY (order_id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
);

Benefits can include partition pruning, easier archival, easier management of very large datasets and fast deletion of old data (ALTER TABLE orders DROP PARTITION p2024 instead of a huge DELETE).

Partition Pruning

With the table partitioned by year:

SELECT *
FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date <  '2027-01-01';

MySQL can scan only the relevant partition instead of all of them — partition pruning. EXPLAIN shows the partitions touched in its partitions column.

#Denormalization

A highly normalized schema means many JOINs, which can make reporting queries expensive. Enterprise reporting systems may use summary tables, denormalized schemas, precomputed aggregates, materialized-view patterns and data warehouses to improve read performance.

Denormalization should be intentional, measured and documented — don't denormalize without a demonstrated performance requirement.

#Common Production Problems

1. Missing Index

SELECT * FROM customer WHERE email = 'abc@gmail.com';

No suitable index → large table → full scan → slow query. A possible fix:

CREATE UNIQUE INDEX idx_customer_email ON customer (email);

2. The N+1 Query Problem

Common with ORM frameworks such as Hibernate. Solutions: proper JOINs, fetch joins (JOIN FETCH), entity graphs, batch fetching (@BatchSize), DTO projections and query-specific fetching strategies.

3. Cartesian Join

SELECT * FROM a, b;   -- no join condition

With 10,000 rows in each table, the result has 100,000,000 rows. Always verify join conditions.

4. Locking Issues

Locks are held for the whole time. Keep transactions short, focused and consistent, and commit or roll back quickly — don't call external services inside a transaction.

5. Too Many Indexes

Indexes help reads but add overhead to every INSERT, UPDATE and DELETE. Monitor and remove genuinely unused or redundant indexes after careful analysis (sys.schema_unused_indexes and sys.schema_redundant_indexes help).

6. Large Result Sets

Returning millions of rows to a Java application causes high database load, high network usage, high application memory and long response times. Use pagination, streaming where appropriate, filtering, aggregation and batch processing.

#Enterprise Performance Checklist

Before deploying important SQL:

• Avoid unnecessary SELECT *.
• Index according to actual query patterns.
• Verify execution plans with EXPLAIN, and EXPLAIN ANALYZE when actual metrics are needed.
• Reduce unnecessary rows and columns.
• Optimize join conditions; use covering indexes where beneficial.
• Prefer keyset pagination for large sequential datasets.
• Keep transactions short.
• Monitor slow queries, and review unused and redundant indexes.
• Measure before and after optimizing.

#Mini Project — Optimize a Query

SELECT *
FROM orders o
JOIN customer c ON o.customer_id = c.customer_id
WHERE YEAR(o.order_date) = 2026
ORDER BY o.order_date DESC;

Step 1 — remove SELECT *:

SELECT o.order_id, o.order_date, o.amount, c.customer_id, c.customer_name
FROM orders o
JOIN customer c ON o.customer_id = c.customer_id
WHERE YEAR(o.order_date) = 2026
ORDER BY o.order_date DESC;

Step 2 — rewrite the date filter:

SELECT o.order_id, o.order_date, o.amount, c.customer_id, c.customer_name
FROM orders o
JOIN customer c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01'
  AND o.order_date <  '2027-01-01'
ORDER BY o.order_date DESC;

Step 3 — add appropriate indexes:

CREATE INDEX idx_orders_order_date_customer ON orders (order_date, customer_id);

The index serves the date range and the ORDER BY (MySQL can read it backwards for DESC, avoiding a filesort), and includes customer_id for the join. Make sure customer.customer_id is indexed — normally through its primary key.

Step 4 — run EXPLAIN on the rewritten query and check type, possible_keys, key, rows, filtered and Extra.

Step 5 — explain the improvements:

Result: less work and better scalability.

#Final Practice Challenges

Easy

1. Explain GROUP BY.
2. Explain HAVING.
3. Explain LEFT JOIN.
4. Explain a primary key.
5. Explain a foreign key.

Medium

1. Write a query using a CTE.
2. Rank employees by salary.
3. Find the second-highest salary.
4. Build a running-total report.
5. Optimize a slow query.

Advanced

1. Design an e-commerce database.
2. Optimize a complex JOIN query.
3. Explain ACID.
4. Design indexes for a banking application.
5. Build a complete executive dashboard using window functions and CTEs.

#Revision Roadmap

Where to Go Next

#Key Takeaways

• Fetch only the columns and rows you need; let the database filter, aggregate and count.
• Index join columns and design composite indexes as equality columns first, range column last.
• Keep predicates sargable — no functions on indexed columns; use half-open date ranges.
• Read EXPLAIN (type, key, rows, Extra) and confirm with EXPLAIN ANALYZE.
• Use the slow query log to find what to optimize.
• Prefer keyset pagination for deep pages; partition only very large tables, and never instead of indexing.
• Watch for missing indexes, N+1 queries, Cartesian joins, long transactions, too many indexes and huge result sets.
• Measure before and after every optimization.