This content originally appeared on DEV Community and was authored by Gaurav

Optimizing SQL Queries for High‑Traffic Applications

(A practical, step‑by‑step guide that works across MySQL, PostgreSQL, SQL Server, and Oracle)

1. Why “SQL‑only” Optimisation Matters

High‑traffic apps often hit the database thousands to millions of times per second. Even a 1 ms improvement per query can save seconds of CPU time, reduce latency, and keep your infrastructure costs down. The biggest gains come from making the database do less work, not just from adding more hardware.

2. The Optimization Lifecycle

3. Foundations – “First‑Principles” Checks

4. Indexing – The Most Powerful Lever

4.1. Types of Indexes (quick reference)

DBMS	B‑Tree	Hash	GiST / SP‑GiST	BRIN	Columnstore	Full‑Text
MySQL		(Memory)	✗	(8.0+)	(InnoDB/ColumnStore)
PostgreSQL		(hash)			(cstore_fdw)
SQL Server		(Memory‑Optimized)	(spatial)	(SQL Server 2019+)	(Columnstore)
Oracle		(in‑memory)	(Spatial)	(approx.)	(Hybrid)

4.2. Index Design Process

1. Identify the hot predicates – the columns that appear in WHERE, JOIN ON, ORDER BY, GROUP BY.
2. Check cardinality – high cardinality (many distinct values) = good for B‑Tree; low cardinality may benefit from bitmap (PostgreSQL BRIN/GIN, Oracle bitmap).
3. Covering (Include) columns – add non‑key columns to an index so the query can be satisfied entirely from the index (MySQL Server INCLUDE, PostgreSQL INCLUDE, MySQL covering via SELECT that matches the index).
4. Composite index ordering – order columns by filtering power first, then by sorting/grouping.
   • Example: WHERE status = ? AND created_at > ? ORDER BY created_at DESC → (status, created_at DESC).
5. Avoid over‑indexing – each extra index adds write overhead (INSERT/UPDATE/DELETE). Keep an index‑to‑write‑ratio < 1 for OLTP workloads.

4.3. Practical Index Recipes

4.4. Maintaining Index Health

5. Query‑Plan Analysis – Reading EXPLAIN

5.1. Common Plan Nodes & Their Cost

5.2. Example: MySQL EXPLAIN

EXPLAIN FORMAT=JSON
SELECT  o.id, o.amount, c.name
FROM    orders o
JOIN    customers c ON c.id = o.customer_id
WHERE   o.status = 'paid'
  AND   o.created_at >= '2025-01-01'
ORDER BY o.created_at DESC
LIMIT 50;

Result (simplified):

{
  "query_block": {
    "select_id": 1,
    "cost_info": {"query_cost":"319.50"},
    "table": {
      "table_name":"orders",
      "access_type":"range",
      "possible_keys":["idx_status_created"],
      "key":"idx_status_created",
      "used_key_parts":["status","created_at"],
      "rows_examined_per_scan": 120,
      "filtered": 85.0,
      "attached_condition":"(`orders`.`status` = 'paid')"
    },
    "inner join":{...}
}

Interpretation

• range on idx_status_created → good (uses composite index).
• rows_examined_per_scan low → index selective.
• No separate sort node → ordering satisfied by index (created_at DESC).

If you see type: ALL (full scan) or a separate sort step, you likely need a better index or query rewrite.

5.3. PostgreSQL EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT *
FROM   orders
WHERE  status = 'paid'
  AND  created_at >= CURRENT_DATE
ORDER  BY created_at DESC
LIMIT  100;

Typical output snippet:

{
  "Plan": {
    "Node Type": "Index Scan Backward",
    "Relation Name": "orders",
    "Index Name": "idx_status_created",
    "Index Cond": "(status = 'paid'::text)",
    "Filter": "(created_at >= now())",
    "Rows Removed by Filter": 0,
    "Buffers": {"Shared Hit Blocks": 75, "Shared Read Blocks": 0},
    "Actual Total Time": 0.78,
    "Actual Loops": 1
  }
}

Key take‑aways

• Index Scan Backward = using index for descending order → no sort needed.
• Buffers shows almost all data served from cache → good.

If you see Seq Scan instead, add a composite index or rewrite the predicate.

6. Advanced Physical Design

6.1. Partitioning (Horizontal Sharding)

Best practice: Partition on a column that appears in most range predicates (e.g., created_at). Keep the partition key immutable (no updates).

-- MySQL example
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  status ENUM('new','paid','canceled') NOT NULL,
  created_at DATETIME NOT NULL,
  ... 
) PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);

6.2. Sharding (Application‑Level Horizontal Scaling)

Implementation tip: Keep the shard key in every table that needs to be joined (e.g., tenant_id). This allows joins to stay within a single shard.

6.3. Materialised Views & Summary Tables

• When: Pre‑aggregate heavy reports (GROUP BY on large fact tables).
• How: Use DB‑native materialised view (PostgreSQL CREATE MATERIALIZED VIEW, Oracle MATERIALIZED VIEW, SQL Server Indexed View).
• Refresh: REFRESH FAST (incremental) or schedule nightly REFRESH COMPLETE.

  CREATE MATERIALIZED VIEW daily_sales
  BUILD IMMEDIATE
  REFRESH FAST ON COMMIT
  AS
  SELECT product_id, DATE(order_ts) AS day,
         SUM(amount) AS total_sales
  FROM orders
  GROUP BY product_id, DATE(order_ts);

6.4. Columnstore / OLAP Optimisations

• SQL Server: CREATE CLUSTERED COLUMNSTORE INDEX on large fact tables → massive compression + vectorised scans.
• PostgreSQL: Use cstore_fdw or timescaledb hypertables for time‑series.
• MySQL: COLUMNSTORE engine (MariaDB) or InnoDB with ROW_FORMAT=COMPRESSED.

Rule of thumb: Use row‑store for OLTP (high‑frequency point reads/writes). Use column‑store for analytical queries that scan many rows but only a few columns.

7. Application‑Side Tactics

8. Monitoring & Alerting

Alert example (Prometheus)

- alert: HighQueryLatency
  expr: avg_over_time(pg_stat_statements_mean_time[5m]) > 0.02
  for: 2m
  labels:
    severity: critical
  annotations:
    summary: "Average PostgreSQL query latency > 20 ms"
    description: "Investigate slow queries; check execution plans."

9. End‑to‑End Optimization Checklist

	Item	How to Verify
1	All hot queries have covering indexes (or at least an index on filter columns).	EXPLAIN shows Index Scan / Index Only Scan.
2	No SELECT * in production code.	Code review / static analysis.
3	Statistics are up‑to‑date (ANALYZE run after bulk loads).	SELECT relname, n_live_tup FROM pg_stat_user_tables; compare with `pg_class

This content originally appeared on DEV Community and was authored by Gaurav