Preface

This book is dedicated to the techies and data workers—the database administrators, developers,
data engineers, and analysts—who tirelessly keep the digital world running smoothly. As a
software engineer with over three and a half years of hands-on experience in database
optimization, I’ve poured my passion and lessons learned into these pages. From debugging slow
queries in high-traffic e-commerce platforms to fine-tuning payroll systems for instant reporting,
I’ve seen the challenges and triumphs of making databases perform at their best. This book is for
you, the heroes who ensure applications are fast, reliable, and scalable, delivering data to users in
the blink of an eye.

Database Performance Optimization: A Comprehensive Guide is designed to be your

practical companion, whether you’re tackling a sluggish startup database or scaling a global
enterprise system. What sets this book apart is its deliberate use of the PAS framework—
Problem, Agitate, Solve—to break down complex concepts. For each topic, we identify the
problem (e.g., slow queries), agitate its impact (e.g., frustrated users and lost revenue), and
provide clear solutions (e.g., indexing strategies or query rewrites). This approach ensures you
not only understand the “how” but also the “why” behind each optimization technique.

To make concepts relatable, the book is packed with real-time examples drawn from real-world
scenarios. You’ll see how a retail app slashed search times from 10 seconds to milliseconds with
indexing, or how a hospital system optimized record retrieval to save lives. Written in simple
English, this book demystifies database performance for all skill levels, empowering you to
transform your systems into high-performing engines. Here’s to every techie and data worker
driving innovation—this is for you.

1
Database Performance Optimization: A Comprehensive
Guide

1. Introduction

What is Database Performance?

Why is Performance Optimization Important?

Common Performance Issues in Databases

2. Understanding Database Performance

How Databases Process Queries (Query Lifecycle: Parse to Fetch)

Key Performance Metrics

Bottlenecks: CPU, Memory, Disk I/O, Network

3. Query Optimization Fundamentals

What is Query Optimization?

Execution Plans: What They Are and How to Read Them

Indexing Basics: Types and How They Improve Performance

Statistics and Cardinality Estimates

Practical Examples and Exercises

4. Indexing Strategies

Clustered vs Non-clustered Indexes

2
Composite Indexes

When to Create or Drop Indexes

Index Maintenance (Rebuild/Reorganize)

Additional Real-World Scenarios

5. Writing Efficient SQL Queries

Best Practices for SQL Query Writing

Avoiding Common Pitfalls

Using WHERE Clauses and Filters Effectively

Using EXISTS vs IN

Real-World Scenario: CRM System Optimization

6. Database Design and Normalization

Normal Forms and Their Impact on Performance

Denormalization: When and Why to Use It

Partitioning and Sharding Basics

7. Advanced Optimization Techniques

Materialized Views and Query Caching

Using Stored Procedures and Prepared Statements

Optimizing Joins and Subqueries

3
Use of Hints and Plan Guides

8. Hardware and Configuration Optimization

Choosing the Right Hardware

Database Configuration Parameters

Connection Pooling and Session Management

9. Monitoring and Troubleshooting

Tools for Monitoring Database Performance

Analyzing Slow Queries

Identifying and Resolving Deadlocks and Blocking

Advanced Monitoring Techniques

10. Case Studies and Real-World Examples

Case Study 1: E-Commerce Platform

Case Study 2: Healthcare System

Case Study 3: Social Media Platform – Handling Viral Content

Case Study 4: Logistics Company – Streamlining Shipment Tracking

Case Study 5: Financial Services – Reducing Report Generation Time

Before and After Metrics

11. Summary and Best Practices

4
Recap Key Takeaways

Checklist for Ongoing Optimization

Resources for Further Learning

5
1. Introduction

What is Database Performance?

Database performance is all about how well a database system does its job. Imagine a database
as the heart of an application—it pumps data to users, applications, and processes. When it works
efficiently, everything runs smoothly. When it doesn’t, delays and frustrations pile up. In
technical terms, database performance measures how quickly and accurately a database handles
operations like reading data (e.g., fetching a customer’s order history), writing data (e.g., saving
a new user profile), updating records (e.g., changing an address), or deleting information (e.g.,
removing an old product).

● Definition: Database performance is the speed, reliability, and resource efficiency with
which a database executes operations to store, retrieve, or manipulate data.
● Key Idea: Think of a database as a librarian in a massive library. A skilled librarian
knows exactly where every book is, retrieves it quickly, and organizes the shelves to save
space. A poorly performing database is like a disorganized librarian who takes ages to
find a book, misplaces items, or gets overwhelmed by too many requests.
● Why It Matters: High-performing databases ensure applications respond instantly, even
under heavy loads. They use resources like CPU, memory, and storage wisely, keeping
costs down and users happy.

Real-World Example

Consider an e-commerce giant like Amazon. When you search for a product, the database must
instantly fetch details like price, stock, and reviews. If the database takes even a few seconds,
you might get frustrated and shop elsewhere. A slow database could lead to abandoned carts,
costing the business millions. For instance, studies show that a 1-second delay in page load time
can reduce conversions by 7%. For Amazon, with billions in annual revenue, that’s a massive
loss. Fast database performance keeps customers clicking “Buy Now.”

Another Perspective

6
Think about a small business running an online bakery. Their database stores orders, customer
details, and inventory. If the database is slow, customers might wait too long to place orders, or
the bakery might oversell items that are out of stock. Optimizing performance ensures the
business runs smoothly, even during a holiday rush.

Technical Breakdown

Database performance depends on several factors:

● Speed: How fast queries return results (measured in milliseconds or seconds).

● Scalability: The ability to handle more users or data without slowing down.
● Resource Usage: Efficient use of CPU, memory, disk, and network resources.
● Reliability: Consistent performance without crashes or errors.

For example, a database processing 1,000 queries per second with 50ms latency is performing
well. But if latency spikes to 5 seconds during peak hours, users notice the lag, and performance
needs improvement.

Why is Performance Optimization Important?

Performance optimization is the process of tuning a database to run faster, use fewer resources,
and handle more work. It’s like tuning a car engine to get better mileage and speed. Without
optimization, databases become sluggish, applications fail, and businesses suffer. Optimization
ensures systems stay responsive, costs stay low, and growth isn’t hindered.

PAS Framework: Problem, Agitate, Solve

● Problem: Slow databases create delays in applications. Imagine a banking app where
checking your balance takes minutes instead of seconds. Or a travel booking site where
searching for flights feels like waiting for a dial-up connection.
● Agitate: These delays frustrate users, erode trust, and drive customers to competitors. A
slow banking app might push users to switch banks. Slow systems also cost more, as
businesses need extra servers or cloud resources to handle the same workload. Worse,

7
 poor performance can lead to downtime, lost revenue, or even safety risks in critical
systems like healthcare.
● Solve: Performance optimization makes databases lightning-fast, improving user
experience and saving money. By tuning queries, adding indexes, or adjusting
configurations, a database can handle more users with less hardware. This keeps
customers happy, reduces server bills, and prepares the system for growth.

Real-World Example: Healthcare

In a hospital, the patient management system relies on a database to store medical records.
During an emergency, doctors need instant access to a patient’s history—allergies, past
surgeries, or medications. If the database takes 10 seconds to load, it could delay treatment,
risking lives. Optimization ensures records appear in milliseconds, enabling quick decisions. For
example, a hospital in New York optimized its database by adding indexes and caching frequent
queries, reducing retrieval time from 8 seconds to 200ms, improving patient care.

Real-World Example: Gaming

Online games like Fortnite handle millions of players simultaneously. The database tracks player
stats, inventory, and match data. If the database lags, players experience delays, like waiting for a
match to start or losing progress. Epic Games, the maker of Fortnite, invests heavily in database
optimization to ensure smooth gameplay, using techniques like sharding (splitting data across
servers) and caching to keep performance snappy.

Why It Matters

Optimization impacts every aspect of a business:

● User Satisfaction: Fast systems keep users engaged. A study by Google found that 53%
of mobile users abandon a site if it takes over 3 seconds to load. Optimized databases
prevent these losses.
● Cost Efficiency: Efficient databases use less CPU, memory, and storage, reducing cloud
or server costs. For example, a company might cut its AWS bill by 30% after optimizing
its database to handle the same workload with fewer resources.

8
 ● Scalability: As businesses grow, databases must handle more users and data.
Optimization ensures the system scales without crashing. For instance, a startup’s
database might support 1,000 users today but needs to handle 100,000 users next year.
Proper tuning makes this possible.
● Competitive Advantage: Fast applications stand out. A retailer with a snappy website
gains an edge over competitors with sluggish systems.

The Cost of Ignoring Optimization

Neglecting performance can be disastrous. In 2018, a major airline’s booking system crashed due
to an unoptimized database during a holiday sale, causing hours of downtime and millions in lost
bookings. Customers flooded social media with complaints, damaging the brand. Optimization
could have prevented this by ensuring the database handled peak traffic.

Common Performance Issues in Databases

Databases face several challenges that degrade performance. Understanding these issues is the
first step to fixing them. Below are the most common problems, explained with examples and
solutions.

1. Slow Queries

● What It Is: Queries that take too long to execute, often due to poor design, missing
indexes, or complex operations.
● Example: A retail app’s search feature takes 10 seconds to find products because it scans
the entire product table without an index. With an index on the product_name column,
the same query could take 50ms.
● Impact: Slow queries frustrate users and overload the database, slowing other operations.
● Real-World Example: A music streaming service noticed users waited 12 seconds to
search for songs. The culprit? A query scanning a 10-million-row table. Adding an index
on the song_title column reduced search time to under 1 second.
● Solution: Optimize queries with indexes, rewrite inefficient SQL, or cache results for
frequent searches.

9
2. Resource Bottlenecks

● What It Is: When CPU, memory, disk I/O, or network resources are overloaded, slowing
the database.
● Example: A database server with only 4GB of RAM tries to process a 10GB dataset. It
swaps data to disk (a slow process), causing queries to take minutes instead of seconds.
● Impact: Bottlenecks create a ripple effect, slowing all database operations and affecting
application performance.
● Real-World Example: A logistics company’s database struggled during peak shipping
season. The server’s slow hard drive (HDD) couldn’t keep up with order updates.
Upgrading to SSDs cut query times by 80%.
● Solution: Monitor resource usage, upgrade hardware (e.g., more RAM, faster disks), or
optimize queries to use fewer resources.

3. Locking and Blocking

● What It Is: When multiple users or processes access the same data, one may wait
(blocking) or get stuck in a deadlock (where two processes wait for each other).
● Example: Two employees update the same customer record simultaneously. One locks
the record, forcing the other to wait, causing a 5-second delay.
● Impact: Blocking slows transactions, while deadlocks can cause errors or crashes.
● Real-World Example: A CRM system had delays when sales reps updated leads at the
same time. The database locked records during updates, causing 10-second waits.
Shortening transactions and using row-level locking fixed the issue.
● Solution: Use shorter transactions, implement row-level locking, or retry failed
transactions automatically.

4. Poor Configuration

● What It Is: Database settings not tuned for the workload, like insufficient connections,
low memory allocation, or unoptimized cache sizes.
● Example: A database allows only 50 connections but receives 200 simultaneous users
during a sale, causing crashes. Increasing the connection limit to 500 resolves the issue.

10
 ● Impact: Poor settings limit performance, even if queries and hardware are optimized.
● Real-World Example: A news website crashed during a major event because its
database wasn’t configured for high traffic. Tuning parameters like max_connections and
buffer_pool_size prevented future outages.
● Solution: Adjust configuration settings based on workload, monitor performance, and
test changes in a staging environment.

Additional Issues to Watch

● Network Latency: Slow connections between the database and application increase
response times. For example, a cloud database in Europe serving users in Asia may have
high latency. Solution: Use a content delivery network (CDN) or move the database
closer to users.
● Fragmented Indexes: Over time, indexes become fragmented, slowing queries. Regular
maintenance (rebuilding or reorganizing indexes) keeps them efficient.
● Overloaded Servers: Running multiple databases or applications on the same server can
starve resources. Dedicated servers or cloud scaling helps.

PAS Framework for Common Issues

● Problem: Slow queries, bottlenecks, locking, and poor configurations make databases
sluggish.
● Agitate: Users abandon slow apps, businesses lose money, and IT teams waste hours
troubleshooting. In critical systems, delays can have serious consequences, like delayed
medical care or financial losses.
● Solve: Identify issues with monitoring tools, optimize queries and indexes, tune
configurations, and upgrade hardware. Proactive optimization prevents problems before
they impact users.

Practical Steps to Start

1. Monitor Performance: Use tools like MySQL’s EXPLAIN, PostgreSQL’s

pg_stat_activity, or SQL Server Profiler to spot slow queries and bottlenecks.

11
 2. Analyze Logs: Check query logs to find patterns, like frequent slow queries during peak
hours.
3. Test Changes: Apply optimizations (e.g., adding an index) in a test environment to
measure impact.
4. Educate Teams: Train developers to write efficient SQL and understand database
performance.

By addressing these common issues, you can transform a sluggish database into a high-
performing powerhouse, setting the stage for the optimization techniques covered in later
chapters.

12
2. Understanding Database Performance

Database performance is the backbone of any application that relies on data. Whether it’s a
shopping website, a banking system, or a social media platform, how quickly and efficiently a
database processes requests determines the user experience and operational success. In this
chapter, we’ll explore how databases handle queries, the key metrics to measure performance,
and the bottlenecks that can slow things down. By understanding these concepts, you’ll be
equipped to identify issues and optimize your database for speed and reliability.

How Databases Process Queries (Query Lifecycle: Parse to Fetch)

Every time you send a query to a database, it goes through a series of steps known as the query
lifecycle. This process transforms your SQL command into actionable results. Understanding
each step helps you pinpoint where performance issues arise and how to fix them.

The Query Lifecycle Explained

The query lifecycle consists of four main stages: Parse, Optimize, Execute, and Fetch. Each
stage plays a critical role in delivering data to the user or application.

1. Parse: The database checks if your query is valid. It examines the syntax (is the SQL
correctly written?) and structure (are the tables and columns real?). If there’s an error,
like a misspelled table name, the process stops here.

○ Example: Consider the query SELECT * FROM users WHERE id = 5. The

database checks if users is a valid table, id is a column, and the syntax follows
SQL rules.
○ Why It Matters: Parsing errors can halt queries, and complex queries take longer
to parse. Simplifying queries reduces parsing time.
○ Real-World Scenario: A payroll system processes a query to fetch employee
salaries. If the query has a syntax error, like SELCT instead of SELECT, the
database rejects it, delaying payroll processing.

13
2. Optimize: Once the query is valid, the database’s query optimizer decides the fastest
way to execute it. It generates an execution plan, which is like a roadmap for retrieving
data. The optimizer considers factors like table size, available indexes, and data
distribution to choose the best plan.

○ Example: For SELECT * FROM users WHERE id = 5, the optimizer might use
an index on id for a quick lookup instead of scanning the entire table.
○ Why It Matters: A bad execution plan can make a query take seconds instead of
milliseconds. Understanding plans helps you optimize queries.
○ Real-World Scenario: A social media app retrieves a user’s posts with a query
like SELECT * FROM posts WHERE user_id = 123. The optimizer chooses an
index on user_id, making the feed load in under 100ms.
3. Execute: The database follows the execution plan to retrieve or modify data. This is
where the actual work happens—reading from tables, joining data, or updating records.

○ Example: For the query above, the database uses the index to find the row where
id = 5 and retrieves the user’s data.
○ Why It Matters: Execution is often the most resource-intensive step. Inefficient
plans lead to slow performance.
○ Real-World Scenario: An e-commerce platform executes a query to update
inventory after a sale. A well-optimized plan ensures the update happens
instantly, preventing overselling.
4. Fetch: The database sends the results back to the user or application. This step involves
packaging the data and transmitting it over the network.

○ Example: The user’s name and email are returned to the application, ready to
display on a webpage.
○ Why It Matters: Slow networks or large result sets can delay fetching, impacting
user experience.
○ Real-World Scenario: A mobile banking app fetches account details after a user
logs in. Fast fetching ensures the balance appears instantly.

14
Key Idea: Optimization Saves Time

Each stage of the query lifecycle consumes time and resources. Parsing a complex query might
take milliseconds, but a poorly optimized plan could make execution take seconds or minutes.
By optimizing each stage—simplifying queries, using indexes, or tuning configurations—you
can significantly reduce query time.

Real-World Example

Imagine a ride-sharing app like Uber processing a query to find nearby drivers: SELECT *
FROM drivers WHERE city = 'New York' AND status = 'available'. If parsing is slow due to
query complexity, optimization chooses a full table scan instead of an index, or execution bogs
down on a slow disk, the app delays showing drivers, frustrating users. Optimizing the query
lifecycle ensures drivers appear in seconds, keeping customers happy.

Practical Tips for the Query Lifecycle

● Simplify Queries: Break complex queries into smaller parts to reduce parsing time.
● Use Indexes: Indexes help the optimizer choose faster execution plans.
● Monitor Plans: Regularly check execution plans to ensure the database picks efficient
paths.
● Test Changes: After optimizing, test queries to confirm performance improvements.

Key Performance Metrics

To understand how well a database performs, we measure specific metrics. These numbers
reveal where problems exist and guide optimization efforts. The three main metrics are latency,
throughput, and resource usage.

1. Latency

Definition: Latency is the time a query takes to complete, from submission to result delivery. It’s
measured in milliseconds (ms) or seconds.

15
 ● Why It Matters: Low latency means faster responses, critical for user-facing
applications.
● Example: A query taking 200ms has higher latency than one taking 10ms. Users notice
delays above 100ms.
● Real-World Scenario: An online bookstore runs a query to display book details. If
latency is 500ms, customers wait half a second per click, leading to a sluggish
experience. Reducing latency to 50ms makes the site feel instant.
● How to Measure: Use database monitoring tools (e.g., MySQL’s SHOW PROFILE or
SQL Server’s Profiler) to track query times.
● Optimization Tip: Add indexes or rewrite queries to lower latency.

2. Throughput

Definition: Throughput is the number of queries a database processes per second. High
throughput means the database handles many requests simultaneously.

● Why It Matters: High throughput supports more users, essential for busy systems.
● Example: A database processing 1,000 queries/second has high throughput, while one
handling 10 queries/second struggles under load.
● Real-World Scenario: A multiplayer game processes thousands of player actions per
second (e.g., moving, attacking). High throughput ensures all actions register without lag,
keeping gameplay smooth.
● How to Measure: Monitor queries per second using tools like PostgreSQL’s
pg_stat_activity or Oracle’s AWR reports.
● Optimization Tip: Increase hardware resources or use connection pooling to boost
throughput.

3. Resource Usage

Definition: Resource usage measures how much CPU, memory, disk I/O, or network bandwidth
a database consumes during operations.

16
 ● Why It Matters: High resource usage can slow down the entire system, affecting other
queries or applications.
● Example: A query using 80% of the CPU may starve other processes, causing delays.
● Real-World Scenario: A reporting system generates sales summaries, consuming 90%
of memory. Other users experience slowdowns until the report finishes. Optimizing the
query reduces memory usage to 20%, freeing resources.
● How to Measure: Use tools like SQL Server’s DMVs or MySQL’s Performance Schema
to track resource consumption.
● Optimization Tip: Tune database configurations or add hardware to balance resource
usage.

PAS Framework for Metrics

● Problem: High latency, low throughput, or excessive resource usage makes applications
slow and unreliable.
● Agitate: Users abandon slow apps, businesses lose revenue, and IT teams spend hours
troubleshooting.
● Solve: Monitor latency, throughput, and resource usage regularly. Optimize queries, add
indexes, or upgrade hardware to improve these metrics.

