The Ultimate Guide to MySQL Query Optimization

At its core, query optimization comes down to one goal: Make the database read as few rows as possible.

If MySQL reads 300,000 rows to return 5 results, it is slow. If it reads 5 rows to return 5 results, it is lightning fast.

In The Developer’s Guide to Identifying Slow MySQL Queries, we covered the mechanics of finding your slowest queries using server logs and gave a brief introduction to the EXPLAIN command.

But once you’ve identified a slow query, how do you actually fix it?

In this guide, we’re going beyond the basics. We will do a deep dive into the internal logic of the MySQL optimizer, master the ESR rule for perfect indexing, and learn how to use EXPLAIN ANALYZE to pinpoint bottlenecks with millisecond precision.

Part 1: Diagnosing Queries (EXPLAIN vs. EXPLAIN ANALYZE)

Before changing anything, you must measure. MySQL gives you two tools to inspect what the query engine is doing under the hood.

1. EXPLAIN (The Estimate)

Prepend EXPLAIN to any SELECT query. MySQL will return a table showing its estimated execution plan without actually running the query.

EXPLAIN SELECT * FROM table1 WHERE id = 1234 AND status = 1;

Key Columns to Look At in EXPLAIN:

Column	What it means	Good Values	Bad Values (Red Flags)
type	How MySQL finds the rows	const, eq_ref, ref, range	ALL (Full table scan – reads everything!)
possible_keys	Indexes MySQL could use	Names of indexes	NULL (No matching indexes exist)
key	The index MySQL actually picked	An index name	NULL (It isn’t using an index)
rows	Estimated rows scanned	Small numbers (< 100)	Large numbers (10,000+)
Extra	Additional execution details	Using index	Using temporary, Using filesort (Slow!)

2. EXPLAIN ANALYZE (The Real Execution)

Introduced in MySQL 8.0 (2018), EXPLAIN ANALYZE actually runs the query and outputs a tree-structured performance report with real timings in milliseconds.

EXPLAIN ANALYZE SELECT * FROM table1 WHERE id = 1234 AND status = 1;

How to read EXPLAIN ANALYZE Output:

Reads bottom-up and inside-out. The deepest nested step runs first.

-> Filter: (table1.id = 1234)  (cost=8220 rows=2606) (actual time=991..991 rows=0 loops=1)
    -> Index lookup on table1 using index_status (status=1)  (cost=8220 rows=156350) (actual time=0.0134..955 rows=323944 loops=1)

Breaking down the metrics:

• actual time=0.0134..955: The first row took 0.0134ms to fetch; fetching all matching rows took 955ms. (This is your bottleneck!)
• rows=323944: MySQL processed 323,944 rows at this step.
• loops=1: The step ran 1 time. (In joins, this might loop multiple times).

Part 2: How to Choose & Build the Right Indexes

An index is like an alphabetical index at the back of a book. Without it, you have to read the book page-by-page (a Full Table Scan).

Single-Column vs. Composite (Multi-Column) Indexes

A common mistake is creating a single-column index for every column. If you query multiple columns at once, MySQL usually only picks one single-column index and ignores the rest.

To optimize queries with multiple WHERE conditions, use Composite Indexes (indexes spanning multiple columns).

The “ESR” Rule for Index Column Order

When creating a composite index (ColA, ColB, ColC), the order of columns matters immensely. Always follow the ESR Rule:

1. E – Equality (=): Put columns with exact matches first (e.g., status = 1, user_id = 50).
   • Pro-tip: Put the most selective (highest variety of values) equality column first. user_id is better first than status because status only has 2-3 values, whereas user_id has thousands.
2. S – Sort (ORDER BY): Put columns used for sorting next.
3. R – Range (>, <, BETWEEN, LIKE ‘abc%’): Put range conditions last.

⚠️ Crucial Rule: Once MySQL hits a range condition in an index, it cannot use any index columns that come after it!

Example:

SELECT * FROM orders 
WHERE store_id = 5          -- Equality (=)
  AND status = 'completed'  -- Equality (=)
  AND total_amount > 100    -- Range (>)
ORDER BY created_at DESC;   -- Sort

Optimal Index: (store_id, status, created_at, total_amount)

Part 3: Text Searching – LIKE vs. FULLTEXT

How you search for text determines whether your site flies or crawls.

The Problem with LIKE ‘%keyword%’

