Performance Optimization

Introduction

A database that returns correct results but takes ten seconds to do it is not really working — users will not wait, and a slow database can bring an entire application to a crawl under real traffic. Performance is not something you chase only when something breaks; it is shaped by decisions you make from the very start of designing a database.

This lesson steps back from any single feature and looks at the general principles behind a fast MySQL database. Many of these ideas — indexing, normalization, avoiding SELECT * — were introduced in earlier lessons; here you will see them specifically through a performance lens, tying them together into one coherent picture.

Indexing (Recap)

An index lets MySQL find matching rows without scanning the entire table, much like a book's index lets you jump straight to a page instead of reading cover to cover. Columns that regularly appear in WHERE clauses, JOIN conditions, or ORDER BY clauses are prime candidates for an index.

-- Speeds up lookups and joins on customer_id
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Choosing Appropriately Sized Data Types

Every column's data type determines how much storage it uses and how quickly MySQL can compare, sort, and index it. Smaller, more precise types generally mean faster queries and less disk I/O, because more rows fit into the same memory page.

A column defined as VARCHAR(255) when the data is always exactly 2 characters wastes space and gives MySQL less useful information to optimize with. Matching the type and size to the actual data keeps rows smaller, tables faster to scan, and indexes more compact.

Avoiding Unnecessary Columns in SELECT

SELECT * asks MySQL for every column in a table, even when your application only needs two or three of them. This wastes network bandwidth transferring data nobody uses, and it can prevent MySQL from using an efficient "covering index" (an index that already contains every column the query needs, avoiding a trip back to the full table).

SELECT * FROM orders WHERE customer_id = 42;

SELECT order_id, order_date, total
FROM orders
WHERE customer_id = 42;

Normalization vs Denormalization

Normalization (covered earlier in this course) organizes data to avoid duplication and keep it consistent, but it often means a query has to JOIN several tables together to reassemble a full picture. For a heavily read, reporting-style workload, those joins can add up.

Denormalization deliberately reintroduces some duplication — for example, storing a customer's name directly on an orders table instead of always joining to the customers table — to make the most common reads faster, at the cost of extra care needed to keep the duplicated data in sync.

Caching at the Application Level

Not every request needs to hit the database at all. Data that is expensive to compute but does not change every second — a homepage's "top products" list, for example — can be computed once and stored temporarily in an application-level cache (such as Redis or Memcached), then served instantly to every subsequent request until the cache expires or is invalidated.

Caching does not replace a well-designed database, but it can dramatically reduce how often expensive queries need to run in the first place, which helps every other query on the server too.

Measure Before You Optimize

It is tempting to guess which query is "probably slow" and start tweaking it. Resist that urge. Databases are often surprising — a query that looks complicated can be fast, while a query that looks simple can be scanning millions of rows behind the scenes.

MySQL gives you tools to measure exactly what is happening instead of guessing, most importantly the EXPLAIN statement, which you will learn in an upcoming lesson. EXPLAIN shows you precisely how MySQL plans to run a query — which indexes it will use and how many rows it expects to examine — so you can optimize based on evidence, not intuition.

Common Mistakes

Key Takeaways

Summary

Fast databases are usually not the result of one clever trick, but of consistently good habits: sensible indexes, right-sized data types, selective queries, a deliberate normalization strategy, and caching where it helps most.

In this lesson, you learned the general principles behind MySQL performance. Next, you will apply these ideas directly to writing more efficient queries, before learning to measure their actual performance with EXPLAIN.