Practical Tips for Monitoring Metrics

● Set Baselines: Know your database’s normal latency and throughput to spot issues.
● Use Alerts: Configure tools to notify you when latency exceeds 100ms or CPU usage hits
80%.
● Analyze Trends: Track metrics over time to predict when upgrades are needed.
● Real-World Example: A streaming service monitors throughput during peak hours (e.g.,
movie releases). When throughput drops, they add servers to maintain performance.

Bottlenecks: CPU, Memory, Disk I/O, Network

17
Bottlenecks are points where limited resources slow down database performance. Identifying and
resolving them is critical for optimization. The four main bottlenecks are CPU, memory, disk
I/O, and network.

1. CPU

Definition: The CPU processes queries, calculations, and joins. An overloaded CPU slows down
all operations.

● Why It Matters: Databases rely on CPU for tasks like sorting, aggregating, or executing
complex queries.
● Example: A query with multiple joins, like SELECT * FROM orders JOIN customers
JOIN products, maxes out the CPU, delaying other queries.
● Real-World Scenario: A financial app calculates daily stock trends. Complex
calculations overload the CPU, slowing user dashboards. Adding CPU cores or
optimizing queries fixes this.
● Signs of a CPU Bottleneck:
○ High CPU usage (e.g., 90%+ in monitoring tools).
○ Queries waiting for CPU resources.
● Solutions:
○ Simplify queries (e.g., reduce joins).
○ Add more CPU cores or upgrade to a faster processor.
○ Use parallel query execution if supported (e.g., PostgreSQL’s parallel scans).
● Practical Tip: Monitor CPU usage with tools like top (Linux) or SQL Server’s
sys.dm_os_ring_buffers.

2. Memory

Definition: Memory (RAM) stores data temporarily for quick access. Low memory forces the
database to use slower disk storage, increasing latency.

● Why It Matters: Databases cache data and execution plans in memory. Insufficient
RAM leads to frequent disk reads/writes.

18
 ● Example: A database with 4GB RAM struggles to cache a 10GB dataset, forcing slow
disk access.
● Real-World Scenario: A CRM system queries customer data frequently. With low
memory, queries take seconds instead of milliseconds. Adding 16GB RAM speeds things
up.
● Signs of a Memory Bottleneck:
○ High disk I/O due to memory swapping.
○ Low cache hit ratios (e.g., MySQL’s Innodb_buffer_pool_hit_ratio).
● Solutions:
○ Increase RAM to match dataset size.
○ Tune memory settings (e.g., MySQL’s innodb_buffer_pool_size).
○ Use indexes to reduce data scanned.
● Practical Tip: Check memory usage with PostgreSQL’s pg_stat_bgwriter or Oracle’s
V$SYSSTAT.

3. Disk I/O

Definition: Disk I/O involves reading/writing data to storage. Slow disks (e.g., HDDs vs. SSDs)
cause delays, especially for large datasets.

● Why It Matters: Databases read tables, indexes, and logs from disk. Slow I/O
bottlenecks execution.
● Example: A query scanning a 1TB table on an HDD takes minutes, while an SSD
completes it in seconds.
● Real-World Scenario: A logistics app tracks shipments in real-time. Slow disk I/O
delays updates, causing outdated tracking info. Switching to NVMe SSDs resolves this.
● Signs of a Disk I/O Bottleneck:
○ High disk read/write wait times (e.g., SQL Server’s sys.dm_io_virtual_file_stats).
○ Queries waiting for I/O completion.
● Solutions:
○ Upgrade to SSDs or NVMe drives.
○ Partition large tables to reduce I/O.

19
 ○ Cache frequently accessed data in memory.
● Practical Tip: Monitor disk I/O with tools like iostat or MySQL’s Performance Schema.

4. Network

Definition: Network bottlenecks occur when data transfer between the database and application
is slow, often due to latency or bandwidth limits.

● Why It Matters: Cloud databases or distributed systems rely on fast networks to deliver
results.
● Example: A cloud database in a distant region (e.g., US server, EU users) adds 100ms
latency per query.
● Real-World Scenario: A global e-commerce platform hosts its database in one region.
Users in other regions experience delays due to network latency. Moving to a multi-
region setup reduces latency.
● Signs of a Network Bottleneck:
○ High network latency or packet loss.
○ Slow fetch times in the query lifecycle.
● Solutions:
○ Host databases closer to users (e.g., use CDNs or regional servers).
○ Compress data transfers.
○ Optimize queries to return smaller result sets.
● Practical Tip: Use tools like ping or database-specific network metrics (e.g., Oracle’s
V$SESSION).

PAS Framework for Bottlenecks

● Problem: Bottlenecks like slow disk I/O or overloaded CPUs make queries sluggish,
impacting applications.
● Agitate: Users face delays, apps crash, and businesses lose customers or revenue. For
example, a slow e-commerce checkout process drives shoppers to competitors.
● Solve: Identify bottlenecks using monitoring tools, then apply targeted fixes like
upgrading hardware, adding indexes, or optimizing queries.

20
Real-World Example: Bottleneck in Action

A news website experiences slow page loads during breaking news events. Monitoring reveals:

● CPU: 95% usage due to complex analytics queries.

● Memory: Low cache hit ratio, forcing disk access.
● Disk I/O: High read times on an HDD.
● Network: Minimal impact, as the database is local.
Solution: The team upgrades to SSDs, adds 32GB RAM, and simplifies analytics
queries. Page load time drops from 5 seconds to 200ms, retaining readers.

Practical Tips for Managing Bottlenecks

● Monitor Regularly: Use tools like SQL Server Management Studio or pgAdmin to track
resource usage.
● Prioritize Fixes: Address the most impactful bottleneck first (e.g., disk I/O over network
if I/O is the main issue).
● Scale Smartly: Add resources (e.g., CPU cores) only after optimizing queries and
configurations.
● Test Upgrades: Measure performance before and after changes to confirm
improvements.

Conclusion

Understanding database performance starts with knowing how queries are processed, measuring
key metrics, and identifying bottlenecks. The query lifecycle (Parse, Optimize, Execute, Fetch)
reveals where time is spent, while metrics like latency, throughput, and resource usage quantify
performance. Bottlenecks—CPU, memory, disk I/O, or network—can cripple even well-
designed databases. By monitoring these areas and applying targeted optimizations, you can
ensure your database runs smoothly, supporting fast and reliable applications.

Actionable Takeaways

21
 ● Analyze Query Lifecycle: Use tools like EXPLAIN (PostgreSQL/MySQL) or SQL
Server’s Query Store to inspect execution plans.
● Track Metrics: Set up dashboards for latency, throughput, and resource usage.
● Hunt for Bottlenecks: Regularly check CPU, memory, disk, and network usage to catch
issues early.
● Test and Iterate: Optimize one area at a time (e.g., add an index), then measure the
impact.

By mastering these concepts, you’ll be ready to tackle performance issues and keep your
database running at peak efficiency.

22
3.Query Optimization Fundamentals

Query optimization is the heart of database performance. It’s about making your SQL queries
run faster, use fewer resources, and keep your applications responsive. In this chapter, we’ll
explore what query optimization is, how to read execution plans, the role of indexes, and how
statistics and cardinality estimates guide the database’s decisions. By the end, you’ll understand
how to make your queries lightning-fast and avoid common pitfalls, with real-world examples to
bring it all to life.

3.1 What is Query Optimization?

Definition and Importance

Query optimization is the process of improving SQL queries to execute faster and more
efficiently. The database’s query optimizer, a built-in component, analyzes your query and
evaluates multiple ways to execute it. It then picks the execution plan with the lowest cost,
meaning the least amount of time and resources (like CPU, memory, or disk I/O).

● Key Idea: Optimization reduces the time it takes to get results and minimizes strain on
the database, making applications faster and cheaper to run.
● Why It Matters: A poorly optimized query can take minutes instead of milliseconds,
slowing down apps and frustrating users.
● Real-World Example: Imagine a payroll system that calculates salaries for 10,000
employees. Without optimization, the query takes 5 minutes, delaying paychecks. After
optimization, it runs in 10 seconds, keeping everyone happy.

How Query Optimization Works

The query optimizer works like a GPS for your query. It looks at all possible routes (execution
plans) to get your data and chooses the fastest one. It considers factors like:

● Available indexes.
● Table sizes.

23
 ● Data distribution (via statistics).
● Hardware resources.

For example, if you query SELECT * FROM orders WHERE customer_id = 100, the optimizer
decides whether to scan the entire table or use an index on customer_id. A good optimizer picks
the index, making the query much faster.

PAS Framework: Why Optimize Queries?

● Problem: Slow queries make applications sluggish, like an e-commerce site taking ages
to show products.
● Agitate: This frustrates users, leading to lost sales or trust. Slow queries also increase
server costs as they hog resources.
● Solve: Optimizing queries cuts execution time, improves user experience, and reduces
hardware demands.

Practical Tips

● Always test queries on realistic data to see how they perform.

● Use tools like EXPLAIN (MySQL/PostgreSQL) or SHOW PLAN (SQL Server) to
understand the optimizer’s choices.
● Optimize frequently run queries first, as they impact performance the most.

Real-World Example

A retail company’s website was slow during Black Friday sales because a product search query
took 8 seconds. By optimizing the query (using an index and rewriting it to avoid unnecessary
joins), the team reduced the time to 200 milliseconds, boosting sales by 15% as customers could
browse faster.

3.2 Execution Plans: What They Are and How to Read Them

What is an Execution Plan?

24
An execution plan is a detailed roadmap the database creates to show how it will execute your
query. It breaks down the steps, like scanning a table, using an index, or joining tables, and
estimates the cost of each step.

● Definition: A step-by-step guide showing operations (e.g., scans, seeks, joins) and their
costs (time/resources).
● Why It’s Useful: Execution plans help you spot inefficiencies, like full table scans, and
fix them.
● Real-World Example: A developer noticed a report query took 10 seconds. The
execution plan showed a full table scan. Adding an index changed it to an index seek,
dropping the time to 100 milliseconds.

How to Read an Execution Plan

Execution plans are like flowcharts. Each “node” represents an operation, and arrows show the
flow of data. Here’s what to look for:

● Nodes: Each step, like “Index Scan,” “Table Scan,” or “Hash Join.”
○ Table Scan: Reads every row in a table (slow for large tables).
○ Index Scan/Seek: Uses an index to find data (faster).
● Cost: Estimated resource usage (CPU, I/O, memory). Higher cost means slower.
○ Example: A node with a cost of 100 is slower than one with a cost of 10.
● Rows: Number of rows processed at each step.
○ Example: A node processing 1 million rows is likely slower than one processing
100 rows.
● Joins: How tables are combined (e.g., Nested Loop, Merge Join).
○ Example: A “Nested Loop” is good for small datasets, while “Hash Join” suits
larger ones.

Example Execution Plan

Consider the query: SELECT * FROM orders WHERE customer_id = 100.

● Plan Output:

25
 ○ Node 1: Index Seek on customer_id (Cost: 0.01, Rows: 1).
○ Node 2: Return results to user.
● Interpretation: The database uses an index on customer_id, finds one row quickly, and
returns it. This is fast because it avoids scanning the entire table.

How to Access Execution Plans

● MySQL: Use EXPLAIN SELECT * FROM orders WHERE customer_id = 100;.

● PostgreSQL: Use EXPLAIN ANALYZE SELECT * FROM orders WHERE
customer_id = 100;.
● SQL Server: Enable “Show Execution Plan” in Management Studio.
● Oracle: Use EXPLAIN PLAN FOR SELECT * FROM orders WHERE customer_id =
100;.

Real-World Example

A logistics company’s tracking system was slow, taking 15 seconds to show shipment details.
The execution plan revealed a full table scan on a 10-million-row table. Adding an index on
shipment_id changed it to an index seek, reducing the time to 50 milliseconds.

PAS Framework: Why Use Execution Plans?

● Problem: You don’t know why a query is slow.

● Agitate: Slow queries waste time, frustrate users, and increase costs.
● Solve: Execution plans pinpoint inefficiencies (e.g., missing indexes), letting you fix
them quickly.

Practical Tips

● Check execution plans for critical queries regularly.

● Look for high-cost operations like table scans or expensive joins.
● Compare plans before and after optimization to measure improvement.

26
3.3 Indexing Basics: Types and How They Improve Performance

What is an Index?

An index is a special data structure that helps the database find rows faster, much like the index
in a book helps you find a page without flipping through every one.

● Definition: A database index is a copy of selected columns, organized for quick lookups.
● Why It’s Important: Indexes reduce the number of rows the database needs to scan,
speeding up queries.
● Real-World Example: A library database with an index on book titles finds “Harry
Potter” in milliseconds instead of scanning every book record.

Types of Indexes

There are two main types of indexes: clustered and non-clustered. Each works differently and
impacts performance in unique ways.

Clustered Index

● Definition: A clustered index determines the physical order of data in a table. Because it
defines how data is stored, a table can have only one clustered index.
● How It Works: Rows are sorted based on the indexed column, like organizing books
alphabetically on a shelf.
● Example: A table with a clustered index on order_id stores orders in ascending order_id
order, making lookups by order_id very fast.
● When to Use: For columns frequently used in WHERE, ORDER BY, or range queries
(e.g., SELECT * FROM orders WHERE order_id BETWEEN 1000 AND 2000).

Non-clustered Index

● Definition: A non-clustered index is a separate structure that points to the actual data. A
table can have multiple non-clustered indexes.
● How It Works: It’s like a card catalog in a library, listing book titles and their locations
without moving the books.

27
 ● Example: A non-clustered index on customer_name allows fast searches by name
without affecting the table’s physical order.
● When to Use: For columns used in searches, joins, or filters, like customer_name or
email.

How Indexes Improve Performance

Indexes work by reducing the amount of data the database needs to read. Without an index, the
database performs a full table scan, checking every row. With an index, it can jump directly to
the relevant rows.

● Example: For SELECT * FROM users WHERE email = 'john@example.com':

○ Without Index: Scans all 1 million rows, taking seconds.
○ With Index: Looks up the email in the index, finding the row in milliseconds.
● Key Benefit: Indexes can turn slow queries into near-instant ones, especially for large
tables.

Trade-offs of Indexes

● Pros:
○ Faster SELECT queries.
○ Improved performance for WHERE, JOIN, and ORDER BY.
● Cons:
○ Slows down INSERT, UPDATE, and DELETE because the index must be
updated.
○ Uses extra storage space.
● Example: Adding an index on email speeds up searches but makes adding new users
slightly slower.

Real-World Example

A university database was slow when searching for students by last name. A full table scan on
50,000 records took 5 seconds. Adding a non-clustered index on last_name reduced it to 20
milliseconds, making the registrar’s job much easier.

28
PAS Framework: Why Use Indexes?

● Problem: Queries are slow because the database scans every row.
● Agitate: This delays apps, frustrates users, and wastes resources.
● Solve: Indexes let the database find data quickly, like a shortcut to the right answer.

Practical Tips

● Create indexes on columns used in WHERE, JOIN, or ORDER BY.

● Avoid indexing columns with low cardinality (e.g., gender with only “M” or “F”).
● Monitor index usage to remove unused ones and save space.

3.4 Statistics and Cardinality Estimates

What are Statistics?

Statistics are metadata that describe the distribution of data in a table or index. They help the
query optimizer make informed decisions about the best execution plan.

● Definition: Statistics include details like the number of rows, unique values in a column,
and data distribution.
● Why They Matter: Without accurate statistics, the optimizer might choose a slow plan,
like scanning a table when an index would be faster.
● Example: Statistics on a city column show 10,000 unique cities, helping the optimizer
pick an index for WHERE city = 'New York'.

Cardinality: The Key to Smart Plans

Cardinality refers to the number of unique values in a column. It’s a critical factor in query
optimization.

● Definition: High cardinality means many unique values (e.g., email or user_id). Low
cardinality means few unique values (e.g., status with “active” or “inactive”).

29
 ● How It Helps: High-cardinality columns are ideal for indexes because they narrow down
results quickly.
● Example: A user_id column with 1 million unique IDs has high cardinality, making it a
great candidate for an index. A status column with only two values has low cardinality,
so an index may not help.

How Statistics Work

The database collects statistics automatically or when you run commands like ANALYZE
(PostgreSQL/MySQL) or UPDATE STATISTICS (SQL Server). These statistics include:

● Row Count: Total rows in a table.

● Unique Values: Number of distinct values in a column.
● Histograms: Distribution of values (e.g., how many rows have city = 'New York').
● Example: If statistics show 90% of orders are from 2024, the optimizer might choose a
range scan for WHERE order_date > '2023-12-31'.

Real-World Example

A marketing database had a slow query filtering customers by age group. Statistics revealed that
the age column had high cardinality (many unique ages), so the optimizer chose an index scan,
reducing query time from 3 seconds to 50 milliseconds.

When Statistics Go Wrong

Outdated statistics can lead to poor plans. For example, if a table grows from 1,000 to 1 million
rows but statistics aren’t updated, the optimizer might underestimate the cost of a table scan.

● Solution: Regularly update statistics, especially after large data changes.

● Example Command:
○ PostgreSQL: ANALYZE table_name;
○ SQL Server: UPDATE STATISTICS table_name;
○ MySQL: ANALYZE TABLE table_name;

PAS Framework: Why Update Statistics?

30
 ● Problem: Outdated statistics cause the optimizer to pick slow plans.
● Agitate: Queries take longer, apps lag, and users complain.
● Solve: Regular statistic updates ensure the optimizer makes smart choices, keeping
queries fast.

Practical Tips

● Update statistics after significant data changes (e.g., bulk inserts or deletes).
● Use high-cardinality columns for indexes to maximize their effectiveness.
● Monitor query performance to spot when statistics are outdated.

3.5 Practical Examples and Exercises

Example 1: Optimizing a Slow Query

Scenario: An e-commerce app has a slow query: SELECT * FROM products WHERE category
= 'electronics' AND price < 500.

● Problem: Execution plan shows a full table scan on 1 million rows.

● Solution:
1. Check statistics: Run ANALYZE products; to ensure accurate data distribution.
2. Create an index: CREATE INDEX idx_category_price ON products(category,
price);.
3. Rerun the query: Plan now shows an index scan, reducing time from 5 seconds to
100 milliseconds.
● Takeaway: Combining statistics and indexes fixes slow queries.

Example 2: Reading an Execution Plan

Query: SELECT name FROM customers WHERE city = 'Chicago' AND signup_date > '2024-
01-01'.

● Plan:

31
 ○ Node 1: Index Scan on idx_city_signup (Cost: 0.05, Rows: 500).
○ Node 2: Return name column.
● Analysis: The index on (city, signup_date) makes the query fast. Without it, a table scan
would process 100,000 rows.
● Exercise: Run EXPLAIN on a similar query in your database and identify the costliest
node.

Example 3: Cardinality in Action

Scenario: A blog platform queries SELECT * FROM posts WHERE author_id = 123. The
author_id column has high cardinality (10,000 unique authors).

● Solution: Create an index on author_id. The optimizer uses it because high cardinality
ensures selective results.
● Result: Query time drops from 2 seconds to 10 milliseconds.
● Exercise: Identify a high-cardinality column in your database and test query performance
with and without an index.

Common Mistakes and How to Avoid Them

Mistake 1: Ignoring Execution Plans

● Problem: Developers assume queries are fast without checking plans.

● Solution: Always use EXPLAIN or equivalent to verify the optimizer’s choices.
● Example: A query seemed fast in testing but slowed in production due to a table scan.
Checking the plan caught it early.

Mistake 2: Over-Indexing

● Problem: Creating too many indexes slows INSERT and UPDATE operations.
● Solution: Monitor index usage (e.g., pg_stat_user_indexes in PostgreSQL) and drop
unused ones.