• LIKE ‘keyword%’ (Wildcard at the end): Can use a standard B-Tree index. Fast.
• LIKE ‘%keyword%’ (Wildcard at the beginning): CANNOT use a standard index. It forces MySQL to read every single string in every row. Slow.

If you are searching unstructured text (e.g., blog post bodies, product descriptions, forum posts), stop using LIKE and use a FULLTEXT index.

In the UltimateWB website builder, you have the option to offer search on your website via FULLTEXT from the Configuration pages in your admin panel. And if you’re using the built-in WordPress blog option in UltimateWB, you automatically get the benefits of the upgraded search function. WordPress still uses ‘%LIKE%’ for its search function, out-of-the-box. One of our customers learned that the hard way.

FULLTEXT Search: MATCH() AGAINST()

To use full-text searching, you must first add a FULLTEXT index to your table:

ALTER TABLE table2 ADD FULLTEXT INDEX ft_post_content (post_title, post_body);

Then rewrite your query using MATCH() AGAINST():

SELECT * FROM table2 
WHERE MATCH(post_title, post_body) AGAINST('database optimization');

If you’re using UltimateWB website builder, we already have the FULLTEXT indexes created for you, ready to go. You don’t have to do anything here yourself.

Full-Text Modes: Natural Language vs. Boolean Mode

Mode 1: Natural Language Mode (Default)

MySQL interprets the search string as human language. It automatically ignores stop words (like “the”, “is”, “at”) and ranks results by relevance.

SELECT *, MATCH(post_title, post_body) AGAINST('mysql database') AS relevance
FROM table2
WHERE MATCH(post_title, post_body) AGAINST('mysql database')
ORDER BY relevance DESC;

Mode 2: IN BOOLEAN MODE (Advanced Control)

Boolean mode allows you to use special operators (+, -, *, “”) to craft precise search rules. It does not require relevance sorting, making it extremely fast.

SELECT * FROM table2 
WHERE MATCH(post_title, post_body) AGAINST('+mysql -postgres "slow query"*' IN BOOLEAN MODE);

Operator	Meaning	Example	Explanation
+	Must contain	+mysql	Row must have “mysql”
–	Must NOT contain	-php	Row must not have “php”
(no operator)	Optional	mysql php	Row contains at least one of them (ranked higher if both)
*	Wildcard / Prefix	optimiz*	Matches “optimize”, “optimization”, “optimizing”
“”	Exact phrase	“slow query”	Matches the exact phrase sequentially

The UltimateWB website builder search functions include these options built-in – the code is created dynamically based on what the user inputs in the search box – much like how the Google search box used to really honor double quotes around a search term as wanting an exact match.

Part 4: Step-by-Step Optimization Workflow

When faced with a slow query, follow this 4-step checklist:

[1. Identify Slow Query] 
        │
        ▼
[2. Run EXPLAIN ANALYZE] ──► Look for large `actual time` & huge `rows` count.
        │
        ▼
[3. Diagnose the Cause]  ──► Is it doing a Full Table Scan?
                         ──► Is it using an unselective index?
                         ──► Is it using `LIKE '%...%'`?
        │
        ▼
[4. Apply the Fix]       ──► Add Composite Index using ESR Rule.
                         ──► Replace `LIKE` with `FULLTEXT` search.
                         ──► Avoid `SELECT *` if you only need 2 columns.

Summary Reference Sheet

• EXPLAIN = Guess performance.
• EXPLAIN ANALYZE = Measure real performance.
• Filter selectivity matters: Index high-unique-value columns first (user_id), low-unique-value columns second (status).
• ESR Rule: (Equality Columns, Sort Columns, Range Columns).
• Use FULLTEXT + IN BOOLEAN MODE for fast wildcard text search instead of LIKE ‘%search%’.

---

Frequently Asked Questions (FAQ)

Q1: Can adding indexes bloat the database?

Yes. Indexes are not “free.” They are separate data structures stored on disk and loaded into RAM (InnoDB Buffer Pool).

When you add an index, you are making a conscious trade-off: Trading disk space and write speed for read speed.

• Disk Bloat: An index takes up storage space. On large tables with millions of rows, an index on a couple of columns can easily take up hundreds of megabytes or even gigabytes.
• Write Penalties (The “Index Tax”): Every time you run an INSERT, UPDATE, or DELETE, MySQL must update the table AND update every single index attached to that table. If a table has 15 indexes, a single INSERT forces MySQL to perform 16 write operations.

