Relational Database Schema and SQL Query Optimization: A Practical Guide

Enes Karakulak · Published: 15.11.2026 · Database design

Relational Database Schema and SQL Query Optimization: A Practical Guide

Why SQL Query Optimization Directly Affects Reporting Times

When a report takes minutes to run, it is rarely due to insufficient server resources; it usually stems from minor decisions in schema design and SQL query structure. When shortening reporting times in client projects, I first examine how the database is structured rather than the code. Two queries returning the exact same data might take 200 ms and 40 seconds respectively; the difference typically lies in indexing, unnecessary joins, or incorrect data types.

In this article, I share the field-tested approach I apply to relational database schema and SQL query optimization, based on scenarios encountered with MySQL and PostgreSQL. What I describe is not a theoretical checklist, but repeatable, measurable steps.

Measure First: The Starting Point of Optimization

Before touching any query, I record the baseline situation. Changes made without knowing how long a query takes often create new problems rather than improvements.

  • I collect queries that exceed thresholds using the slow query log (slow_query_log in MySQL, log_min_duration_statement in PostgreSQL).
  • For each critical query, I obtain EXPLAIN and EXPLAIN ANALYZE outputs, identifying tables that perform full scans.
  • I test with realistic data volumes. A query that appears fast on a 10,000-row table behaves completely differently with 5 million rows.
  • I note execution times before and after changes, so it becomes clear which step actually worked.

Schema Design: Decisions Preceding the Query

Normalization and Intentional Denormalization

In schema design, I follow normalization rules first, moving redundant data into separate tables. However, if every reporting query requires joining five tables, I opt for intentional denormalization for frequently queried summary fields. Before making this call, I evaluate which reports run and how often.

Correct Data Types

Storing dates as text in PostgreSQL renders indexes useless. In MySQL, a column defined with utf8mb4 that only stores numbers wastes disk space and slows down comparisons. Data type selection may seem trivial, but its cumulative impact is massive.

Indexing Strategy

Indexing is one of the most misunderstood topics. Adding an index to every column is not the solution; it slows write operations and wastes storage. I follow this process:

  1. Identify columns used in WHERE and JOIN clauses.
  2. In composite indexes, order columns according to the query's filtering sequence.
  3. Detect and remove unused indexes.
  4. When building indexes on large tables, choose appropriate methods to avoid locking.

Simplifying Query Structure

Even with a sound schema, how a query is written determines its performance. Frequent adjustments I make include:

  • Selecting only required columns instead of SELECT *, especially when wide text columns exist.
  • Converting function-wrapped columns (e.g., YEAR() on date columns) into direct range comparisons to ensure index utilization.
  • Using JOIN or EXISTS where appropriate instead of nested subqueries.
  • Re-evaluating pagination methods on large datasets, as high OFFSET values become prohibitively expensive.
  • Detecting N+1 query patterns and converting them into batch queries.
The most effective way to speed up a report is often not a more powerful server, but a query that produces the same result with fewer operations.

Practical Differences Between MySQL and PostgreSQL

I apply the same principles to both systems, but the tooling differs. In PostgreSQL, EXPLAIN ANALYZE reveals actual execution times and row estimates; a large discrepancy between estimated and actual row counts indicates stale statistics. In MySQL, access types and index usage shown in EXPLAIN guide the analysis. In both engines, keeping statistics up to date is essential for the query planner to make sound decisions.

On maintenance, regular VACUUM (PostgreSQL) and table analyze / statistics refreshes prevent performance degradation over time. I treat these not as one-off fixes, but as routine maintenance steps.

Making Optimization Sustainable

One-off fixes eventually degrade because data expands and new queries are added. That is why I establish these habits in every project: evaluating newly added queries for performance during code reviews, monitoring the duration of critical reports, and making schema modifications in incremental, measured steps. In e-commerce or enterprise dashboard projects, this approach measurably slashes report generation times without inflating server infrastructure costs.

If you have slow-running reports or queries that degrade as data grows, starting with baseline measurements is the most reliable first step. The optimization sequence is clear: measure, refine schema, establish indexes, streamline queries, and measure again.

sql query optimization relational database schema database design sql performance indexing mysql postgresql query optimization database management reporting performance

This article reflects the expert's own views and experience.

Services by this expert

All services