32
 ● Example: A database with 10 indexes on a small table slowed updates by 30%.
Removing unused indexes fixed it.

Mistake 3: Outdated Statistics

● Problem: The optimizer picks bad plans because statistics don’t reflect current data.
● Solution: Schedule regular statistic updates, especially after data imports.
● Example: A CRM system’s query slowed after a bulk import. Running ANALYZE
restored performance.

Key Takeaways

● Query Optimization: The process of making queries faster by choosing efficient

execution plans.
● Execution Plans: Roadmaps showing how queries are executed. Check for high-cost
operations like table scans.
● Indexes: Speed up data retrieval but add overhead to writes. Use clustered for physical
order, non-clustered for flexibility.
● Statistics and Cardinality: Guide the optimizer with accurate metadata. High-cardinality
columns are best for indexes.
● Actionable Steps:
○ Use EXPLAIN to analyze every critical query.
○ Create indexes on frequently queried columns.
○ Update statistics regularly to keep plans accurate.

Further Learning

● Books: “SQL Performance Explained” by Markus Winand.

● Online Resources:
○ MySQL: EXPLAIN documentation.

33
 ○ PostgreSQL: EXPLAIN ANALYZE guide.
○ SQL Server: Query Store tutorials.
● Tools: Try pgAdmin, SQL Server Management Studio, or MySQL Workbench to
visualize execution plans.
● Exercise: Optimize a slow query in your database by adding an index and checking the
execution plan before and after.

4: Indexing Strategies for Database Performance

34
Indexing is one of the most powerful tools for optimizing database performance. Indexes allow
databases to find and retrieve data quickly, reducing the time it takes to process queries.
However, improper use of indexes can waste storage and slow down operations like inserts or
updates. This chapter explores indexing strategies, including clustered and non-clustered
indexes, composite indexes, when to create or drop indexes, and index maintenance. By the end,
you’ll understand how to design and manage indexes to keep your database running smoothly.

4.1 Clustered vs Non-clustered Indexes

Indexes are like the index at the back of a book—they help you find information quickly without
scanning every page. Databases use two main types of indexes: clustered and non-clustered.
Understanding their differences is key to using them effectively.

Clustered Indexes

A clustered index determines the physical order of data in a table. It’s like organizing books on
a shelf by their ID numbers. Since the data is stored in the order of the index, a table can have
only one clustered index. This makes clustered indexes highly efficient for queries that retrieve
data in a specific order or range.

● Definition: A clustered index sorts and stores the table’s data rows based on the index
key. It defines the physical layout of the data.
● Key Characteristics:
○ Only one per table, as it dictates how data is physically stored.
○ Fast for range queries (e.g., WHERE order_date BETWEEN '2024-01-01' AND
'2024-12-31') and ordered results (e.g., ORDER BY order_id).
○ Updates to the indexed column may require reorganizing the table, which can be
slower.
● Example: In a table of orders, a clustered index on order_id stores rows in ascending
order of order_id. A query like SELECT * FROM orders WHERE order_id = 12345
retrieves the data quickly because the rows are already sorted.

35
 ● Real-World Example: A school database uses a clustered index on student_id in the
students table. When retrieving student records by ID (e.g., for attendance or grading),
the database finds the data instantly because the rows are physically sorted by student_id.

Non-clustered Indexes

A non-clustered index is a separate structure that contains pointers to the actual data, like a
library card catalog pointing to books on shelves. A table can have multiple non-clustered
indexes, making them versatile for speeding up various queries.

● Definition: A non-clustered index stores a copy of the indexed columns and pointers to
the table’s data. It doesn’t affect the physical order of the table.
● Key Characteristics:
○ Multiple non-clustered indexes can exist on a single table.
○ Useful for searches on columns not covered by the clustered index (e.g., WHERE
last_name = 'Smith').
○ Requires additional storage space since it’s a separate structure.
● Example: In the orders table, a non-clustered index on customer_name speeds up queries
like SELECT * FROM orders WHERE customer_name = 'John Doe'. The index points to
the relevant rows without scanning the entire table.
● Real-World Example: The same school database uses a non-clustered index on
last_name in the students table. When teachers search for students by last name (e.g., to
generate class lists), the database uses the index to find matches quickly.

Clustered vs Non-clustered: When to Use

● Use Clustered Indexes:

○ For columns frequently used in range queries or sorting (e.g., order_id, date).
○ When you need fast retrieval of large datasets in a specific order.
● Use Non-clustered Indexes:
○ For columns used in frequent searches, joins, or filters (e.g., email,
phone_number).
○ When you need multiple indexes to support different query patterns.

36
 ● PAS Framework:
○ Problem: Queries on unsorted columns take too long, scanning entire tables.
○ Agitate: Slow queries frustrate users, increase server load, and hurt business
performance.
○ Solve: Use a clustered index for primary keys or range queries and non-clustered
indexes for secondary searches to speed up data retrieval.
● Real-World Example: An e-commerce platform uses a clustered index on order_date to
quickly retrieve recent orders and a non-clustered index on product_id to speed up
product-based searches. This ensures customers see order histories and product details
instantly.

Practical Considerations

● Storage: Clustered indexes don’t require extra storage (they are the table), but non-
clustered indexes do, as they store a separate copy of the indexed data.
● Performance Trade-offs: Clustered indexes slow down INSERT and UPDATE
operations slightly because the table must be reordered. Non-clustered indexes slow
down writes more due to maintaining the separate index structure.
● Example: A payroll system uses a clustered index on employee_id for fast lookups but
avoids excessive non-clustered indexes to keep payroll updates quick.

4.2 Composite Indexes

What Are Composite Indexes?

A composite index (also called a multi-column index) includes two or more columns in a single
index. It’s like a phone book sorted by both last name and first name, making it faster to find
someone when you know both. Composite indexes are powerful for queries that filter or sort on
multiple columns.

● Definition: A composite index is a single index that combines multiple columns to

optimize queries with conditions on those columns.
● Key Characteristics:

37
 ○ Speeds up queries with WHERE, JOIN, or ORDER BY clauses involving
multiple columns.
○ Order of columns in the index matters (most selective column should come first).
○ Can be non-clustered (most common) or clustered (rare, as it’s limited to one per
table).
● Example: In an employees table, a composite index on (department, hire_date) speeds up
queries like SELECT * FROM employees WHERE department = 'HR' AND hire_date >
'2023-01-01'. The database uses the index to narrow down rows efficiently.
● Real-World Example: A logistics app uses a composite index on (warehouse_id,
product_id) in the inventory table. When checking stock levels (e.g., SELECT quantity
FROM inventory WHERE warehouse_id = 5 AND product_id = 123), the query runs in
milliseconds instead of seconds.

When to Use Composite Indexes

● Use Cases:
○ Queries with multiple conditions in WHERE clauses (e.g., WHERE column1 = X
AND column2 = Y).
○ Queries with sorting on multiple columns (e.g., ORDER BY column1, column2).
○ Joins involving multiple columns.
● Column Order Matters:
○ Place the most selective column (with the most unique values) first.
○ Example: If department has 10 unique values and hire_date has 1,000, index on
(department, hire_date) for better performance.
● PAS Framework:
○ Problem: Queries filtering on multiple columns scan large tables, slowing down
applications.
○ Agitate: Slow searches frustrate users, increase server costs, and delay critical
operations.
○ Solve: Create composite indexes on frequently used column combinations to
reduce query time dramatically.

38
 ● Real-World Example: A travel booking system uses a composite index on
`(flight_date！

, destination)` to quickly find flights matching a user’s search criteria, reducing response time
from 3 seconds to 100ms.

Practical Tips

● Limit Columns: Include only the columns needed for queries, as each additional column
increases index size.
● Monitor Usage: Check if the composite index is used via execution plans (e.g.,
EXPLAIN in MySQL/PostgreSQL).
● Example: A query like SELECT * FROM employees WHERE department = 'HR' may
not use a (department, hire_date) index fully unless hire_date is also filtered.

4.3 When to Create or Drop Indexes

Indexes are a trade-off: they speed up reads but slow down writes (INSERT, UPDATE,
DELETE). Knowing when to create or drop indexes is critical for balancing performance.

When to Create Indexes

● Create Indexes:
○ For columns frequently used in WHERE, JOIN, GROUP BY, or ORDER BY
clauses.
○ When execution plans show full table scans for slow queries.
○ For columns with high selectivity (many unique values, like user_id).
● Examples:
○ Create an index on email in a users table for login queries (WHERE email =
'user@example.com').
○ Add an index on order_date for a report query like SELECT * FROM orders
WHERE order_date > '2024-01-01'.

39
 ● Real-World Example: A blog platform adds an index on post_title to speed up search
queries, reducing search time from 5 seconds to 200ms.
● PAS Framework:
○ Problem: Queries scanning entire tables take too long, slowing down apps.
○ Agitate: Users leave due to slow performance, and servers struggle under load.
○ Solve: Add indexes on frequently queried columns to make searches lightning-
fast.

When to Drop Indexes

● Drop Indexes:
○ If they’re rarely used (check with query logs or execution plans).
○ When they slow down INSERT, UPDATE, or DELETE operations excessively.
○ If storage space is limited (indexes consume disk space).
● Examples:
○ Drop an index on a comments column rarely used in searches to speed up
comment inserts.
○ Remove an old index on a deprecated status column after a system upgrade.
● Real-World Example: A social media platform drops unused indexes on old
post_category columns after migrating to a new tagging system, freeing up 10GB of
storage and speeding up post creation by 20%.
● Practical Tip: Use database tools (e.g., pg_stat_user_indexes in PostgreSQL) to identify
unused indexes and drop them safely.

Balancing Indexes

● Too Few Indexes: Queries are slow due to table scans.

● Too Many Indexes: Writes are slow, and storage costs increase.
● Example: A small e-commerce database with 5 indexes performs well, but adding 20
more slows down order processing without significant query gains.
● Real-World Example: A CRM system maintains 3-5 key indexes per table, regularly
reviewing usage to avoid performance degradation.

40
4.4 Index Maintenance (Rebuild/Reorganize)

Indexes can become fragmented over time as data is inserted, updated, or deleted, leading to
slower performance. Regular maintenance keeps indexes efficient.

What is Index Fragmentation?

Fragmentation occurs when index pages (data structures storing index entries) become
disorganized, causing the database to read more pages than necessary.

● Definition: Fragmentation is the scattering of index data across non-contiguous pages,

increasing I/O and query time.
● Example: After 1,000 updates to a customers table, the index on customer_id becomes
fragmented, slowing searches.
● Impact: Fragmented indexes increase disk reads, raising query latency by 20-50%.

Rebuild: Restoring Index Efficiency

● Definition: Rebuilding an index re-creates it from scratch, removing fragmentation and

optimizing structure.
● When to Rebuild:
○ After heavy INSERT, UPDATE, or DELETE operations.
○ When fragmentation exceeds 30% (check with database tools like DBCC
SHOWCONTIG in SQL Server).
● Example: Rebuilding an index on order_id after a bulk update of 10,000 orders reduces
query time from 500ms to 50ms.
● Real-World Example: A bank rebuilds indexes on its transactions table monthly to
ensure fast processing during peak hours, maintaining sub-second response times.

Reorganize: Lightweight Maintenance

● Definition: Reorganizing an index reorders pages without rebuilding the entire structure,
using fewer resources.
● When to Reorganize:

41
 ○ For moderate fragmentation (10-30%).
○ As part of regular maintenance (e.g., weekly).
● Example: Reorganizing an index on product_id weekly keeps search queries fast without
the overhead of a full rebuild.
● Real-World Example: An online retailer reorganizes indexes on its inventory table
every weekend to maintain performance during sales events.

Maintenance Strategies

● Automate Maintenance:
○ Use database jobs (e.g., SQL Server Agent, PostgreSQL pg_cron) to schedule
rebuilds/reorganizes.
○ Example: Schedule reorganizations weekly and rebuilds monthly.
● Monitor Fragmentation:
○ Check fragmentation levels with tools like pgstattuple (PostgreSQL) or
sys.dm_db_index_physical_stats (SQL Server).
● Real-World Example: A streaming service monitors index fragmentation daily and
rebuilds indexes when fragmentation exceeds 25%, ensuring smooth video metadata
searches.
● PAS Framework:
○ Problem: Fragmented indexes slow down queries, increasing latency.
○ Agitate: Slow performance frustrates users and risks system downtime during
peak loads.
○ Solve: Regular index maintenance (rebuild or reorganize) keeps queries fast and
efficient.

Practical Tips for Index Maintenance

● Rebuild vs Reorganize:
○ Rebuild for heavily fragmented indexes or after major data changes.
○ Reorganize for routine upkeep to minimize resource usage.
● Online vs Offline:

42
 ○ Use online operations (e.g., REBUILD INDEX ... ONLINE in SQL Server) to
avoid locking tables during maintenance.
● Storage Considerations: Rebuilding requires temporary disk space (up to 2x the index
size).
● Example: A database with a 10GB index needs 20GB free during a rebuild.
● Real-World Example: A healthcare system uses online index rebuilds during off-peak
hours to maintain patient record access without downtime.

4.5 Additional Real-World Scenarios

Scenario 1: Retail Inventory System

● Problem: A retail chain’s inventory queries were slow, taking 10 seconds to check stock
levels across warehouses.
● Analysis: Execution plans showed full table scans on the inventory table due to missing
indexes.
● Solution:
○ Added a composite index on (warehouse_id, product_id).
○ Rebuilt indexes after bulk inventory updates.
● Result: Query time dropped to 150ms, improving cashier efficiency and customer
satisfaction.

Scenario 2: Social Media Platform

● Problem: User profile searches by username were slow, impacting user experience.
● Analysis: No index on the username column, causing table scans.
● Solution:
○ Created a non-clustered index on username.
○ Dropped an unused index on a deprecated bio column.
● Result: Search time reduced from 4 seconds to 100ms, and storage usage decreased by
5GB.

Scenario 3: Financial Reporting

43
 ● Problem: Monthly financial reports took hours to generate due to fragmented indexes.
● Analysis: High fragmentation (40%) on the transactions table’s clustered index.
● Solution:
○ Scheduled weekly reorganizations and monthly rebuilds.
○ Added a composite index on (transaction_date, account_id) for report queries.
● Result: Report generation time dropped from 2 hours to 10 minutes.

Best Practices for Indexing

● Analyze Query Patterns: Use query logs to identify frequently used columns and create
indexes accordingly.
● Limit Index Count: Aim for 3-5 indexes per table to balance read and write
performance.
● Choose Selective Columns: Index columns with high cardinality (many unique values)
for better efficiency.
● Regular Maintenance: Schedule index reorganizations weekly and rebuilds monthly or
after major updates.
● Test Changes: Use a staging environment to test new indexes before applying them to
production.
● Monitor Usage: Drop unused indexes to save space and improve write performance.

Common Pitfalls to Avoid

● Over-Indexing: Too many indexes slow down writes and waste storage.
○ Example: A table with 15 indexes takes 2x longer to insert new rows.
● Indexing Low-Cardinality Columns: Indexes on columns with few unique values (e.g.,
gender) are rarely used.
○ Example: An index on status with values active/inactive is inefficient.
● Ignoring Maintenance: Unmaintained indexes become fragmented, slowing queries.
○ Example: A fragmented index doubles query time after heavy updates.
● Wrong Column Order in Composite Indexes: Placing less selective columns first
reduces index efficiency.

44
 ○ Example: Indexing (hire_date, department) instead of (department, hire_date) for
WHERE department = 'HR' queries.

Tools for Indexing

● SQL Server: Use sys.dm_db_index_usage_stats to track index usage and DBCC

SHOWCONTIG for fragmentation.
● MySQL/PostgreSQL: Use EXPLAIN or ANALYZE to check if indexes are used in
queries.
● Oracle: Use DBA_INDEXES to monitor index health and ALTER INDEX ... REBUILD
for maintenance.
● Example: A developer uses EXPLAIN in PostgreSQL to confirm a new index on email
is used, reducing query time by 80%.

Summary

Indexes are critical for fast database performance but require careful planning. Clustered indexes
organize data for quick range queries, while non-clustered indexes support flexible searches.
Composite indexes optimize multi-column queries, but column order matters. Create indexes for
frequently queried columns, drop unused ones, and maintain them to avoid fragmentation. By
following these strategies, you can ensure your database delivers fast, reliable performance for
any application.

5. Writing Efficient SQL Queries

Writing efficient SQL queries is one of the most critical skills for improving database
performance. A well-crafted query retrieves data quickly, uses minimal resources, and scales
with growing data and user demands. Poorly written queries, on the other hand, can slow down
applications, frustrate users, and strain hardware. This chapter explores best practices for writing
efficient SQL queries, common pitfalls to avoid, effective use of WHERE clauses and filters, and

45
when to use EXISTS versus IN. By mastering these techniques, you’ll ensure your database
performs optimally, even under heavy workloads.

Best Practices for SQL Query Writing

Efficient SQL queries are concise, targeted, and designed to minimize database workload.
Following best practices reduces execution time, lowers resource usage, and improves
application responsiveness. Let’s explore key strategies with practical examples.

Select Only Needed Columns

Using SELECT * retrieves all columns from a table, even those you don’t need, increasing data
transfer and processing time. Instead, specify only the columns required for your application.

● Why It Matters: Fetching unnecessary columns wastes memory, CPU, and network
bandwidth. For large tables, this can significantly slow queries.
● Example: Suppose you’re building a user profile page that only needs a user’s name and
email. Compare these queries:
○ Inefficient: SELECT * FROM users WHERE id = 123;
■ Retrieves all columns (e.g., id, name, email, address, phone, etc.), even if
only two are needed.
○ Efficient: SELECT name, email FROM users WHERE id = 123;
■ Retrieves only name and email, reducing data transfer.
● Real-World Example: A news app displays the latest 20 articles on its homepage. Using
SELECT title, summary FROM articles ORDER BY published_date DESC LIMIT 20
instead of SELECT * cuts query time from 500ms to 50ms, improving page load speed.
● PAS Framework:
○ Problem: Using SELECT * fetches unneeded data, slowing queries.
○ Agitate: This increases latency, frustrates users, and strains servers, especially
during peak traffic.
○ Solve: Specify only required columns to reduce resource usage and speed up
queries.

46
Use Specific Conditions with WHERE

The WHERE clause filters rows before processing, reducing the dataset the database must
handle. Specific conditions ensure only relevant rows are retrieved.

● Why It Matters: Without a WHERE clause, the database processes all rows, even those
irrelevant to the query, wasting resources.
● Example: A retail database has 1 million orders. To find recent orders:
○ Inefficient: SELECT * FROM orders; (processes all 1M rows).
○ Efficient: SELECT order_id, total FROM orders WHERE order_date > '2024-01-
01'; (processes only recent orders).
● Tips:
○ Use precise conditions (e.g., order_date > '2024-01-01' instead of scanning all
dates).
○ Ensure columns in WHERE clauses are indexed for faster filtering.
● Real-World Example: An e-commerce platform filters products by category and price
range: SELECT name, price FROM products WHERE category = 'electronics' AND
price < 500;. This retrieves only relevant products, reducing query time from 2 seconds to
100ms.
● PAS Framework:
○ Problem: Queries without WHERE clauses process unnecessary data.
○ Agitate: This slows applications and overloads servers, especially with large
datasets.
○ Solve: Use specific WHERE conditions to filter data early, improving
performance.

Limit Results with LIMIT or TOP

Using LIMIT (MySQL, PostgreSQL) or TOP (SQL Server) restricts the number of rows
returned, reducing processing and data transfer.