Rule of Thumb: Keep indexes on transactional tables (like orders or user activity) to a minimum (typically 3 to 7 indexes max per table). Remove unused indexes.

---

Q2: If adding indexes adds bloat and slows down writes, is it still a good idea to do it?

Yes, absolutely. In 99% of web applications, the benefits of indexing far outweigh the costs.

Here is why you should almost always use indexes despite the extra disk bloat:

1. Disk Space is Cheap; User Time is Expensive

A query that takes 3 seconds instead of 0.005 seconds will:

• Drive users away from your site (reducing revenue).
• Destroy your SEO rankings (Google penalizes slow websites).
• Frustrate your customers.

Losing users costs far more than the cost of disk space.

2. Web Apps follow the “90 / 10” Rule

Most web applications perform 90% Reads (SELECT) and only 10% Writes (INSERT/UPDATE).

Think about a forum post, a product listing, or a blog article:

• It is written once (INSERT).
• It is viewed 10,000 times by users browsing the site (SELECT).

Sacrificing 1 or 2 milliseconds on a single INSERT so that 10,000 users get instant page load times is one of the best trades you can make in database engineering.

3. Unindexed Queries Crash Servers

If a query takes 1 second, it holds open a database thread for that entire second.

If 50 users visit your site at the exact same time, your database will try to run 50 of those 1-second queries simultaneously. Your CPU will spike to 100%, MySQL will run out of connections, and your server will crash (Error: Too many connections).

Proper indexing keeps query execution times under 0.005 seconds, allowing a single database server to serve thousands of concurrent users without breaking a sweat.

💡 The Golden Rule of Indexing

You don’t need to avoid indexes – you just need to be intentional:

• ❌ Bad: Adding a single-column index to every column in your table “just in case.”
• ❌ Bad: Keeping 5 different composite indexes that all start with the exact same column.
• ✅ Good: Creating 3 to 6 targeted, composite indexes tailored specifically to your app’s actual WHERE and ORDER BY queries.

---

Q3: How do I find and delete unused indexes that are slowing down my writes?

In MySQL 8.0+, you can query the Performance Schema to find indexes that have never been used since the server restarted:

SELECT 
    OBJECT_SCHEMA AS database_name,
    OBJECT_NAME AS table_name,
    INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
  AND INDEX_NAME != 'PRIMARY'
  AND COUNT_STAR = 0
ORDER BY OBJECT_SCHEMA, OBJECT_NAME;

If an index shows COUNT_STAR = 0, no query has used it. You can safely drop it to speed up your INSERT and UPDATE queries!

A Quirk About MySQL FULLTEXT Indexes

You might wonder: “Can I delete title_2 (title, tags) since title (title, tags, content) already includes those columns?”

In MySQL, FULLTEXT indexes do not follow the Left-Prefix Rule.

If your PHP code runs:

WHERE MATCH(title, tags) AGAINST('search term');

MySQL requires an exact FULLTEXT index defined on (title, tags). It cannot use an index defined on (title, tags, content).

• Keep title IF your search code queries MATCH(title, tags, content)
• Keep title_2 IF your search code queries MATCH(title, tags)

---

Q4: I created an index, but MySQL is ignoring it! Why?

This is a very common issue. MySQL’s query optimizer might ignore your index for several reasons:

1. You wrapped the column in a function:
   • ❌ WHERE YEAR(created_at) = 2023 (Index ignored!)
   • ✅ WHERE created_at >= ‘2023-01-01’ AND created_at <= ‘2023-12-31’ (Index used!)
2. Data Type Mismatch:
   If user_id is a VARCHAR string, but you query WHERE user_id = 12345 (as an integer), MySQL converts the column types on the fly, which breaks index usage. Always match types: WHERE user_id = ‘12345’.
3. Low Cardinality (Not enough unique values):
   If a table has 1,000,000 rows, and 990,000 of them have status = 1, MySQL knows using the index is actually slower than just reading the table sequentially. It will deliberately ignore the index.
4. Using OR conditions across different columns:
   • ❌ WHERE status = 1 OR member_id = 5 (Often breaks single indexes unless a composite index or UNION is used).

Q5: What is a “Covering Index” and why is it the gold standard of speed?