● Why It Matters: Returning fewer rows saves resources, especially for applications
displaying paginated data.

47
 ● Example: A blog platform wants to show 10 recent posts:
○ Inefficient: SELECT * FROM posts ORDER BY created_date DESC; (returns all
posts).
○ Efficient: SELECT title, author FROM posts ORDER BY created_date DESC
LIMIT 10; (returns only 10 posts).
● Real-World Example: A social media app displays a user’s latest 20 posts. Using
SELECT post_id, content FROM posts WHERE user_id = 123 ORDER BY timestamp
DESC LIMIT 20 ensures fast feed loading, even with millions of posts.
● Tip: Combine LIMIT with OFFSET for pagination (e.g., LIMIT 10 OFFSET 20 for page
3).

Additional Best Practices

● Use Meaningful Aliases: Improve readability with clear column and table aliases (e.g.,
SELECT u.name AS user_name FROM users u;).
● Avoid Unnecessary Sorting: Only use ORDER BY when needed, as sorting is resource-
intensive.
○ Example: Skip ORDER BY for internal queries where order doesn’t matter.
● Batch Updates/Deletes: Process data in smaller chunks to avoid locking and
performance issues.
○ Example: DELETE FROM logs WHERE created_date < '2023-01-01' LIMIT
1000; in a loop.

Real-World Scenario: News App Optimization

A news app struggled with slow homepage loading due to a query fetching all articles: SELECT
* FROM articles ORDER BY published_date DESC;. The database processed 500,000 rows,
taking 5 seconds. By applying best practices:

● Changed to SELECT title, summary, published_date FROM articles WHERE

published_date > '2024-01-01' ORDER BY published_date DESC LIMIT 20;.
● Added an index on published_date.

48
 ● Result: Query time dropped to 50ms, and page load time improved from 6 seconds to 1
second, boosting user engagement.

Avoiding Common Pitfalls

Writing inefficient queries can degrade performance, even with a well-designed database. Below
are common mistakes and how to avoid them, with examples to illustrate the impact.

Avoid SELECT *

Using SELECT * retrieves all columns, even those not needed, increasing memory usage, disk
I/O, and network traffic.

● Why It’s Bad: Unnecessary columns increase processing time and may prevent index-
only scans.
● Example:
○ Inefficient: SELECT * FROM customers WHERE customer_id = 1000; (fetches
20 columns, including large fields like address or notes).
○ Efficient: SELECT first_name, last_name FROM customers WHERE
customer_id = 1000; (fetches only 2 columns).
● Impact: In a table with 1 million rows and 50 columns, SELECT * might take 2 seconds,
while selecting 2 columns takes 200ms.
● Real-World Example: A CRM system generating reports used SELECT *, fetching 50
columns when only 5 were needed. Switching to specific columns reduced report
generation time by 50%, from 10 seconds to 5 seconds.
● PAS Framework:
○ Problem: SELECT * retrieves unneeded data, slowing queries.
○ Agitate: This delays reports, frustrates users, and increases server costs.
○ Solve: List only required columns in SELECT statements.

Minimize Joins

49
Joins combine data from multiple tables, but too many or poorly designed joins increase
complexity and slow queries.

● Why It’s Bad: Each join requires matching rows across tables, which can be slow
without indexes or with large datasets.
● Example:
○ Inefficient: Joining 5 tables to get customer order details: SELECT * FROM
customers c JOIN orders o ON c.id = o.customer_id JOIN order_items oi ON o.id
= oi.order_id JOIN products p ON oi.product_id = p.id JOIN categories cat ON
p.category_id = cat.id;.
○ Efficient: Simplify by fetching only needed data: SELECT c.name, o.order_date
FROM customers c JOIN orders o ON c.id = o.customer_id;.
● Tip: Use indexed columns in ON conditions and avoid joining unnecessary tables.
● Real-World Example: A logistics app used a 6-table join to track shipments, taking 3
seconds. Simplifying to a 2-table join and using indexes reduced query time to 150ms.

Avoid Functions on Indexed Columns

Applying functions (e.g., UPPER, SUBSTRING) to indexed columns prevents the database from
using the index, forcing slower table scans.

● Why It’s Bad: Indexes store raw column values. Functions like UPPER(name) transform
the data, making the index unusable.
● Example:
○ Inefficient: SELECT * FROM employees WHERE UPPER(last_name) =
'SMITH'; (full table scan).
○ Efficient: SELECT * FROM employees WHERE last_name = 'Smith'; (uses
index on last_name).
● Workaround: If case-insensitive searches are needed, use a case-insensitive index or
store data in a consistent case.
● Real-World Example: A search feature in a library database used WHERE
LOWER(title) = 'harry potter', slowing queries. Rewriting to WHERE title = 'Harry
Potter' and using an index cut search time from 1 second to 20ms.

50
Additional Pitfalls to Avoid

● Correlated Subqueries: Subqueries that run for each row are slow.
○ Example: Replace SELECT * FROM orders WHERE id IN (SELECT order_id
FROM order_items WHERE quantity > 10) with a join.
● Overusing Wildcards in LIKE: WHERE name LIKE '%john%' scans all rows. Use
WHERE name LIKE 'john%' for prefix searches with indexes.
● Ignoring Data Types: Mismatched data types (e.g., comparing strings to numbers)
prevent index usage.
○ Example: WHERE employee_id = '123' (string) is slower than WHERE
employee_id = 123 (integer).

Real-World Scenario: CRM System Optimization

A CRM system’s sales report query used SELECT *, included 4 unnecessary joins, and applied
UPPER to an indexed column, taking 12 seconds. The team:

● Changed to SELECT customer_name, total_sales FROM sales ....

● Reduced joins to 2 tables.
● Removed UPPER and used a case-insensitive index.
● Result: Query time dropped to 1.5 seconds, improving user satisfaction.

Using WHERE Clauses and Filters Effectively

The WHERE clause is a powerful tool for filtering data early in the query execution process,
reducing the number of rows the database processes. Effective use of WHERE clauses and filters
can dramatically improve performance.

Definition and Importance

● Definition: The WHERE clause specifies conditions to filter rows before they are
processed by joins, aggregations, or other operations.
● Why It Matters: Filtering early reduces the dataset, lowering CPU, memory, and I/O
usage.

51
 ● Example: In a table with 10 million rows, SELECT * FROM transactions WHERE status
= 'completed'; processes only completed transactions, not all 10 million.

Tips for Effective WHERE Clauses

1. Use Indexes for WHERE Conditions:

○ Ensure columns in WHERE clauses have indexes to speed up filtering.
○ Example: WHERE customer_id = 1000 is fast if customer_id is indexed.
2. Place Restrictive Filters First:
○ Order conditions to filter out the most rows early.
○ Example: In WHERE status = 'active' AND created_date > '2024-01-01', if status
= 'active' eliminates 90% of rows, place it first.
3. Avoid Complex Expressions:
○ Simple conditions like age > 18 are faster than YEAR(birth_date) < 2006.
4. Use Range Filters for Dates and Numbers:
○ Example: WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31' is
efficient with an index on order_date.

Example

Consider a database of 1 million orders:

● Inefficient: SELECT order_id, total FROM orders; (processes all rows).

● Efficient: SELECT order_id, total FROM orders WHERE status = 'active' AND
order_date > '2024-01-01'; (processes only recent active orders).
● If status and order_date are indexed, the query uses an index seek, taking 50ms instead of
5 seconds.

Real-World Example: Ticketing System

A ticketing system queried all tickets, taking 8 seconds: SELECT * FROM tickets ORDER BY
created_date DESC;. By adding filters—SELECT ticket_id, title FROM tickets WHERE status =
'open' AND created_date > '2024-06-01' ORDER BY created_date DESC LIMIT 50;—and

52
indexing status and created_date, query time dropped to 100ms, speeding up the support team’s
workflow.

Advanced Filtering Techniques

● Combine Conditions Logically: Use AND and OR carefully to avoid unintended row
inclusion.
○ Example: WHERE department = 'HR' AND (status = 'active' OR status =
'pending') ensures precise filtering.
● Use IN for Small Lists: For small, fixed lists, WHERE status IN ('active', 'pending') is
efficient.
● Avoid Overlapping Conditions: WHERE price > 100 AND price >= 50 can be
simplified to WHERE price > 100.

PAS Framework

● Problem: Poorly designed WHERE clauses process too many rows.

● Agitate: This slows queries, delays applications, and increases costs.
● Solve: Use indexed, specific, and well-ordered WHERE conditions to filter efficiently.

Using EXISTS vs IN

Both EXISTS and IN are used to check if data exists in a subquery, but they perform differently
depending on the dataset size and query structure. Choosing the right one can significantly
impact performance.

EXISTS: Definition and Use

● Definition: EXISTS checks if a subquery returns at least one row, stopping as soon as a
match is found.
● Why It’s Efficient: It’s optimized for early termination, especially with correlated
subqueries.
● Example:

53
 ○ Query: SELECT name FROM customers c WHERE EXISTS (SELECT 1 FROM
orders o WHERE o.customer_id = c.id);
○ Explanation: Checks if each customer has at least one order, stopping once an
order is found.
● When to Use:
○ Large subquery result sets.
○ Correlated subqueries where the subquery depends on the outer query.
● Real-World Example: A subscription service checks active users: SELECT user_id
FROM users u WHERE EXISTS (SELECT 1 FROM subscriptions s WHERE s.user_id
= u.id AND active = true);. This runs faster than IN for millions of subscriptions,
reducing query time from 3 seconds to 200ms.

IN: Definition and Use

● Definition: IN compares a column against a list of values returned by a subquery.

● Why It’s Slower: The subquery generates a full list before comparison, which can be
slow for large datasets.
● Example:
○ Query: SELECT name FROM customers WHERE id IN (SELECT customer_id
FROM orders);
○ Explanation: Retrieves all customer_id values from orders, then checks if each
customer’s id is in that list.
● When to Use:
○ Small subquery result sets (e.g., fewer than 1,000 rows).
○ Non-correlated subqueries where the subquery runs independently.
● Real-World Example: A small e-commerce site uses WHERE product_id IN (SELECT
id FROM categories WHERE type = 'electronics') for a few hundred products, where IN
is simple and fast.

EXISTS vs IN: Performance Comparison

● Scenario: A database with 1 million customers and 10 million orders.

54
 ○ IN: SELECT name FROM customers WHERE id IN (SELECT customer_id
FROM orders);
■ Generates a list of all order customer_id values, then compares. Takes 5
seconds.
○ EXISTS: SELECT name FROM customers c WHERE EXISTS (SELECT 1
FROM orders o WHERE o.customer_id = c.id);
■ Checks each customer individually, stopping early. Takes 500ms with
proper indexes.
● Key Difference: EXISTS is faster for large subqueries because it doesn’t build a full list.

Best Practices

● Use EXISTS for Large Subqueries: When the subquery might return many rows,
EXISTS is usually faster.
● Use IN for Small Lists: For small, static lists or subqueries, IN is simpler and
performant.
● Index Subquery Columns: Ensure columns in subqueries (e.g., orders.customer_id) are
indexed.
● Consider Joins as Alternatives: Sometimes, rewriting as a JOIN is faster than both
EXISTS and IN.
○ Example: SELECT DISTINCT c.name FROM customers c JOIN orders o ON
c.id = o.customer_id; may outperform IN.

Real-World Scenario: Subscription Service

A subscription platform’s query to find active users was slow: SELECT user_id FROM users
WHERE id IN (SELECT user_id FROM subscriptions WHERE active = true);. With 10 million
subscriptions, it took 6 seconds. Rewriting to SELECT user_id FROM users u WHERE EXISTS
(SELECT 1 FROM subscriptions s WHERE s.user_id = u.id AND active = true); and indexing
subscriptions.user_id reduced query time to 300ms, improving dashboard performance.

Additional Considerations

55
 ● NOT EXISTS vs NOT IN: Similar logic applies. NOT EXISTS is often faster for large
datasets, but NOT IN can return incorrect results if the subquery contains NULL values.
○ Example: WHERE id NOT IN (SELECT customer_id FROM orders WHERE
customer_id IS NOT NULL); avoids NULL issues.
● Test Performance: Always test EXISTS vs IN with your data, as performance depends
on table size, indexes, and database engine.

Putting It All Together: A Practical Example

Let’s combine these techniques in a real-world scenario for a retail application with a database
containing:

● products table (1 million rows): product_id, name, category, price, stock.

● orders table (10 million rows): order_id, product_id, customer_id, order_date.

Original Slow Query

The app displays products ordered in 2024 with low stock:

SELECT * FROM products WHERE product_id IN (SELECT product_id FROM orders

WHERE order_date >= '2024-01-01') AND stock < 10;

● Issues:
○ SELECT * fetches unneeded columns.
○ IN is slow for 10 million orders.
○ No specific index usage.

Optimized Query

SELECT p.name, p.price, p.stock

FROM products p

56
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.product_id = p.product_id AND
o.order_date >= '2024-01-01')

AND p.stock < 10

ORDER BY p.name LIMIT 100;

● Improvements:
○ Selects only name, price, stock.
○ Uses EXISTS for faster subquery execution.
○ Adds LIMIT 100 for pagination.
○ Assumes indexes on orders.product_id, orders.order_date, and products.stock.
● Result: Query time reduced from 10 seconds to 150ms, improving inventory
management.

Steps to Achieve This

1. Analyze the Query: Used EXPLAIN to identify a full table scan on orders.
2. Add Indexes:
○ CREATE INDEX idx_orders_product_date ON orders(product_id, order_date);
○ CREATE INDEX idx_products_stock ON products(stock);.
3. Rewrite Query: Replaced SELECT * and IN with specific columns and EXISTS.
4. Test Performance: Confirmed the new query uses indexes and runs faster.

Key Takeaways

● Select Specific Columns: Avoid SELECT * to reduce data transfer.

● Use WHERE Effectively: Filter early with indexed, specific conditions.
● Limit Results: Use LIMIT or TOP for smaller datasets.
● Choose EXISTS over IN for Large Subqueries: EXISTS stops early, improving
performance.
● Test and Monitor: Use tools like EXPLAIN to verify query efficiency.

57
By applying these principles, you can write SQL queries that are fast, scalable, and resource-
efficient, ensuring your database supports your application’s needs.

6. Database Design and Normalization

Database design is the foundation of a high-performing database. It’s like building a house: a
strong structure ensures everything works smoothly, while a weak one leads to problems. In this
chapter, we’ll explore normalization, denormalization, partitioning, and sharding—key
concepts that shape how data is stored and accessed. We’ll explain each topic in simple English,
provide clear definitions, and use real-world examples to show how they impact performance.

58
By the end, you’ll understand how to design databases that balance efficiency, scalability, and
speed.

6.1 Normal Forms and Their Impact on Performance

What is Normalization?

Normalization is the process of organizing data in a database to eliminate redundancy (duplicate

data) and ensure data integrity (accuracy and consistency). Think of it like tidying up a messy
room: you group similar items together, remove duplicates, and make everything easy to find.
Normalization follows a set of rules called normal forms, each building on the previous one to
create a more organized structure.

● Definition: Normalization involves structuring tables and columns to reduce redundant

data and prevent issues like inconsistent updates.
● Why It Matters: Normalized databases save storage space, ensure data consistency, and
make maintenance easier.
● Real-World Example: A retail store’s database stores customer information. Without
normalization, the same customer’s name and address might appear in multiple tables,
leading to duplicates. If the customer updates their address, you’d need to update every
table, risking errors. Normalization solves this by storing the address once in a customers
table.

First Normal Form (1NF): The Starting Point

The first normal form (1NF) ensures data is stored in a way that’s simple and consistent.

● Definition: A table is in 1NF if:

○ All attributes (columns) are atomic, meaning they can’t be divided further.
○ There are no repeating groups (e.g., multiple values in a single column).
● How to Achieve 1NF:
○ Break down complex data into single values.

59
 ○ Create separate rows for multiple values instead of cramming them into one
column.
● Example:
○ Non-1NF Table: A students table with a phone_numbers column containing
“123-456-7890, 987-654-3210” for one student.

1NF Solution: Create a separate student_phones table with one row per phone number, linked by
student_id.
Non-1NF:

| student_id | name | phone_numbers |

|------------|------------|----------------------------|

|1 | Alice | 123-456-7890, 987-654-3210|

1NF:

| student_id | name |

|------------|-------|

|1 | Alice |

| student_id | phone_number |

|------------|----------------|

|1 | 123-456-7890 |

|1 | 987-654-3210 |

60
 ● Impact on Performance: 1NF reduces data duplication, saving storage. However,
queries may need joins to combine data, which can slow performance if not optimized.
● Real-World Example: A library database stores book authors. Instead of a single
column listing multiple authors (e.g., “John Smith, Jane Doe”), a separate book_authors
table links each author to a book, ensuring 1NF and making searches easier.
● PAS Framework:
○ Problem: Storing multiple values in one column makes searching and updating
difficult.
○ Agitate: Queries become complex, errors creep in (e.g., updating only one phone
number), and storage is wasted.
○ Solve: Split data into atomic values and separate tables to ensure 1NF,
simplifying queries and updates.

Second Normal Form (2NF): Removing Partial Dependencies

The second normal form builds on 1NF to further reduce redundancy.

● Definition: A table is in 2NF if:

○ It is in 1NF.
○ All non-key attributes depend fully on the primary key (no partial dependencies).
● Partial Dependency: When a column depends on only part of a composite primary key
(a key with multiple columns).
● How to Achieve 2NF:
○ Identify columns that depend on only part of the primary key.
○ Move those columns to a new table with the relevant key.
● Example:
○ Non-2NF Table: An orders table with order_id, customer_id, customer_name,
and order_date. Here, customer_name depends only on customer_id, not the full
primary key (order_id, customer_id).

2NF Solution: Move customer_name to a customers table.

Non-2NF:

61
| order_id | customer_id | customer_name | order_date |

|----------|-------------|---------------|-------------|

| 101 |1 | Alice | 2024-01-01 |

| 102 |1 | Alice | 2024-01-02 |

2NF:

| order_id | customer_id | order_date |

|----------|-------------|-------------|

| 101 |1 | 2024-01-01 |

| 102 |1 | 2024-01-02 |

| customer_id | customer_name |

|-------------|---------------|

|1 | Alice |

○
● Impact on Performance: 2NF reduces redundancy (e.g., storing “Alice” once instead of
multiple times), saving storage and simplifying updates. However, joins are needed to
retrieve customer_name, which may impact query speed.
● Real-World Example: An e-commerce platform stores order details. By moving
customer details to a separate customers table, the database avoids duplicating names and
addresses, but reports require joins to combine data.
● PAS Framework:

62
 ○ Problem: Storing customer data in every order duplicates data and complicates
updates.
○ Agitate: Duplicate data wastes space, and updating a customer’s name requires
changing multiple rows, risking errors.
○ Solve: Move customer data to a separate table, ensuring 2NF and simplifying
maintenance.

Third Normal Form (3NF): Eliminating Transitive Dependencies

The third normal form takes normalization further by addressing transitive dependencies.

● Definition: A table is in 3NF if:

○ It is in 2NF.
○ There are no transitive dependencies (non-key columns depending on other non-
key columns).
● Transitive Dependency: When a column depends on another non-key column, which
depends on the primary key.
● How to Achieve 3NF:
○ Identify columns that depend on non-key columns.
○ Move them to a separate table with their own key.
● Example:
○ Non-3NF Table: An orders table with order_id, customer_id, city, and state.
Here, state depends on city, not directly on order_id.

3NF Solution: Move city and state to a cities table.

Non-3NF:

| order_id | customer_id | city | state |

|----------|-------------|-----------|----------|

| 101 |1 | New York | NY |