A Covering Index is an index that contains ALL the columns requested by the query (in the SELECT, WHERE, JOIN, and ORDER BY clauses).

When a query is “covered,” MySQL gets 100% of the requested data directly from the light-weight index in RAM. It never touches the actual table on the disk.

• Query: SELECT postid, status FROM table2 WHERE userid = 1234;
• Covering Index: (userid, status, postid)

In EXPLAIN, you will see Using index in the Extra column. This indicates maximum performance.

---

Q5 – B Follow-up:

In this:
Query: SELECT postid, status FROM table2 WHERE userid = 1234;

Covering Index: (userid, status, postid)
Would it make more sense to do : userid,postid,status, or postid,userid,status ?

The short answer is: userid MUST be the first column.

Because of that rule, (postid, userid, status) is a bad choice, but between (userid, status, postid) and (userid, postid, status), they are 100% identical in performance for this specific query.

Here is why:

1. Why (postid, userid, status) does NOT work well

Indexes are ordered left-to-right, like a phone book (sorted by Last Name, then First Name).

If a phone book is sorted by postid first, and you ask MySQL: “Find all rows where userid = 1234”, MySQL cannot jump to 1234. It has to scan the entire phone book from page 1 to the end looking for userid = 1234.

Because your WHERE clause filters by userid, userid MUST be the first column in the index.

2. Why (userid, status, postid) vs (userid, postid, status) are equal

Once userid is the first column, MySQL uses it to jump straight to userid = 1234.

At that point, both status and postid are already sitting right there inside the index node. MySQL doesn’t care about the order of the 2nd and 3rd columns just to return their values in a SELECT.

• Option A (userid, status, postid): Jumps to userid = 1234 →→ Reads status & postid from RAM.
• Option B (userid, postid, status): Jumps to userid = 1234 →→ Reads postid & status from RAM.

Both execute in 0.000 milliseconds.

How to decide between Option A and Option B?

To pick the absolute best one, look at the other queries in your application:

Pick (userid, status, postid) IF you have queries like this:

-- You filter by BOTH userid AND status
SELECT * FROM table2 WHERE userid = 1234 AND status = 1;

(Because status is 2nd in the index, MySQL can use the index to filter both columns at once).

Pick (userid, postid, status) IF you have queries like this:

-- You filter by userid and SORT by postid
SELECT * FROM table2 WHERE userid = 1234 ORDER BY postid DESC;

(Because postid is 2nd in the index, MySQL gets the rows already sorted and avoids a slow filesort).

---

Q6: Why is SELECT * considered bad for query performance?

Using SELECT * hurts performance in three main ways:

1. It breaks Covering Indexes: If you select *, MySQL is forced to go to the disk to retrieve every single column, even if an index contains 90% of what you need.
2. Memory Overheads: Fetching text/blob columns you don’t need consumes excess RAM in MySQL, excess network bandwidth, and excess memory in your PHP/Node.js application.
3. Prevents In-Memory Sorting: When MySQL performs an ORDER BY, large SELECT * payloads force MySQL to write temporary sort files to the disk (Using filesort) instead of sorting in memory.

Q7: Should I index NULL values, or make columns NOT NULL?

Whenever possible, define your columns as NOT NULL DEFAULT ... if a meaningful default value exists (such as 0 for numbers or an empty string '' for text).

• Know the difference: An empty string ('') is a concrete text value (zero characters long), whereas NULL means the data is completely missing or unknown.
• When to use NULL: Use NULL when the absence of data has a distinct business meaning. For numeric, boolean, or date fields, NULL is essential because these types have no natural “blank” state; using placeholders (like 0 for a missing test score) can break math functions (AVG, SUM) and create ambiguous data.
• Storage Impact: In many database engines, nullable columns require a tiny bit of extra overhead (like a null-bitmap) per row to track whether the value is present.
• Index & Query Performance: While modern databases index NULL values just fine, queries using IS NULL or IS NOT NULL can sometimes behave differently than exact matches (WHERE column = 0), occasionally impacting the query optimizer’s choices depending on data distribution.

---

Q8: Why does the Slow Query Log say a query took 30 seconds, but in phpMyAdmin it runs in 0.001 seconds?

This frustrating situation happens for four main reasons:

1. phpMyAdmin automatically adds LIMIT 0, 25

When you paste a query like SELECT * FROM posts ORDER BY created_at into phpMyAdmin, phpMyAdmin silently rewrites it behind the scenes to:
SELECT * FROM posts ORDER BY created_at LIMIT 0, 25
Fetching 25 rows takes 0.001 seconds. But your application might be fetching all 100,000 rows without a limit, which takes 30 seconds.

2. The InnoDB Buffer Pool (RAM Caching)

• In the Slow Query Log: The query ran for the first time. MySQL had to read the data cold from the slow hard drive/SSD into memory (Disk I/O), taking 30 seconds.
• In phpMyAdmin: Because the query just ran, the data and indexes are now cached in server RAM (InnoDB Buffer Pool). MySQL reads it instantly from RAM in 0.001 seconds.

3. Row & Table Locking (Resource Contention)

The Slow Query Log records total elapsed time, including time spent waiting in a queue.
If your web application was running an UPDATE transaction on that table at the exact same moment, the SELECT query had to wait 29.9 seconds for the lock to release, and 0.1 seconds to actually execute. When you test it later in phpMyAdmin, there are no locks, so it runs instantly.

4. Prepared Statements vs. Hardcoded Values

In your web app code (PHP/Node), you use placeholders like WHERE status = ?. The database creates a generic execution plan. In phpMyAdmin, you paste hardcoded values like WHERE status = 1, allowing MySQL’s optimizer to make smarter shortcuts.

---

Q9: Is it really faster to use a subquery to fetch ONLY IDs first, and then fetch all fields in an outer query? (Deferred Joins)

YES! This technique is called a Deferred Join (or Late Row Lookup), and it is one of the most powerful optimization tricks in database engineering.

Instead of this standard slow query:

-- SLOW: Fetches huge text/blob columns for 100,020 rows, then discards 100,000 of them
SELECT * FROM re_forumposts 
WHERE status = 1 
ORDER BY added_date DESC 
LIMIT 100000, 20;

You rewrite it as a Deferred Join:

-- FAST: Uses index to find 20 IDs, then fetches full rows ONLY for those 20
SELECT p.* 
FROM re_forumposts p
JOIN (
    SELECT postid 
    FROM re_forumposts 
    WHERE status = 1 
    ORDER BY added_date DESC 
    LIMIT 100000, 20
) AS sub ON p.postid = sub.postid;

Why Deferred Joins are game-changers in two specific scenarios:

Scenario A: Deep Pagination (LIMIT 100000, 20 / Page 20+)

When you paginate deeply (e.g., page 500), MySQL must still scan all 100,000 previous rows before it can give you the 20 rows you asked for.

• Standard Query: MySQL loads all columns (including heavy content, subject, tags, etc.) for all 100,020 rows off the disk into memory, sorts them, throws away 100,000 rows, and returns 20. (Takes 5–10 seconds)
• Deferred Join: The subquery reads only the lightweight index tree (postid and added_date) in RAM for 100,020 rows, throws away 100,000 IDs, and then fetches the heavy text columns for ONLY the 20 winning IDs. (Takes 0.05 seconds)

Scenario B: FULLTEXT Searches with Sorting

Fulltext searches return results ranked by relevance score, but if you want to sort by something else (e.g., date or popularity), performance drops off a cliff.

Using a Deferred Join allows MySQL to perform the MATCH() AGAINST() on the lightweight FULLTEXT index first to grab just the postids, and then do a clean, fast join to pull the heavy content columns for the final display rows.

-- Ultra-fast Fulltext Pagination Pattern
SELECT p.* 
FROM re_forumposts p
JOIN (
    SELECT postid 
    FROM re_forumposts 
    WHERE MATCH(subject, msg) AGAINST('+mysql +optimization' IN BOOLEAN MODE)
    ORDER BY added_date DESC
    LIMIT 40, 20
) AS sub ON p.postid = sub.postid;

---

Related:

The Developer’s Guide to Identifying and Optimizing Slow MySQL Queries

---

Looking for a website builder that is flexible enough for a developer but easy enough for a beginner? Learn more about UltimateWB! We also offer web design packages if you would like your website designed and built for you.

Got a techy/website question? Whether it’s about UltimateWB or another website builder, web hosting, or other aspects of websites, just send in your question in the “Ask David!” form. We will email you when the answer is posted on the UltimateWB “Ask David!” section.