| 102 |2 | Chicago | IL |

63
3NF:

| order_id | customer_id | city_id |

|----------|-------------|---------|

| 101 |1 |1 |

| 102 |2 |2 |

| city_id | city | state |

|---------|-----------|-------|

|1 | New York | NY |

|2 | Chicago | IL |

○
● Impact on Performance: 3NF further reduces redundancy, ensuring data like state is
stored once. However, additional joins (e.g., to get city and state) can slow queries if not
optimized with indexes.
● Real-World Example: A logistics company normalizes its database to store city and
state data in a separate table, reducing duplicates but requiring joins for delivery reports.
● PAS Framework:
○ Problem: Storing state in the orders table duplicates data and risks
inconsistencies.
○ Agitate: If a city’s state changes (e.g., due to a data correction), every order row
must be updated, which is error-prone.
○ Solve: Move city and state to a separate table, ensuring 3NF and consistency.

Beyond 3NF: Higher Normal Forms

64
While 1NF, 2NF, and 3NF are the most commonly used, higher normal forms like Boyce-Codd
Normal Form (BCNF) and Fourth Normal Form (4NF) exist for advanced scenarios.

● BCNF: Addresses anomalies in 3NF where a non-key attribute determines another

attribute.
○ Example: In a courses table, if teacher determines subject, BCNF splits them into
separate tables.
● 4NF: Removes multi-valued dependencies (e.g., a table storing multiple hobbies and
courses per student).
● Impact: Higher normal forms are rarely used in practice because they increase
complexity and joins, often outweighing benefits for most applications.
● Real-World Example: A university database might use 3NF for simplicity, avoiding
BCNF unless specific anomalies arise.

Performance Trade-offs of Normalization

Normalization improves data integrity but can impact performance:

● Pros:
○ Reduces storage by eliminating duplicates.
○ Simplifies updates, ensuring consistency.
○ Supports flexible querying.
● Cons:
○ Increases the number of tables, requiring more joins.
○ Joins can slow queries, especially for large datasets.
○ Complex queries may need optimization (e.g., indexing).
● Real-World Example: A retail database normalizes customer and order data, saving
storage but requiring joins for sales reports. Indexes on customer_id and order_date help
mitigate join overhead.
● Tips for Balancing:
○ Use indexes to speed up joins.
○ Analyze query patterns to prioritize performance-critical tables.
○ Consider denormalization for read-heavy workloads (covered next).

65
6.2 Denormalization: When and Why to Use It

What is Denormalization?

Denormalization is the opposite of normalization: it intentionally adds redundant data to improve

query performance. Think of it like keeping a summary of your favorite book’s key points
instead of flipping through the whole book every time.

● Definition: Denormalization involves adding redundant data or combining tables to

reduce the need for joins and speed up reads.
● Why It Matters: For applications where read speed is critical (e.g., dashboards, reporting
systems), denormalization can significantly reduce query times.
● Real-World Example: A dashboard for an e-commerce platform stores total sales per
product in a single table, avoiding complex joins across orders and order_items.

When to Use Denormalization

Denormalization is not always the answer—it’s a trade-off. Use it in these scenarios:

● Read-Heavy Applications: When queries mostly read data (e.g., reports, analytics).
○ Example: A news website displaying article views and likes.
● Performance-Critical Systems: When query speed is more important than storage or
update complexity.
○ Example: A stock trading app needing instant price updates.
● Complex Queries: When joins slow down frequently run queries.
○ Example: A social media app showing user posts and profiles in one query.
● Real-World Example: A travel booking site denormalizes flight and price data into a
single table to display search results faster.

How to Denormalize

● Add Redundant Columns: Store frequently accessed data in the same table.
○ Example: Add customer_name to the orders table instead of joining with
customers.

66
 ● Combine Tables: Merge related tables to avoid joins.
○ Example: Store product_name and category_name in the order_items table.
● Precompute Aggregates: Store calculated values like totals or averages.
○ Example: A sales_summary table with monthly totals instead of calculating sums
on the fly.
● Example:

Normalized:
| order_id | customer_id | order_date |

|----------|-------------|-------------|

| 101 |1 | 2024-01-01 |

| customer_id | customer_name | city |

|-------------|---------------|-----------|

|1 | Alice | New York |

○ Query: SELECT o.order_id, c.customer_name FROM orders o JOIN customers c

ON o.customer_id = c.customer_id

Denormalized:
| order_id | customer_id | customer_name | order_date |

|----------|-------------|---------------|-------------|

| 101 |1 | Alice | 2024-01-01 |

○ Query: SELECT order_id, customer_name FROM orders

● Impact: The denormalized query is faster (no join) but requires more storage and
complex updates.

67
Trade-offs of Denormalization

● Pros:
○ Faster read queries due to fewer joins.
○ Simpler queries, reducing database workload.
● Cons:
○ Increased storage due to redundant data.
○ Complex updates (e.g., updating customer_name in multiple tables).
○ Risk of data inconsistencies if updates are not managed carefully.
● Real-World Example: A reporting dashboard denormalizes data to show sales metrics
instantly but uses automated scripts to sync updates and maintain consistency.
● PAS Framework:
○ Problem: Joins in a normalized database slow down critical reports.
○ Agitate: Users wait too long for data, impacting decision-making and user
experience.
○ Solve: Denormalize key tables to eliminate joins, speeding up queries at the cost
of extra storage.

Best Practices for Denormalization

● Start Normalized: Always normalize first to ensure data integrity, then denormalize
selectively.
● Use for Read-Heavy Workloads: Denormalize only tables involved in frequent,
performance-critical queries.
● Maintain Consistency: Use triggers, stored procedures, or application logic to update
redundant data.
● Monitor Storage: Ensure denormalization doesn’t exceed storage limits.
● Example: A blog platform denormalizes post_title and author_name into a comments
table for faster display but uses a trigger to update author_name when it changes.

6.3 Partitioning and Sharding Basics

What is Partitioning?

68
Partitioning splits a large table into smaller, more manageable pieces based on a key (e.g., date,
region). Each partition acts like a mini-table, but the database treats them as one.

● Definition: Partitioning divides a table into subsets based on a column’s values,

improving query performance and maintenance.
● Why It Matters: Large tables slow down queries and maintenance tasks like backups.
Partitioning makes these operations faster.
● Types of Partitioning:
○ Range Partitioning: Divides data based on ranges (e.g., dates).
■ Example: Partition orders by year (2023, 2024, 2025).
○ List Partitioning: Groups data by specific values (e.g., regions).
■ Example: Partition users by country (US, EU, Asia).
○ Hash Partitioning: Distributes data evenly using a hash function.
■ Example: Partition logs by a hash of user_id for balanced distribution.
● Real-World Example: A retail chain partitions its sales table by month, so queries for
recent sales only scan the latest partition, speeding up reports.

How Partitioning Improves Performance

● Faster Queries: Queries only scan relevant partitions, not the entire table.
○ Example: Querying orders for 2024 scans only the 2024 partition.
● Easier Maintenance: Backups, index rebuilds, or deletes affect smaller partitions.
○ Example: Dropping old partitions (e.g., 2020 data) is faster than deleting rows.
● Scalability: Handles large datasets without slowing down.
● Real-World Example: A banking app partitions transaction data by year, allowing fast
queries for recent transactions while archiving old data.

Partitioning Example

● Table: orders with millions of rows.

● Partition Key: order_date.

69
Structure:
| order_id | customer_id | order_date | amount |

|----------|-------------|-------------|--------|

| 101 |1 | 2023-01-01 | 100 |

| 102 |2 | 2024-01-01 | 200 |

Partitions:

- orders_2023: Rows where order_date is in 2023

- orders_2024: Rows where order_date is in 2024

●
● Query: SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND
'2024-12-31'
● Impact: The database scans only the orders_2024 partition, reducing query time.

What is Sharding?

Sharding distributes data across multiple database servers (or shards) to spread the workload.
Each shard holds a subset of the data, and the application routes queries to the right shard.

● Definition: Sharding splits a database into separate servers based on a key (e.g., user ID,
region).
● Why It Matters: Sharding enables horizontal scaling, handling massive datasets and
high Mateus: user traffic by distributing data across servers.
● Real-World Example: A global social media platform shards its users table by region
(US, EU, Asia), so each server handles local users, reducing latency.

How Sharding Works

70
 ● Shard Key: A column (e.g \user_id`, `region`) determines which shard stores the data.
● Routing: The application or database routes queries to the correct shard.
● Example:
○ Shard 1: Users with region = 'US'.
○ Shard 2: Users with region = 'EU'.
○ Query: SELECT * FROM users WHERE region = 'US' goes to Shard 1.
● Benefits:
○ Scales to handle millions of users by distributing load.
○ Reduces latency by keeping data close to users.
● Challenges:
○ Complex to implement (requires routing logic).
○ Cross-shard queries are slow or difficult.
● Real-World Example: A ride-sharing app shards driver data by city, so queries for
nearby drivers are fast and localized.

Partitioning vs. Sharding

● Partitioning: Splits data within a single database server.

○ Example: Splitting orders by year on one server.
● Sharding: Splits data across multiple servers.
○ Example: Splitting users by region across different servers.
● When to Use:
○ Partitioning: For large tables on a single server (e.g., historical data).
○ Sharding: For massive, distributed systems (e.g., global apps).
● Real-World Example: A streaming service partitions its view_history table by year for
fast queries and shards user data by country for scalability.

Trade-offs of Partitioning and Sharding

● Pros:
○ Partitioning: Faster queries, easier maintenance, better for single-server setups.
○ Sharding: Scales to millions of users, reduces latency for global apps.
● Cons:

71
 ○ Partitioning: Limited by single-server capacity, complex to manage partitions.
○ Sharding: Requires complex routing, cross-shard queries are slow.
● PAS Framework:
○ Problem: Large tables or global apps slow down due to size or latency.
○ Agitate: Slow queries frustrate users, and single servers can’t handle growth.
○ Solve: Use partitioning for large tables and sharding for distributed systems to
boost performance.

Best Practices for Partitioning and Sharding

● Choose the Right Key: Pick a partition or shard key that aligns with query patterns (e.g.,
order_date for time-based queries).
● Plan for Growth: Design partitions/shards to handle future data volume.
● Monitor Performance: Regularly check query times and adjust partitions/shards as
needed.
● Automate Management: Use tools to manage partition creation or shard rebalancing.
● Real-World Example: An online game partitions player_scores by month and shards
player_data by region, ensuring fast leaderboards and global scalability.

Practical Tips for Database Design

● Start with Normalization: Always design in 3NF to ensure data integrity, then
denormalize selectively.
● Analyze Query Patterns: Base partitioning and indexing on how data is queried.
● Test Performance: Use tools like EXPLAIN to check query plans before and after
changes.
● Balance Trade-offs: Weigh storage vs. speed when denormalizing or partitioning.
● Document Design: Keep a record of tables, indexes, and shard keys for future
maintenance.
● Real-World Example: A CRM system normalizes data for consistency, denormalizes
key reports for speed, and partitions old leads by year for efficiency.

Case Study: Optimizing an E-Commerce Database

72
 ● Scenario: An online store’s database slows down during holiday sales due to millions of
orders.
● Problem: Queries for recent orders take seconds, frustrating users.
● Solution:
○ Normalized tables to 3NF, splitting customers, orders, and order_items.
○ Denormalized product_name into order_items for faster reports.
○ Partitioned orders by month to speed up recent order queries.
○ Sharded users by region to reduce latency for global customers.
● Result: Query time dropped from 5 seconds to 200ms, improving user experience and
sales.
● Metrics:
○ Before: 5s query time, 80% CPU usage.
○ After: 0.2s query time, 40% CPU usage.

Key Takeaways

● Normalization reduces redundancy and ensures consistency but may slow queries due to
joins.
● Denormalization speeds up reads by adding redundant data, ideal for read-heavy
systems.
● Partitioning splits large tables for faster queries and easier maintenance.
● Sharding distributes data across servers for scalability and low latency.
● Balance performance, storage, and complexity based on your application’s needs.

73
7.Advanced Optimization Techniques

In this chapter, we dive into advanced strategies to supercharge your database’s performance.
These techniques go beyond basic query tuning and indexing, targeting complex scenarios where
standard optimizations fall short. We’ll explore Materialized Views and Query Caching,
Stored Procedures and Prepared Statements, Optimizing Joins and Subqueries, and Use of
Hints and Plan Guides. Each section includes definitions, examples, and real-world
applications to help you apply these methods effectively. By mastering these, you’ll handle high-
traffic systems, reduce query times, and ensure smooth operations even under heavy loads.

7.1 Materialized Views and Query Caching

What Are Materialized Views?

74
A materialized view is a database object that stores the results of a query as a physical table.
Unlike regular views, which are virtual and recompute results each time they’re queried,
materialized views save precomputed data, making them ideal for complex or frequently
accessed queries.

● Definition: A materialized view is a snapshot of query results stored in the database,

updated periodically or on demand.
● Key Idea: Think of it as a pre-baked cake. Instead of baking (computing) the cake every
time someone wants a slice, you serve the ready-made one, saving time.
● How It Works: The database runs the query once, stores the results, and refreshes the
view when data changes (manually or on a schedule).

Example: A retail company creates a materialized view to store monthly sales summaries:
CREATE MATERIALIZED VIEW monthly_sales AS

SELECT store_id, DATE_TRUNC('month', sale_date) AS sale_month, SUM(amount) AS

total_sales

FROM sales

GROUP BY store_id, DATE_TRUNC('month', sale_date);

● Querying this view is faster than recomputing the totals each time.

Benefits of Materialized Views

● Speed: Precomputed results reduce query execution time.

● Resource Efficiency: Less CPU and I/O usage for repeated queries.
● Use Case: Best for read-heavy applications, like reporting or analytics dashboards.

Challenges

● Storage: Materialized views consume disk space.

● Refresh Overhead: Updating the view can be resource-intensive.
● Stale Data: Results may not reflect real-time changes unless refreshed.

75
Real-World Example: Analytics Dashboard

A financial analytics platform generates daily reports on stock trades. Computing totals for
millions of trades takes 10 seconds per query, slowing the dashboard.

● PAS Framework:
○ Problem: Slow reports frustrate users and delay decisions.
○ Agitate: Analysts miss market opportunities, and the company risks losing clients
to faster competitors.
○ Solve: A materialized view stores daily trade summaries, reducing query time to
50ms and improving user satisfaction.
● Implementation: The platform creates a materialized view refreshed nightly, ensuring
fast access to precomputed data.

What is Query Caching?

Query caching stores the results of a query in memory or disk so the database can reuse them
without re-executing the query. It’s like keeping a photocopy of a document instead of reprinting
it.

● Definition: Query caching saves query results for quick retrieval, typically for identical
or similar queries.
● Key Idea: Caching is ideal for queries that don’t change often, like product catalogs or
configuration data.

Example: An e-commerce site caches a query for its product catalog:

SELECT product_id, name, price FROM products WHERE category = 'electronics';

● The results are stored in memory, so subsequent requests load instantly.

Types of Query Caching

● Database-Level Caching: Built into the database (e.g., MySQL’s query cache, though
deprecated in newer versions).
● Application-Level Caching: Managed by the app using tools like Redis or Memcached.

76
 ● Result Set Caching: Specific to certain databases, like Oracle’s RESULT_CACHE hint.

Benefits of Query Caching

● Instant Response: Cached results eliminate query execution time.

● Reduced Load: Fewer queries hit the database, saving resources.
● Use Case: Static or semi-static data, like website menus or user settings.

Challenges

● Cache Invalidation: Ensuring cached data stays fresh when underlying data changes.
● Memory Usage: Caching large datasets consumes RAM.
● Not Real-Time: Cached results may lag behind live data.

Real-World Example: E-Commerce Product Page

An online store’s product page loads slowly because it queries the database for product details
every time. By caching the query results in Redis, page load time drops from 500ms to 10ms,
boosting conversions.

● PAS Framework:
○ Problem: Slow page loads drive customers away.
○ Agitate: Lost sales hurt revenue, and competitors with faster sites win market
share.
○ Solve: Query caching delivers instant page loads, keeping customers engaged.

When to Use Materialized Views vs. Query Caching

● Materialized Views: For complex, aggregated queries (e.g., reports) that don’t need real-
time data.
● Query Caching: For simple, frequently run queries with static data (e.g., product lists).
● Example: Use a materialized view for yearly sales trends and query caching for a
homepage banner.

7.2 Using Stored Procedures and Prepared Statements

77
What Are Stored Procedures?

A stored procedure is a precompiled set of SQL statements stored in the database. It’s like a
reusable recipe: you define it once, and the database executes it whenever called.

● Definition: A stored procedure is a named collection of SQL queries and logic that can
be executed with parameters.
● Key Idea: Stored procedures reduce application code, improve security, and speed up
execution by precompiling the SQL.

Example: A stored procedure to process an order:

CREATE PROCEDURE ProcessOrder (IN customer_id INT, IN product_id INT, IN quantity
INT)

BEGIN

INSERT INTO orders (customer_id, product_id, quantity, order_date)

VALUES (customer_id, product_id, quantity, NOW());

UPDATE products SET stock = stock - quantity WHERE id = product_id;

END;

● Call it with: CALL ProcessOrder(100, 50, 2);.

Benefits of Stored Procedures

● Performance: Precompiled code runs faster than ad-hoc queries.

● Security: Limits direct table access, reducing SQL injection risks.
● Reusability: Centralizes logic, making maintenance easier.
● Use Case: Complex transactions, like order processing or user registration.

Challenges

● Complexity: Writing and debugging stored procedures can be tricky.

78
 ● Portability: Procedures are database-specific (e.g., MySQL vs. PostgreSQL).
● Maintenance: Changes require database updates, not just app code.

Real-World Example: Banking Transactions

A banking app uses a stored procedure to transfer funds between accounts, ensuring both
accounts are updated atomically. This reduces client-side code and ensures security.

● PAS Framework:
○ Problem: Ad-hoc queries for transfers are slow and prone to errors.
○ Agitate: Failed transfers anger customers, and security flaws risk fraud.
○ Solve: A stored procedure handles transfers reliably, cutting execution time by
30%.

What Are Prepared Statements?

A prepared statement is a precompiled SQL query with placeholders for parameters. It’s like a
form with blank fields you fill in each time.

● Definition: A prepared statement is a template query executed multiple times with

different parameter values.
● Key Idea: Precompilation and parameter binding improve performance and security.

Example: A prepared statement to fetch user details:

PREPARE user_query FROM 'SELECT * FROM users WHERE id = ?';

SET @id = 100;

EXECUTE user_query USING @id;

● The query is compiled once and reused for different id values.

Benefits of Prepared Statements

● Performance: Reusing the compiled query saves parsing time.

● Security: Prevents SQL injection by separating data from code.

79
 ● Flexibility: Works with dynamic inputs, like user searches.
● Use Case: Repeated queries, like fetching records by ID or filtering data.

Challenges

● Overhead: Initial compilation takes time, though minor.

● Complexity: Requires application code to handle binding.
● Database Support: Not all databases optimize prepared statements equally.

Real-World Example: Search Functionality

A job portal uses prepared statements to search job listings by location and category. By reusing
the compiled query, search time drops from 200ms to 20ms, and SQL injection risks are
eliminated.

● PAS Framework:
○ Problem: Slow and insecure search queries hurt user experience.
○ Agitate: Users abandon the site, and hackers exploit vulnerabilities.
○ Solve: Prepared statements deliver fast, secure searches, boosting retention.

Stored Procedures vs. Prepared Statements

● Stored Procedures: For complex, multi-step logic stored in the database.

● Prepared Statements: For simple, repetitive queries with varying parameters.
● Example: Use a stored procedure for order processing and a prepared statement for user
lookups.

7.3 Optimizing Joins and Subqueries

Understanding Joins

A join combines data from multiple tables based on a condition. Poorly optimized joins can slow
queries significantly.

● Definition: A join merges rows from two or more tables using a key, like customer_id.

80
 ● Types:
○ Inner Join: Returns matching rows from both tables.
○ Left/Right Join: Includes all rows from one table, with NULLs for non-matches.
○ Full Join: Returns all rows, with NULLs for non-matches.
● Key Idea: Joins are resource-intensive, so optimize them with indexes and filters.

Optimizing Joins

● Use Indexes: Ensure join columns (e.g., customer_id) have indexes.

○ Example: An index on orders.customer_id speeds up SELECT * FROM
customers JOIN orders ON customers.id = orders.customer_id.
● Limit Rows Early: Apply WHERE clauses before joining.
○ Example: WHERE orders.order_date > '2024-01-01' reduces rows before the
join.
● Avoid Unnecessary Joins: Only join tables needed for the result.
● Real-World Example: A logistics app joins shipments and tracking tables on an indexed
shipment_id, reducing query time from 1s to 50ms.

Understanding Subqueries

A subquery is a query nested within another query, often used for filtering or calculations.

● Definition: A subquery runs inside a main query, like SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders)).
● Key Idea: Subqueries can be slow, especially if correlated (referencing the outer query).

Optimizing Subqueries

● Replace with Joins: Joins are often faster than subqueries.

Example: Rewrite:
SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders)

81
as:
SELECT DISTINCT customers.* FROM customers JOIN orders ON customers.id =
orders.customer_id

○
● Use EXISTS: For existence checks, EXISTS is faster than IN.

Example:
SELECT * FROM orders WHERE EXISTS (

SELECT 1 FROM shipments WHERE order_id = orders.id

);

○
● Avoid Correlated Subqueries: They run for each row, slowing performance.
● Real-World Example: A retail app optimizes a subquery for active customers to a join,
cutting query time by 40%.

PAS Framework: Join Optimization

● Problem: A reporting query with multiple joins takes 15 seconds.

● Agitate: Slow reports delay decisions, frustrating managers.
● Solve: Indexing join columns and limiting rows reduces time to 1s, enabling timely
insights.

Real-World Example: Logistics Tracking

A shipping company optimizes a query to track packages, joining orders, shipments, and tracking
tables. By indexing join keys and filtering early, query time drops from 12s to 200ms, improving
efficiency.

7.4 Use of Hints and Plan Guides

What Are Hints?

82
A hint is a database instruction that overrides the query optimizer’s default execution plan. It’s
like giving the database a specific route to follow.

● Definition: A hint is a directive in a SQL query to force a specific action, like using an
index or join method.
● Key Idea: Hints are a last resort when the optimizer chooses a poor plan.

Example: Force an index in MySQL:

SELECT * FROM customers USE INDEX (idx_customer_id) WHERE id = 100;

In Oracle:
SELECT /*+ INDEX(customers idx_customer_id) */ * FROM customers WHERE id = 100;

When to Use Indexes

● When to Use hints when:

○ The optimizer picks a slow plan due to outdated statistics.
○ Query performance varies unpredictably.
○ Testing a specific plan for better results.
● Risks:
○ Hints reduce optimizer flexibility, potentially causing issues as data changes.
○ Incorrect hints can worsen performance.
● Example: A query scans a table despite an index. A hint forces the index, cutting time
from 5s to 50ms.

What Are Plan Guides?

A plan guide is a database object that enforces a specific execution plan for a query without
modifying its SQL code.

● Definition: A plan guide associates a query with a predefined plan, ensuring consistency.
● Key Idea: Plan guides are useful for legacy systems or third-party apps where code
changes aren’t possible.

83
Example: In SQL Server, create a plan guide to force an index for a query:
EXEC sp_create_plan_guide

@name = 'Guide_Customer_Query',

@stmt = 'SELECT * FROM customers WHERE id = @0',

@type = 'SQL',

@hints = 'OPTION (FORCE INDEX (idx_customer_id))';

Benefits of Hints and Plan Guides

● Consistency: Ensures predictable performance.

● Control: Overrides suboptimal optimizer choices.
● Use Case: Stabilizing queries in high-stakes or legacy systems.

Challenges

● Maintenance: Hints and guides need updates as data evolves.

● Expertise: Requires deep knowledge of execution plans.
● Risk: Poorly chosen plans can degrade performance.

Real-World Example: Legacy Application

A legacy inventory system’s queries slow down after a database upgrade due to optimizer
changes. Plan guides force the old, efficient plans, restoring performance without rewriting the
app.

PAS Framework: Hints

● Problem: A critical query picks a slow plan, delaying operations.

● Agitate: Downtime costs money and frustrates users.
● Solve: A hint forces an optimized plan, ensuring fast, reliable execution.

84
This chapter has equipped you with advanced tools to tackle tough performance challenges.
Materialized views and query caching speed up data access, stored procedures and prepared
statements streamline logic, optimized joins and subqueries cut query times, and hints and plan
guides provide precise control. Apply these techniques thoughtfully, monitor their impact, and
keep learning to master database performance.

8.Hardware and Configuration Optimization

Optimizing a database isn’t just about writing better queries or adding indexes. The hardware
your database runs on and how you configure the database software play a massive role in
performance. Think of hardware as the foundation of a house—if it’s weak, no amount of
interior decorating (query optimization) will make it perfect. Similarly, database configuration
settings are like tuning a car engine—small tweaks can unlock significant speed. In this chapter,
we’ll explore how to choose the right hardware, configure database settings for peak
performance, and manage connections efficiently. By the end, you’ll have practical strategies to
ensure your database runs smoothly, even under heavy workloads.

8.1 Choosing the Right Hardware

Databases are resource-hungry systems, relying on CPU, memory, storage, and network
components to process queries and deliver data. Choosing the right hardware is critical to avoid

85
bottlenecks and ensure scalability. Let’s break down the key hardware components and how they
impact database performance.

8.1.1 CPU: Powering Query Processing

The CPU (Central Processing Unit) is the brain of your database server, handling query parsing,
optimization, and execution. Modern databases like PostgreSQL, MySQL, and SQL Server can
process multiple queries in parallel, making the number of CPU cores a critical factor.

● Why It Matters: More cores allow the database to handle multiple queries
simultaneously, improving throughput. For example, a complex analytical query (e.g.,
generating a sales report) might use one core, while other users’ queries run on separate
cores.
● How to Choose:
○ Core Count: Aim for 8-16 cores for small to medium databases, and 32+ for
high-traffic systems.
○ Clock Speed: Higher GHz (e.g., 3.0+ GHz) helps single-threaded tasks like
parsing.
○ Cache: Larger CPU cache (e.g., 12MB+) speeds up frequent operations.
● Example: A 16-core CPU with 3.5 GHz and 20MB cache can handle a busy e-commerce
database processing 1,000 queries per second, ensuring low latency even during peak
sales events.
● Real-World Example: A streaming service like Netflix uses high-core CPUs (e.g., 32-
core Intel Xeon processors) to process millions of user requests for video metadata,
ensuring smooth playback and recommendations.
● PAS Framework:
○ Problem: A single-core CPU struggles with multiple users, causing query delays.
○ Agitate: Slow queries frustrate users, crash apps, and hurt business reputation.
○ Solve: Upgrade to a multi-core CPU to handle parallel workloads efficiently.

8.1.2 Memory: Caching for Speed

86
Memory (RAM) is where the database stores frequently accessed data, like indexes or query
results, to avoid slow disk access. Insufficient RAM forces the database to rely on disk I/O,
which is significantly slower.

● Why It Matters: More RAM allows the database to cache tables, indexes, and query
results, reducing disk reads. For example, a 50GB database performs better with 64GB
RAM than 16GB, as more data stays in memory.
● How to Choose:
○ RAM Size: Allocate 1.5-2x the size of your database for optimal caching. For a
100GB database, aim for 128-200GB RAM.
○ Speed: Faster RAM (e.g., DDR4 or DDR5) improves data access times.
○ Configuration: Ensure the database’s buffer pool (e.g., MySQL’s
innodb_buffer_pool_size) is set to use most of the available RAM.
● Example: A database with 64GB RAM caching a 50GB dataset keeps most tables in
memory, reducing query latency from 500ms to 10ms.
● Real-World Example: A financial analytics platform uses 256GB RAM to cache stock
market data, enabling real-time analysis for traders without disk delays.
● PAS Framework:
○ Problem: Low RAM forces disk access, slowing queries.
○ Agitate: Users experience lag, and servers may crash under load.
○ Solve: Add sufficient RAM and configure buffer pools to keep data in memory.

8.1.3 Storage: SSDs for Fast I/O

Storage handles reading and writing data to disk. Traditional hard disk drives (HDDs) are slow
compared to solid-state drives (SSDs), especially for random I/O operations common in
databases.

● Why It Matters: Databases perform thousands of read/write operations per second.

SSDs, especially NVMe SSDs, offer lower latency and higher throughput than HDDs.
● How to Choose:
○ Type: Use NVMe SSDs for high-performance workloads; SATA SSDs for cost-
sensitive setups.

87
 ○ IOPS: Aim for high IOPS (Input/Output Operations Per Second), e.g., 100,000+
for busy databases.
○ Capacity: Ensure enough space for data, indexes, logs, and growth (e.g., 2x
current data size).
● Example: Replacing HDDs with NVMe SSDs reduces query response time from 200ms
to 5ms for a table scan on a 1M-row table.
● Real-World Example: A gaming platform uses NVMe SSDs to store player data,
handling millions of writes per minute during global tournaments.
● PAS Framework:
○ Problem: Slow HDDs bottleneck I/O, delaying queries.
○ Agitate: Users wait longer, and high-traffic apps crash.
○ Solve: Upgrade to SSDs, especially NVMe, for faster reads and writes.

8.1.4 Network: Reducing Data Transfer Delays

The network connects the database server to applications and users. Slow or congested networks
increase latency, especially for cloud databases.

● Why It Matters: Large result sets or frequent queries strain network bandwidth, slowing
responses. For example, a cloud database in a distant region adds 100ms latency for
users.
● How to Choose:
○ Bandwidth: Use high-speed connections (e.g., 10Gbps) for on-premises servers.
○ Latency: Place cloud databases in regions close to users (e.g., AWS us-east-1 for
US users).
○ Compression: Enable data compression to reduce network load.
● Example: A database with a 10Gbps network transfers 1GB result sets in seconds,
compared to minutes on a 1Gbps network.
● Real-World Example: A global e-commerce platform uses a content delivery network
(CDN) and regional database replicas to minimize network latency for users worldwide.

8.1.5 Practical Tips for Hardware Selection

88
 ● Balance Cost and Performance: High-end hardware is expensive. For small businesses,
start with 8-core CPUs, 32GB RAM, and SATA SSDs, then scale up as needed.
● Cloud vs. On-Premises: Cloud providers like AWS, Azure, or Google Cloud offer
scalable hardware (e.g., EC2 instances with 16 vCPUs and 128GB RAM). On-premises
servers give more control but require maintenance.
● Future-Proofing: Plan for growth. A database serving 1,000 users today may need to
handle 10,000 in a year, requiring more cores and storage.
● Real-World Example: A startup uses AWS RDS with a db.m5.4xlarge instance (16
vCPUs, 64GB RAM, SSDs) to support a growing user base, scaling to db.m5.8xlarge
during peak seasons.

8.2 Database Configuration Parameters

Database configuration parameters are settings that control how the database uses resources like
memory, CPU, and connections. Default settings are often conservative, designed for general
use, not high performance. Tuning these parameters can significantly boost efficiency.

8.2.1 Understanding Configuration Parameters

● Definition: Parameters are settings in the database’s configuration file (e.g.,

postgresql.conf for PostgreSQL, my.cnf for MySQL) that define resource limits, caching
behavior, and query processing rules.
● Why It Matters: Proper tuning aligns the database with your workload. For example, a
read-heavy app needs more memory for caching, while a write-heavy app needs
optimized I/O settings.
● Example: Increasing work_mem in PostgreSQL from 4MB to 16MB speeds up sorting
for large queries.

8.2.2 Key Parameters to Tune

Here are common parameters for popular databases, with examples of how to adjust them:

● PostgreSQL:

89
 ○ work_mem: Memory for query operations like sorts and joins.
■ Default: 4MB (too low for complex queries).
■ Tuning: Set to 16-64MB for analytical queries, but monitor total RAM
usage.
■ Example: SET work_mem = '32MB'; reduces disk-based sorting for a
report query.
○ shared_buffers: Memory for caching data and indexes.
■ Default: 128MB.
■ Tuning: Set to 25-40% of system RAM (e.g., 16GB for a 64GB server).
■ Example: shared_buffers = '16GB' keeps more data in memory.
● MySQL (InnoDB):
○ innodb_buffer_pool_size: Memory for caching tables and indexes.
■ Default: 128MB.
■ Tuning: Set to 50-70% of RAM (e.g., 40GB for a 64GB server).
■ Example: innodb_buffer_pool_size = 40G reduces disk I/O.
○ innodb_log_file_size: Size of transaction logs.
■ Default: 48MB.
■ Tuning: Set to 512MB-2GB for write-heavy workloads.
■ Example: innodb_log_file_size = 1G speeds up transactions.
● SQL Server:
○ Max Degree of Parallelism (MAXDOP): Controls CPU core usage for queries.
■ Default: 0 (all cores).
■ Tuning: Set to 4-8 for balanced performance on a 16-core server.
■ Example: sp_configure 'max degree of parallelism', 8; improves query
throughput.
○ Memory Allocation: Limits SQL Server’s memory usage.
■ Tuning: Set max server memory to 80% of system RAM.
■ Example: sp_configure 'max server memory', 51200; (for 64GB RAM).

8.2.3 Tuning Process

90
 ● Step 1: Analyze Workload: Is your database read-heavy (e.g., reporting), write-heavy
(e.g., logging), or mixed?
● Step 2: Monitor Metrics: Use tools like pg_stat_activity (PostgreSQL) or Performance
Monitor (SQL Server) to check resource usage.
● Step 3: Adjust Parameters: Make small changes (e.g., increase work_mem by 4MB)
and test performance.
● Step 4: Test and Validate: Run benchmarks (e.g., pgbench for PostgreSQL) to measure
improvements.
● Example: A web app increases innodb_buffer_pool_size from 128MB to 8GB, reducing
query latency by 60%.
● Real-World Example: A social media platform tunes shared_buffers to 32GB, cutting
response times for user feeds from 500ms to 100ms.

8.2.4 Common Mistakes

● Over-Tuning: Setting work_mem too high can exhaust RAM, causing crashes.
● Ignoring Workload: Using the same settings for a transactional database (e.g., e-
commerce) and an analytical one (e.g., data warehouse).
● Not Testing: Changing parameters without benchmarking can degrade performance.
● Real-World Example: A retailer set work_mem to 1GB, causing memory exhaustion
during peak hours. Reverting to 32MB and optimizing queries fixed the issue.

8.2.5 PAS Framework

● Problem: Default settings cause slow queries and high resource usage.
● Agitate: Users experience delays, and servers struggle under load.
● Solve: Tune parameters like shared_buffers or innodb_buffer_pool_size to match your
workload, improving speed and stability.

8.3 Connection Pooling and Session Management

91
Databases handle multiple user connections, each consuming resources like memory and CPU.
Poor connection management can lead to bottlenecks or crashes. Connection pooling and session
management optimize how connections are handled.

8.3.1 Connection Pooling: Reusing Connections

● Definition: Connection pooling maintains a pool of reusable database connections,

reducing the overhead of opening and closing connections.
● Why It Matters: Opening a new connection takes time (e.g., 10-50ms) and resources.
Pooling reuses existing connections, improving performance.
● How It Works:
○ A pooler (e.g., PgBouncer for PostgreSQL, MySQL Connector) maintains a set
number of connections (e.g., 50).
○ Applications request connections from the pool, not directly from the database.
○ When done, connections return to the pool, ready for reuse.
● Example: A web app with 1,000 users uses a pool of 50 connections, reducing
connection overhead from 50ms to near-zero.
● Real-World Example: A SaaS platform like Slack uses connection pooling to handle
thousands of users chatting simultaneously, keeping latency low.
● Configuration Tips:
○ Pool Size: Set to 2-3x the number of CPU cores (e.g., 32-48 for a 16-core server).
○ Pooler Tools: Use PgBouncer (PostgreSQL), HikariCP (Java apps), or built-in
poolers in cloud services like AWS RDS Proxy.
○ Timeout: Close idle connections after 5-10 minutes to free resources.
● PAS Framework:
○ Problem: Too many connections overload the database.
○ Agitate: Slow responses or crashes during peak traffic.
○ Solve: Implement connection pooling to reuse connections efficiently.

8.3.2 Session Management: Controlling Idle Sessions

● Definition: Session management limits how long connections remain open and handles
idle sessions to free resources.

92
 ● Why It Matters: Idle sessions consume memory and CPU, reducing capacity for active
users.
● How to Manage:
○ Idle Timeout: Close sessions after a set period (e.g., 5 minutes).
○ Max Connections: Limit total connections (e.g., 100 for small servers).
○ Monitoring: Track active and idle sessions with tools like pg_stat_activity.
● Example: Setting idle_in_transaction_session_timeout to 5 minutes in PostgreSQL
closes idle transactions, freeing memory.
● Real-World Example: A banking app limits sessions to 200 and closes idle ones after 3
minutes, ensuring resources for active users.
● Common Tools:
○ PostgreSQL: idle_in_transaction_session_timeout, max_connections.
○ MySQL: wait_timeout, max_connections.
○ SQL Server: Connection limits via server properties.

8.3.3 Best Practices

● Use Pooling for High-Traffic Apps: Web or mobile apps with thousands of users need
pooling to avoid connection bottlenecks.
● Monitor Connection Usage: Use tools like pg_stat_activity or SQL Server DMVs to
track connection counts.
● Test Under Load: Simulate peak traffic to ensure pool size and timeouts handle demand.
● Real-World Example: An e-commerce site uses PgBouncer with a 100-connection pool,
reducing connection time from 20ms to 1ms during Black Friday sales.

8.3.4 Common Pitfalls

● Too Many Connections: Setting max_connections too high exhausts resources.

● No Pooling: Direct connections for each user request slow performance.
● Long Idle Sessions: Idle connections waste memory, reducing capacity.
● Real-World Example: A startup’s app crashed during a product launch due to 1,000
open connections. Implementing PgBouncer with a 50-connection pool fixed the issue.

93
Real-World Case Study: Optimizing a Retail Database

● Scenario: A retail chain’s inventory database slowed during holiday sales, with queries
taking 5-10 seconds.
● Problem: Insufficient RAM (16GB for a 20GB database), slow HDDs, and high
connection overhead (500 direct connections).
● Agitate: Customers faced delays checking stock, leading to lost sales and complaints.
● Solution:
○ Hardware: Upgraded to 64GB RAM and NVMe SSDs, caching 90% of the
database.
○ Configuration: Set innodb_buffer_pool_size to 40GB and work_mem to 32MB.
○ Connection Pooling: Implemented PgBouncer with a 100-connection pool and 5-
minute idle timeout.
● Result: Query latency dropped to 50ms, throughput increased by 70%, and the system
handled 10,000 users without crashing.
● Metrics:
○ Before: 5s query latency, 200 queries/second, 80% CPU usage.
○ After: 50ms query latency, 1,400 queries/second, 40% CPU usage.

Practical Checklist for Hardware and Configuration Optimization

● Assess workload: Is it read-heavy, write-heavy, or mixed?

● Choose hardware: 8+ core CPU, 1.5x database size RAM, NVMe SSDs.
● Tune parameters: Adjust shared_buffers, innodb_buffer_pool_size, or work_mem based
on workload.
● Implement connection pooling: Use PgBouncer or similar for high-traffic apps.
● Set idle timeouts: Close sessions after 5-10 minutes.
● Monitor and test: Use tools like pg_stat_activity and benchmark under load.
● Plan for growth: Ensure hardware and settings support future scale.

Key Takeaways

94
 ● Hardware Matters: CPUs, RAM, and SSDs directly impact performance. Choose based
on workload and scale.
● Tune Configurations: Adjust parameters like memory and connection limits to match
your database’s needs.
● Manage Connections: Use pooling and session timeouts to reduce overhead and
improve scalability.
● Test and Monitor: Regularly benchmark and monitor to catch issues early.

By optimizing hardware and configurations, you can transform a sluggish database into a high-
performance system, ensuring fast queries and happy users. In the next chapter, we’ll explore
how to monitor and troubleshoot performance issues to keep your database running smoothly.

9. Monitoring and Troubleshooting

Monitoring and troubleshooting are critical for maintaining a high-performing database. Without
proper oversight, performance issues like slow queries, deadlocks, or resource bottlenecks can go
unnoticed, causing downtime, frustrated users, and lost revenue. This chapter explains how to
monitor database performance, analyze slow queries, and resolve issues like deadlocks and
blocking. We’ll use simple language, real-world examples, and practical steps to ensure you can
keep your database running smoothly.

9.1 Tools for Monitoring Database Performance

95
Monitoring tools provide insights into how your database is performing, helping you identify
problems before they escalate. These tools track metrics like query execution time, resource
usage, and system health. Below, we explore key tools, how they work, and how to use them
effectively.

9.1.1 Automatic Workload Repository (AWR) - Oracle

● Definition: AWR is a built-in Oracle tool that collects and stores performance statistics,
generating detailed reports on database activity.
● How It Works: AWR takes snapshots of database metrics (e.g., CPU usage, query
performance) at regular intervals (usually hourly) and stores them for analysis. You can
generate reports comparing snapshots to identify trends or issues.
● Key Features:
○ Tracks query execution times, wait events, and resource usage.
○ Highlights top SQL queries by CPU or I/O usage.
○ Identifies bottlenecks like slow disk I/O or locking issues.
● How to Use:
○ Enable AWR in Oracle (default in Enterprise Edition).
○ Generate a report using the awrrpt.sql script.
○ Analyze sections like “Top 5 Timed Events” or “SQL by Elapsed Time.”
● Example: A database administrator runs an AWR report and finds a query consuming
40% of CPU time. They optimize it by adding an index, reducing CPU usage to 10%.
● Real-World Example: A retailer uses AWR during Black Friday sales to monitor
database performance. The report shows high disk I/O wait times, prompting a switch to
faster SSDs, which improves response times by 30%.
● PAS Framework:
○ Problem: Without monitoring, performance issues go unnoticed until users
complain.
○ Agitate: Slowdowns during peak events like Black Friday can crash systems,
losing sales.
○ Solve: AWR provides actionable insights, helping you fix issues proactively.

96
9.1.2 SQL Profiler - SQL Server

● Definition: SQL Profiler is a SQL Server tool that captures and analyzes database events,
such as query execution, errors, or locking.
● How It Works: It records events in real-time, allowing you to filter by criteria like query
duration or user. You can save traces for later analysis.
● Key Features:
1. Tracks slow queries and their execution plans.
2. Monitors user activity and application performance.
3. Identifies blocking or deadlock events.
● How to Use:
1. Open SQL Server Management Studio (SSMS) and start SQL Profiler.
2. Create a new trace, selecting events like “SQL:BatchCompleted.”
3. Filter for queries with high duration (e.g., >1 second).
4. Analyze results to find slow queries or errors.
● Example: A profiler trace shows a query taking 3 seconds due to a table scan. Adding an
index reduces it to 100ms.
● Real-World Example: A financial app uses SQL Profiler to find a slow report query
during month-end processing. Rewriting the query cuts runtime from 10 seconds to
500ms.
● Tip: Use SQL Profiler sparingly in production, as it can add overhead. Consider lighter
alternatives like Extended Events for ongoing monitoring.

9.1.3 EXPLAIN - MySQL/PostgreSQL

● Definition: EXPLAIN is a command in MySQL and PostgreSQL that shows the

execution plan for a query, detailing how the database will retrieve data.
● How It Works: It outlines steps like table scans, index usage, or joins, along with
estimated costs and row counts.
● Key Features:
1. Identifies full table scans (slow) vs. index seeks (fast).
2. Shows join order and temporary table usage.

97
 3. Estimates rows processed, helping spot inefficiencies.
● How to Use:
1. Run EXPLAIN SELECT * FROM orders WHERE customer_id = 100;.
2. Check for “Seq Scan” (full table scan) or “Index Scan” (uses index).
3. Optimize queries with indexes or rewrites if plans show inefficiencies.
● Example: EXPLAIN reveals a query scanning 1 million rows. Adding an index on
customer_id changes it to an index seek, scanning only 10 rows.
● Real-World Example: A blogging platform uses EXPLAIN to find a slow query
fetching recent posts. The plan shows a table scan, so they add an index on post_date,
reducing query time from 5 seconds to 50ms.
● Note: In PostgreSQL, use EXPLAIN ANALYZE for actual runtime data, but test in a
non-production environment to avoid impacting users.

9.1.4 Other Monitoring Tools

● pgAdmin (PostgreSQL): A GUI tool for monitoring queries, server health, and
performance metrics.
○ Example: A developer uses pgAdmin’s dashboard to spot high CPU usage during
peak hours.
● MySQL Workbench (MySQL): Visualizes performance metrics and query execution
plans.
○ Example: A startup uses Workbench to monitor slow queries in their e-commerce
database.
● New Relic or Datadog: Third-party tools for cloud databases, offering real-time
dashboards and alerts.
○ Real-World Example: A SaaS company uses Datadog to monitor AWS RDS
performance, setting alerts for high latency.

9.1.5 Practical Exercise: Using EXPLAIN

1. Create a sample table: CREATE TABLE products (id INT PRIMARY KEY, name
VARCHAR(100), category_id INT);.
2. Insert 10,000 rows of dummy data.

98
 3. Run EXPLAIN SELECT * FROM products WHERE category_id = 5;.
4. Check if it uses a table scan. If so, add an index: CREATE INDEX idx_category ON
products(category_id);.
5. Rerun EXPLAIN to confirm an index scan.
6. Note the difference in estimated rows or cost.

This exercise helps you understand how indexes impact query plans, a key monitoring skill.

9.2 Analyzing Slow Queries

Slow queries are a common performance issue, often caused by poor query design, missing
indexes, or inefficient execution plans. Analyzing and fixing them is essential for a responsive
database.

9.2.1 Steps to Analyze Slow Queries

1. Identify Slow Queries:

○ Use monitoring tools (e.g., SQL Profiler, slow query logs).
○ Check database logs for queries exceeding a threshold (e.g., 1 second).
○ Example: MySQL’s slow query log shows a query taking 4 seconds.
2. Check Execution Plans:
○ Use EXPLAIN (MySQL/PostgreSQL), SHOW PLAN (SQL Server), or AWR
(Oracle).
○ Look for full table scans, high row counts, or costly operations like sorts.
○ Example: A plan shows a table scan on a 1-million-row table.
3. Optimize Queries:
○ Add indexes on columns used in WHERE, JOIN, or ORDER BY.
○ Rewrite queries to reduce complexity (e.g., avoid subqueries).
○ Example: Adding an index reduces query time from 5 seconds to 50ms.
4. Test and Validate:
○ Rerun the query and check new execution plans.

99
 ○ Monitor performance metrics to confirm improvements.
○ Example: After optimization, CPU usage drops from 80% to 30%.

9.2.2 Common Causes of Slow Queries

● Missing Indexes: Forces table scans.

○ Example: A query on orders without an index on order_date.
● Complex Joins: Joining multiple large tables without indexes.
○ Example: A 5-table join slows a report query.
● Functions on Columns: Prevents index usage.
○ Example: WHERE UPPER(name) = 'JOHN' ignores the name index.
● Large Data Scans: Fetching more data than needed.
○ Example: SELECT * retrieves 50 columns when only 2 are used.

9.2.3 Real-World Example: Analytics Platform

● Problem: A marketing analytics platform had slow report queries, taking 10 seconds.
● Analysis:
○ Slow query log identified a query: SELECT * FROM campaigns WHERE
start_date > '2024-01-01'.
○ EXPLAIN showed a table scan on 2 million rows.
● Solution:
○ Added an index: CREATE INDEX idx_start_date ON campaigns(start_date);.
○ Rewrote query to select only needed columns: SELECT campaign_id, name.
● Result: Query time dropped to 100ms, and user satisfaction improved.
● PAS Framework:
○ Problem: Slow reports frustrate users.
○ Agitate: Clients switch to competitors due to delays.
○ Solve: Indexes and query rewrites restore performance.

9.2.4 Practical Exercise: Fixing a Slow Query

100
 1. Create a table: CREATE TABLE employees (id INT PRIMARY KEY, name
VARCHAR(100), dept_id INT, hire_date DATE);.
2. Insert 100,000 rows of sample data.
3. Run a slow query: SELECT * FROM employees WHERE dept_id = 10 AND hire_date >
'2023-01-01';.
4. Use EXPLAIN to check the plan. Note if it’s a table scan.
5. Add a composite index: CREATE INDEX idx_dept_hire ON employees(dept_id,
hire_date);.
6. Rerun the query and compare execution time and plan.

This exercise teaches you to identify and fix slow queries using indexes.

9.3 Identifying and Resolving Deadlocks and Blocking

Deadlocks and blocking occur when transactions compete for resources, causing delays or
failures. Understanding and resolving them is crucial for a stable database.

9.3.1 Deadlocks

● Definition: A deadlock occurs when two or more transactions lock resources each other
needs, creating a stalemate. The database detects and resolves deadlocks by terminating
one transaction.
● How It Happens:
○ Transaction A locks Table1 and waits for Table2.
○ Transaction B locks Table2 and waits for Table1.
○ Neither can proceed, causing a deadlock.
● Example:
○ Transaction A: Updates orders (locks it), then tries to update customers.
○ Transaction B: Updates customers (locks it), then tries to update orders.
○ Deadlock occurs as each waits for the other’s lock.
● Detection:

101
 ○ Use monitoring tools like SQL Server’s Profiler or Oracle’s AWR.
○ Check logs for deadlock errors (e.g., “Deadlock detected, transaction aborted”).
● Solutions:
○ Shorten Transactions: Commit changes quickly to release locks.
■ Example: Break a long transaction into smaller ones.
○ Consistent Lock Order: Always lock tables in the same order (e.g., orders then
customers).
■ Example: Rewrite code to lock orders first in all transactions.
○ Retry Logic: Automatically retry failed transactions.
■ Example: Application code retries a deadlocked transaction after a delay.
○ Lower Isolation Levels: Use less strict levels (e.g., Read Committed) to reduce
locking.
■ Example: Change from Serializable to Read Committed for less
contention.
● Real-World Example: A payment system had frequent deadlocks during peak hours.
Analysis showed two transactions updating accounts and transactions in different orders.
Standardizing the lock order (always accounts first) reduced deadlocks by 90%.
● PAS Framework:
○ Problem: Deadlocks crash transactions, disrupting user actions.
○ Agitate: Customers abandon payments, harming revenue.
○ Solve: Proper lock ordering and retry logic prevent deadlocks.

9.3.2 Blocking

● Definition: Blocking occurs when one transaction holds a lock, forcing others to wait
until the lock is released.
● How It Happens:
○ Transaction A locks a table row during an update.
○ Transaction B tries to access the same row and waits.
● Example:
○ A report query scans orders for hours, locking rows.
○ An update query on orders waits, causing delays.

102
 ● Detection:
○ Use tools like sp_who2 (SQL Server) or pg_stat_activity (PostgreSQL).
○ Check for sessions in a “waiting” state.
● Solutions:
○ Optimize Long-Running Queries: Add indexes or rewrite queries to reduce lock
time.
■ Example: Index a report query to finish in seconds.
○ Use Read-Only Modes: Run reports with NOLOCK (SQL Server) or lower
isolation levels.
■ Example: SELECT * FROM orders WITH (NOLOCK) avoids locking.
○ Schedule Heavy Queries: Run reports during off-peak hours.
■ Example: Schedule analytics jobs at midnight.
○ Kill Blocking Sessions: Terminate problematic sessions after a timeout.
■ Example: Automatically kill sessions running over 1 hour.
● Real-World Example: An HR system had blocking issues when payroll reports ran
during work hours. Moving reports to off-hours and adding indexes reduced blocking
incidents by 95%.

9.3.3 Practical Exercise: Simulating and Resolving a Deadlock

1. Create two tables: CREATE TABLE accounts (id INT PRIMARY KEY, balance INT);
and CREATE TABLE transactions (id INT PRIMARY KEY, account_id INT, amount
INT);.
2. Open two database sessions (e.g., two SQL clients).

In Session 1, run:
BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Wait 10 seconds

UPDATE transactions SET amount = 100 WHERE id = 1;

103
COMMIT;

3.

In Session 2, run:
BEGIN TRANSACTION;

UPDATE transactions SET amount = 100 WHERE id = 1;

-- Wait 10 seconds

UPDATE accounts SET balance = balance + 100 WHERE id = 1;

COMMIT;

4.
5. Observe the deadlock error in one session.
6. Fix by ensuring both transactions update accounts first, then transactions.
7. Rerun to confirm no deadlock occurs.

This exercise demonstrates how lock order causes and resolves deadlocks.

9.4 Advanced Monitoring Techniques

9.4.1 Setting Up Alerts

● Definition: Alerts notify you when performance metrics exceed thresholds (e.g., high
CPU, slow queries).
● How to Set Up:
○ Use tools like Datadog, New Relic, or database-specific features (e.g., Oracle
Enterprise Manager).
○ Configure alerts for:
■ Query duration > 1 second.
■ CPU usage > 80%.

104
 ■ Deadlock occurrences.
● Example: Set an alert in Datadog to email when query latency exceeds 500ms.
● Real-World Example: A gaming platform uses alerts to detect high latency during
tournaments, allowing quick fixes.

9.4.2 Baselining Performance

● Definition: A baseline is a snapshot of normal database performance, used to detect

deviations.
● How to Create:
○ Collect metrics (e.g., query time, CPU usage) during typical operations.
○ Use AWR or New Relic to store baselines.
○ Compare current performance to the baseline.
● Example: A baseline shows average query time of 50ms. A spike to 500ms triggers
investigation.
● Real-World Example: A bank baselines performance during regular hours, catching a
slowdown during a software update.

9.4.3 Log Analysis

● Definition: Logs record database activity, including errors, slow queries, and deadlocks.
● How to Analyze:
○ Enable slow query logs in MySQL (log_slow_queries).
○ Check SQL Server error logs for deadlocks.
○ Use log analysis tools like ELK Stack for large logs.
● Example: A slow query log identifies a query running 10 seconds nightly.
● Real-World Example: A retailer analyzes logs to find frequent deadlocks, leading to
transaction optimizations.

Troubleshooting Checklist

● Slow Queries:

105
 ○ Check slow query logs.
○ Analyze execution plans with EXPLAIN or Profiler.
○ Add indexes or rewrite queries.
● Deadlocks:
○ Review deadlock logs.
○ Standardize lock order.
○ Implement retry logic.
● Blocking:
○ Identify blocking sessions with sp_who2 or pg_stat_activity.
○ Optimize or schedule long-running queries.
● Resource Bottlenecks:
○ Monitor CPU, memory, and disk I/O.
○ Upgrade hardware or tune configurations.

Case Study: E-Commerce Platform

● Problem: During a holiday sale, the platform’s database slowed, with queries taking 5-10
seconds.
● Analysis:
○ AWR report showed high I/O wait times and slow queries.
○ EXPLAIN revealed table scans on products.
○ Profiler detected blocking from report queries.
● Solution:
○ Added indexes on products(category_id, price).
○ Scheduled reports for off-peak hours.
○ Increased memory for caching.
● Result: Query time dropped to 100ms, blocking reduced by 80%, and sales increased by
20%.

106
Key Takeaways

● Use tools like AWR, SQL Profiler, and EXPLAIN to monitor performance.
● Analyze slow queries by checking plans and adding indexes.
● Resolve deadlocks with consistent lock orders and shorter transactions.
● Prevent blocking by optimizing queries and scheduling heavy tasks.
● Set up alerts and baselines for proactive monitoring.

10. Case Studies and Real-World Examples

This section dives into real-world scenarios where database performance issues caused
significant problems and how they were resolved through optimization techniques. Each case
study follows the PAS framework (Problem, Agitate, Solve) to highlight the issue, its impact,
and the solution. We’ll explore detailed examples, including the two provided (e-commerce and
healthcare), plus additional cases to provide a broad perspective. Before-and-after metrics
quantify the improvements, making the benefits clear. These stories show how the concepts from
earlier chapters—query optimization, indexing, hardware tuning, and more—apply in practice.

107
Case Study 1: E-Commerce Platform – Speeding Up Product Searches

Problem

An e-commerce platform, ShopFast, experienced slow product searches during major sales
events like Black Friday. Searches that should take milliseconds were taking up to 8 seconds.
The database, running on MySQL, struggled to handle thousands of simultaneous queries from
users browsing products.

Agitate

Slow searches frustrated customers, leading to abandoned carts. During a recent sale, ShopFast
estimated a $2 million revenue loss due to users leaving the site. High latency also increased
server costs, as the platform scaled up cloud resources to cope with the load. Customer
complaints flooded social media, damaging the brand’s reputation. Without action, ShopFast
risked losing market share to competitors with faster websites.

Solution

The database team conducted a thorough analysis using MySQL’s EXPLAIN tool and slow
query logs. They identified the following issues:

1. Missing Indexes: The products table, with 10 million rows, had no indexes on
product_name or category_id, forcing full table scans for search queries like SELECT *
FROM products WHERE product_name LIKE '%phone%' AND category_id = 5.
2. Inefficient Queries: Queries used SELECT *, retrieving unnecessary columns, and
lacked proper filtering.
3. No Caching: Frequently searched products weren’t cached, causing repeated database
hits.

Steps Taken:

108
 ● Added Indexes: Created a non-clustered index on product_name (using a FULLTEXT
index for LIKE searches) and a composite index on (category_id, product_name). This
reduced table scans and sped up filtering.
● Optimized Queries: Rewrote queries to select only needed columns (e.g., SELECT
product_id, product_name, price) and added LIMIT 50 to cap results. The team also
replaced LIKE '%phone%' with more efficient search patterns where possible.
● Implemented Caching: Used Redis to cache popular search results (e.g., “smartphones”
in category “Electronics”). Cache hits bypassed the database entirely.
● Tuned Configuration: Increased MySQL’s innodb_buffer_pool_size to 16GB (from
8GB) to cache more data in memory.

Result

● Search Time: Dropped from 8 seconds to 200 milliseconds, a 40x improvement.

● Throughput: The database handled 50% more queries per second (from 2,000 to 3,000).
● Business Impact: Cart abandonment decreased by 20%, boosting sales by 15% during
the next sale. Customer satisfaction improved, and server costs dropped by 10% due to
efficient resource use.
● Execution Plan: Before, the plan showed a “Full Table Scan” with a cost of 1.2 million.
After, it used an “Index Seek” with a cost of 1,200.

Key Takeaway: Indexes, query optimization, and caching are critical for high-traffic e-
commerce platforms, especially during peak events.

Case Study 2: Healthcare System – Faster Patient Record Retrieval

Problem

A hospital’s patient management system, HealthCare, took 10 seconds to retrieve patient records.
The PostgreSQL database stored 5 million patient records, and doctors needed instant access
during emergencies.

109
Agitate

Slow record retrieval delayed treatments, risking patient safety. In critical cases, like heart
attacks, every second counts. Nurses spent time waiting for data instead of assisting patients,
reducing care quality. The hospital faced complaints from staff and patients, and regulatory
audits flagged the system’s performance as a concern. Without improvement, the hospital risked
legal and reputational damage.

Solution

The IT team analyzed performance using PostgreSQL’s EXPLAIN ANALYZE and

pg_stat_activity. They found:

1. Large Table Scans: The patients table was unpartitioned, causing slow queries like
SELECT * FROM patients WHERE patient_id = 12345.
2. Missing Indexes: No composite indexes for common filters like (admission_date,
department_id).
3. Memory Issues: Insufficient memory allocation led to disk I/O bottlenecks.

Steps Taken:

● Table Partitioning: Partitioned the patients table by admission_year (e.g., 2020, 2021).
This reduced the rows scanned for recent records, as most queries targeted current
patients.
● Added Composite Indexes: Created an index on (admission_date, department_id) to
speed up queries like SELECT * FROM patients WHERE admission_date > '2024-01-01'
AND department_id = 3.
● Tuned Memory: Increased work_mem to 16MB (from 4MB) for sorting and
shared_buffers to 8GB (25% of server RAM) to cache more data.
● Query Optimization: Replaced SELECT * with specific columns (e.g., SELECT
patient_id, name, medical_history) and used EXISTS instead of IN for subqueries.

Result

110
 ● Retrieval Time: Reduced from 10 seconds to 50 milliseconds, a 200x improvement.
● CPU Usage: Dropped from 90% to 40%, freeing resources for other tasks.
● Business Impact: Doctors accessed records instantly, improving patient care. Staff
productivity increased, and the hospital passed its next audit with praise.
● Execution Plan: Before, a “Sequential Scan” processed 5 million rows. After, a
“Partitioned Index Scan” processed ~10,000 rows.

Key Takeaway: Partitioning and indexing are vital for large datasets in critical systems like
healthcare, where speed saves lives.

Case Study 3: Social Media Platform – Handling Viral Content

Problem

ConnectSphere, a social media platform, faced slow feed loading when posts went viral. The
SQL Server database took 5 seconds to load user feeds with millions of posts, especially during
trending events.

Agitate

Slow feeds frustrated users, who expected instant updates. Engagement dropped as users left for
competitors with faster apps. Advertisers complained about reduced ad views, threatening
revenue. The platform’s servers also hit 95% CPU usage, risking crashes during peak traffic.
Without a fix, ConnectSphere could lose its growing user base.

Solution

The database team used SQL Server Profiler and Dynamic Management Views (DMVs) to
pinpoint issues:

1. Complex Joins: Feed queries joined posts, users, and likes tables, causing high CPU
usage.
2. No Materialized Views: Frequently accessed feeds weren’t precomputed.

111
 3. Locking Issues: High write traffic from likes/comments caused blocking.

Steps Taken:

● Optimized Joins: Rewrote queries to join on indexed columns (e.g., post_id) and filtered
rows early with WHERE user_id IN (followed_users). Added a non-clustered index on
posts(user_id, created_at).
● Materialized Views: Created a materialized view for each user’s feed, refreshed every 5
minutes, to precompute popular posts.
● Reduced Locking: Used NOLOCK hints for read-heavy queries and shortened
transactions for writes.
● Connection Pooling: Implemented a connection pool with 200 connections to handle
10,000 concurrent users.

Result

● Feed Load Time: Dropped from 5 seconds to 150 milliseconds, a 33x improvement.
● CPU Usage: Reduced from 95% to 50%.
● Business Impact: User engagement increased by 25%, ad views rose by 30%, and server
stability improved during viral events.
● Execution Plan: Before, a “Nested Loop Join” processed 1 million rows. After, an
“Index Seek” processed 5,000 rows.

Key Takeaway: Materialized views and optimized joins are game-changers for dynamic, read-
heavy applications like social media.

Case Study 4: Logistics Company – Streamlining Shipment Tracking

Problem

112
GlobalFreight, a logistics company, had a MySQL database that took 12 seconds to track
shipments. With 20 million shipment records, queries like SELECT * FROM shipments
WHERE tracking_number = 'ABC123' were too slow.

Agitate

Slow tracking frustrated customers, who expected real-time updates. Call center complaints
spiked, increasing operational costs. Partners like retailers lost trust, as they couldn’t provide
accurate delivery estimates. The database also consumed 80% disk I/O, slowing other operations.
Without improvement, GlobalFreight risked losing major contracts.

Solution

The team used MySQL’s Performance Schema and slow query logs to diagnose:

1. Unindexed Columns: No index on tracking_number, causing full table scans.

2. Poor Configuration: Low innodb_io_capacity limited disk performance.
3. Redundant Data: Normalized tables required complex joins.

Steps Taken:

● Added Index: Created a unique index on tracking_number, enabling instant lookups.

● Tuned Configuration: Increased innodb_io_capacity to 1,000 (from 200) and upgraded
to NVMe SSDs for faster I/O.
● Denormalization: Added customer_name and status to the shipments table to avoid joins
with customers and statuses.
● Query Rewrite: Used SELECT tracking_number, status, delivery_date instead of
SELECT *.

Result

● Tracking Time: Reduced from 12 seconds to 30 milliseconds, a 400x improvement.

● Disk I/O: Dropped from 80% to 20%.
● Business Impact: Customer satisfaction rose, call center complaints fell by 40%, and
partnerships strengthened.

113
 ● Execution Plan: Before, a “Full Table Scan” cost 2 million. After, an “Index Seek” cost
100.

Key Takeaway: Indexing and denormalization can transform performance in tracking systems
with unique identifiers.

Case Study 5: Financial Services – Reducing Report Generation Time

Problem

WealthBank’s Oracle database took 15 minutes to generate daily transaction reports for 1 million
accounts. The query aggregated data across transactions, accounts, and categories.

Agitate

Slow reports delayed decision-making, frustrating managers who needed timely insights.
Compliance teams struggled to meet regulatory deadlines, risking fines. The database hit 90%
memory usage, slowing other operations. Without a fix, WealthBank faced operational and legal
challenges.

Solution

The team used Oracle’s Automatic Workload Repository (AWR) and EXPLAIN PLAN:

1. Slow Aggregations: GROUP BY operations on transaction_date were unindexed.

2. No Caching: Reports weren’t cached, causing repeated computations.
3. Suboptimal Hardware: Insufficient memory for large sorts.

Steps Taken:

● Added Indexes: Created a composite index on (transaction_date, account_id) to speed up

GROUP BY.
● Query Caching: Used Oracle’s result cache for static reports.

114
 ● Hardware Upgrade: Increased RAM to 128GB (from 64GB) and set
pga_aggregate_target to 16GB.
● Rewrote Query: Used PARTITION BY in analytical functions to optimize aggregations.

Result

● Report Time: Dropped from 15 minutes to 20 seconds, a 45x improvement.

● Memory Usage: Reduced from 90% to 30%.
● Business Impact: Managers accessed reports instantly, compliance deadlines were met,
and operational efficiency improved.
● Execution Plan: Before, a “Hash Group By” processed 10 million rows. After, an “Index
Range Scan” processed 100,000 rows.

Key Takeaway: Indexing, caching, and hardware tuning are critical for analytical workloads.

Before and After Metrics Summary

Case Study Metric Before After Improvemen

t

E-Commerce (ShopFast) Query 8 seconds 200 ms 40x

Latency

Throughput 2,000 3,000 50%

queries/sec queries/sec

Healthcare (HealthCare) Retrieval 10 seconds 50 ms 200x

Time

115
 CPU Usage 90% 40% 56%
reduction

Social Media Feed Load 5 seconds 150 ms 33x

(ConnectSphere) Time

CPU Usage 95% 50% 47%

reduction

Logistics (GlobalFreight) Tracking 12 seconds 30 ms 400x

Time

Disk I/O 80% 20% 75%

reduction

Financial (WealthBank) Report Time 15 minutes 20 seconds 45x

Memory 90% 30% 67%

Usage reduction

Lessons Learned

These case studies highlight universal principles:

● Proactive Monitoring: Use tools like EXPLAIN, AWR, or Profiler to catch issues early.

116
 ● Targeted Indexing: Add indexes for frequently queried columns but avoid over-
indexing.
● Query Optimization: Rewrite queries to reduce complexity and leverage database
features.
● Hardware and Configuration: Tune memory, I/O, and connections to match workloads.
● Business Impact: Performance improvements enhance user experience, reduce costs, and
drive growth.

By applying these techniques, any organization can transform its database performance, just as
these companies did.

11. Summary and Best Practices

This final chapter consolidates the core lessons from our journey through database performance
optimization. Whether you're a database administrator, developer, or business owner, the
strategies outlined in this book empower you to make your databases faster, more reliable, and
cost-efficient. We'll recap the key takeaways, provide a detailed checklist for ongoing
optimization, and share resources for continued learning. By applying these principles
consistently, you can ensure your database systems meet the demands of modern applications,
from e-commerce platforms to healthcare systems.

117
Recap Key Takeaways

Database performance optimization is about making systems run efficiently, delivering data
quickly, and using resources wisely. Below, we revisit the four critical takeaways introduced
earlier, with deeper insights and real-world applications to solidify your understanding.

1. Optimize Queries with Indexes, Efficient SQL, and Execution Plans

Efficient queries are the backbone of a high-performing database. By leveraging indexes, writing
streamlined SQL, and understanding execution plans, you can significantly reduce query
execution time and resource consumption.

● Indexes: Indexes act like a book’s table of contents, allowing the database to find data
quickly without scanning entire tables. For example, an index on a customer_id column
can reduce a query’s runtime from seconds to milliseconds.
○ Real-World Example: An online bookstore’s search feature was slow, taking 5
seconds to find books by author. Adding an index on the author_name column cut
the time to 100ms, delighting users.
○ PAS Framework:
■ Problem: Slow queries frustrate users and clog systems.
■ Agitate: Customers abandon apps, and servers struggle under load,
increasing costs.
■ Solve: Create indexes on frequently queried columns and analyze
execution plans to ensure they’re used.
● Efficient SQL: Writing precise SQL avoids unnecessary work. For instance, selecting
only needed columns (e.g., SELECT name, email instead of SELECT *) reduces data
transfer and processing time.
○ Example: A payroll system querying SELECT * FROM employees retrieved 50
columns when only 3 were needed, slowing reports. Specifying columns halved
the query time.
● Execution Plans: These show how the database executes a query. Reading them helps
identify inefficiencies, like full table scans, and guides optimization.

118
 ○ Example: A developer used EXPLAIN in PostgreSQL to find a query scanning a
million-row table. Adding an index changed it to an index seek, speeding it up
50x.

2. Monitor Performance Regularly to Catch Issues Early

Proactive monitoring prevents small issues from becoming big problems. By tracking metrics
like query latency, throughput, and resource usage, you can identify bottlenecks before they
impact users.

● Why It Matters: Regular monitoring is like checking a car’s dashboard for warning
lights. Ignoring it risks breakdowns.
● Real-World Example: A streaming service noticed slow response times during peak
hours. Monitoring revealed high CPU usage, prompting an upgrade to a more powerful
server, restoring performance.
● Tools:
○ AWR (Oracle): Generates detailed performance reports.
○ SQL Profiler (SQL Server): Tracks slow queries and resource usage.
○ EXPLAIN (MySQL/PostgreSQL): Analyzes query plans.
● PAS Framework:
○ Problem: Unmonitored databases hide issues like slow queries or resource
exhaustion.
○ Agitate: These issues cause outages, user complaints, and revenue loss.
○ Solve: Use monitoring tools to track performance metrics and set alerts for
anomalies.

3. Balance Normalization and Denormalization for Performance

Normalization reduces data redundancy, while denormalization boosts read performance by

reducing joins. Striking the right balance depends on your application’s needs.

119
 ● Normalization: Organizes data into separate tables to ensure consistency. For example,
storing customer_name in a customers table rather than duplicating it in orders saves
space and simplifies updates.
○ Example: A retail database normalized customer data, reducing storage by 30%
but requiring joins for reports.
● Denormalization: Adds redundant data for faster reads. For instance, storing
customer_name in the orders table avoids a join when displaying order details.
○ Real-World Example: A dashboard showing sales metrics denormalized data to
display results instantly, avoiding complex joins.
● When to Use:
○ Normalize for write-heavy systems (e.g., transactional apps).
○ Denormalize for read-heavy systems (e.g., reporting tools).
● PAS Framework:
○ Problem: Over-normalized databases slow down queries with excessive joins.
○ Agitate: Slow reports frustrate users and delay decisions.
○ Solve: Denormalize selectively for read-heavy queries while maintaining
normalization for data integrity.

4. Tune Hardware and Configurations for Your Workload

Hardware and database settings must match your workload. The right CPU, memory, storage,
and configurations ensure optimal performance.

● Hardware: More CPU cores handle parallel queries, ample RAM caches data, and SSDs
speed up disk I/O.
○ Example: A database with 4GB RAM struggled with a 10GB dataset, causing
frequent disk swaps. Upgrading to 16GB RAM eliminated the issue.
● Configurations: Settings like memory allocation or connection limits impact
performance.
○ Example: Increasing innodb_buffer_pool_size in MySQL improved caching,
reducing query times by 40%.

120
 ● Real-World Example: A gaming app tuned connection pooling to handle 10,000
concurrent users, preventing crashes during peak times.
● PAS Framework:
○ Problem: Poor hardware or default settings cause bottlenecks.
○ Agitate: Slow systems lead to user churn and higher costs.
○ Solve: Choose SSDs, allocate sufficient RAM, and tune parameters like
work_mem or max_connections.

Checklist for Ongoing Optimization

Maintaining database performance is an ongoing process. This checklist provides actionable

steps to keep your database running smoothly. Each item is explained with practical tips and
examples to ensure clarity.

1. Review Slow Query Logs Weekly

Slow query logs identify queries that take too long, helping you pinpoint optimization
opportunities.

● How to Do It:
○ Enable slow query logging in your database (e.g., slow_query_log in MySQL).
○ Set a threshold (e.g., queries taking >1 second).
○ Analyze logs using tools like pt-query-digest (MySQL) or pgBadger
(PostgreSQL).
● Example: A retail app found a query taking 3 seconds in the slow query log. Adding an
index on order_date reduced it to 50ms.
● Why It Matters: Slow queries compound during peak traffic, causing delays.
● Real-World Example: A travel booking platform reviewed logs weekly, identifying a
slow search query. Optimizing it with a composite index improved user satisfaction by
20%.

2. Update Statistics and Rebuild Indexes Monthly

121
Statistics help the optimizer choose efficient plans, while index maintenance reduces
fragmentation.

● Update Statistics:
○ Run ANALYZE (PostgreSQL) or UPDATE STATISTICS (SQL Server) to
refresh data distribution info.
○ Example: A database with outdated statistics chose a full table scan over an
index. Updating statistics fixed the plan.
● Rebuild Indexes:
○ Use REINDEX (PostgreSQL) or ALTER INDEX REBUILD (SQL Server) to
deffragment indexes.
○ Example: A fragmented index slowed queries by 30%. Rebuilding it restored
performance.
● Real-World Example: A bank rebuilt indexes monthly, ensuring fast transaction
processing even after heavy updates.

3. Test Execution Plans for Critical Queries

Regularly check execution plans for key queries to ensure they use indexes and avoid
inefficiencies.

● How to Do It:
○ Use EXPLAIN (MySQL/PostgreSQL) or SHOW PLAN (SQL Server) to view
plans.
○ Look for red flags like full table scans or high-cost operations.
● Example: A query joining three tables showed a nested loop join costing 80% of
runtime. Adding an index changed it to a hash join, cutting time by 60%.
● Real-World Example: A logistics app tested plans for shipment tracking queries, fixing
a slow plan by adding a composite index.

4. Monitor CPU, Memory, and Disk Usage

Track resource usage to identify bottlenecks before they cause outages.

122
 ● Tools:
○ Database-specific: Oracle Enterprise Manager, SQL Server Performance Monitor.
○ General: Grafana, Prometheus, or cloud tools like AWS CloudWatch.
● What to Monitor:
○ CPU: High usage (>80%) indicates query or workload issues.
○ Memory: Low free memory forces disk I/O, slowing queries.
○ Disk I/O: High read/write latency suggests slow storage.
● Example: A database with 90% CPU usage during reports was fixed by optimizing a
join-heavy query.
● Real-World Example: A SaaS app monitored disk I/O, upgrading to NVMe SSDs when
latency spiked, improving performance by 50%.

5. Use Connection Pooling for High-Traffic Apps

Connection pooling reuses database connections, reducing overhead and improving scalability.

● How It Works: A pool maintains open connections, assigning them to users as needed.
● Setup:
○ Use tools like PgBouncer (PostgreSQL) or HikariCP (Java apps).
○ Configure pool size based on traffic (e.g., 50 connections for 1,000 users).
● Example: A web app without pooling crashed with 500 users. Adding a pool of 100
connections supported 5,000 users.
● Real-World Example: A social media platform implemented connection pooling,
reducing connection overhead by 70% during peak traffic.

Additional Checklist Items

To make optimization a habit, consider these supplementary tasks:

● 6. Audit Indexes Quarterly: Drop unused indexes to save space and speed up writes.
Use database metadata (e.g., pg_stat_user_indexes in PostgreSQL) to find unused ones.
○ Example: Dropping an unused index on a deprecated column saved 10GB of
storage.

123
 ● 7. Test Backup Performance: Ensure backups don’t slow the database. Schedule them
during low-traffic periods.
○ Example: A nightly backup slowed queries. Moving it to 3 AM resolved the
issue.
● 8. Review Configuration Changes: After upgrades, check settings like max_connections
or work_mem for compatibility.
○ Example: A database upgrade reset innodb_buffer_pool_size, slowing queries
until adjusted.
● 9. Simulate Peak Loads: Use tools like JMeter to test performance under stress.
○ Example: A retailer simulated Black Friday traffic, identifying a connection limit
issue before the event.
● 10. Train Your Team: Ensure developers and DBAs understand optimization basics.
○ Real-World Example: A startup trained its team on indexing, reducing query
times by 40% across the app.

Resources for Further Learning

To deepen your knowledge and stay updated, explore these resources. They offer practical
guidance, advanced techniques, and community support for database performance optimization.

Books

● “SQL Performance Explained” by Markus Winand: A concise guide to query

optimization, indexing, and execution plans. Ideal for beginners and intermediates.
● “High Performance MySQL” by Baron Schwartz et al.: Covers MySQL-specific
optimization, including configuration and hardware tuning.
● “Database System Concepts” by Abraham Silberschatz et al.: A comprehensive
resource for understanding database internals, useful for advanced readers.

Online Resources

● Official Documentation:

124
 ○ PostgreSQL: https://www.postgresql.org/docs/ — Detailed guides on indexing,
statistics, and monitoring.
○ MySQL: https://dev.mysql.com/doc/ — Covers query optimization and
configuration.
○ SQL Server: https://docs.microsoft.com/sql — Includes tutorials on SQL Profiler
and performance tuning.
● Blogs and Communities:
○ Percona Blog: https://www.percona.com/blog/ — Practical tips for MySQL and
PostgreSQL.
Robots.txt

125