Skip to content
Tutorials Center
My learning

Topics

Lesson 46 of 75 ยท Indexes and Performance

Query Optimization Techniques

Speed up MySQL queries: sargable predicates, functions on indexed columns, implicit casts, keyset pagination versus OFFSET, SELECT *, subquery rewrites and hints.

You now know what an index is, how to design one, and how to read EXPLAIN. This lesson is the other half of tuning: writing queries the optimizer can serve well. A perfect index is useless if the WHERE clause hides the column behind a function, a type mismatch forces a scan, or the application pages through a million rows with OFFSET.

Most optimizations come down to one idea: give the optimizer a condition it can turn into an index seek. Such a condition is called sargable (from "Search ARGument ABLE"). The live examples use SQLite where its behavior is similar. Where MySQL behaves differently, the lesson says so, and MySQL-only features (hints, EXPLAIN output) are read-only.

Five habits of fast queries

Sargable filters Compare the bare column to a constant.
Matching types No hidden casts on indexed columns.
Keyset paging Seek past the last row instead of OFFSET.
Named columns Skip SELECT *, enable covering indexes.
1 / 2

Sargable predicates: do not wrap the column

An index stores the raw column values in order. If your condition applies a function to the column, MySQL would have to compute that function for every row to see which ones match, so it cannot seek in the index. The fix is almost always to move the work to the other side, to the constant.

Slow (function on the column) Fast (bare column)
WHERE YEAR(placed_at) = 2025 WHERE placed_at >= '2025-01-01' AND placed_at < '2026-01-01'
WHERE DATE(created_at) = '2025-06-01' WHERE created_at >= '2025-06-01' AND created_at < '2025-06-02'
WHERE total + 10 > 100 WHERE total > 90
WHERE LEFT(code, 3) = 'ABC' WHERE code LIKE 'ABC%'
WHERE LOWER(email) = '[email protected]' a case-insensitive collation (the default) or a functional index

Use half-open ranges (>= the start, < the next start) for dates. They stay correct for DATETIME values with times and fractional seconds, whereas BETWEEN '2025-06-01' AND '2025-06-01 23:59:59' quietly misses the last second.

SQL A function on the column versus a range

Press Run (or Ctrl + Enter) to see the result here.

The first plan says SCAN ... USING COVERING INDEX, which reads every entry, and the second says SEARCH with a range. MySQL 8.0.13 and later can also index an expression directly, INDEX ((YEAR(placed_at))), which is useful when you cannot change the query. Prefer the range rewrite when you can, because it needs no extra index.

Other sargability traps: a leading wildcard (LIKE '%son'), OR between different columns (an index per branch is needed, and UNION ALL of two queries is often faster), and NOT or <> conditions, which usually match most of the table anyway.

Implicit casts and mismatched types

When MySQL compares values of different types, it converts one side. If it converts the column, the index is lost. The classic case is a string column compared with a number:

SQL
-- phone is VARCHAR(20) with an index
SELECT * FROM users WHERE phone = 5551234;     -- column converted to a number: scan
SELECT * FROM users WHERE phone = '5551234';   -- constant is a string: index seek

With the number, MySQL converts every phone value to a number to compare. That is a full scan, and it also changes the result, since '5551234abc' and '5551234 ' both convert to 5551234 and match (with a warning). The opposite case is harmless: WHERE int_column = '42' converts the constant, so the index works.

The same problem appears in joins between columns with different types (INT against VARCHAR, INT against BIGINT is fine, or utf8mb4 against utf8mb3) and different collations. The index on the side that must be converted is not used for the lookup. EXPLAIN warns you: run SHOW WARNINGS right after it and look for a note mentioning convert. The cure is to make the column types and character sets match in the schema (see Choosing the Right Data Types and Character Sets and Collations) and to pass parameters in the right type from application code.

SQLite works differently, which makes a useful contrast. A column has an affinity, and SQLite applies it to the constant instead of the column:

SQL Types in comparisons: SQLite versus MySQL

Press Run (or Ctrl + Enter) to see the result here.

Do not read the first plan as "numbers are safe". The lesson is that each database has its own conversion rules, so test your actual engine. On MySQL, pass strings for string columns.

Pagination: OFFSET versus keyset

Paging with LIMIT ... OFFSET looks innocent:

SQL
SELECT id, title FROM events ORDER BY id LIMIT 20 OFFSET 400000;

To return rows 400,001 to 400,020, MySQL must produce and throw away the first 400,000. Page 1 is fast, page 20,000 is slow, and a crawler or an export script that walks all pages gets quadratically slower. Rows inserted while a user pages also cause duplicates or skips.

Keyset pagination (also called the seek method) remembers where the last page ended and seeks past it using the index:

SQL
-- First page
SELECT id, title FROM events ORDER BY id LIMIT 20;

-- Next page: pass the last id you showed
SELECT id, title FROM events WHERE id > 400020 ORDER BY id LIMIT 20;

The cost is the same on every page, because the index seek jumps straight to id > 400020. When you sort by a column that is not unique, such as created_at, add the unique id as a tie-breaker in both the ORDER BY and the comparison, with a matching index:

SQL
-- INDEX (created_at, id)
SELECT id, created_at, title
FROM events
WHERE (created_at, id) < ('2025-06-01 12:00:00', 98765)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The row comparison (a, b) < (x, y) means "a < x, or a = x and b < y", and MySQL's range optimizer can use it with the composite index. Keyset paging cannot jump to "page 500" directly, and it suits "next page" and infinite-scroll interfaces, APIs and exports. If you need page numbers, cap the depth or accept the OFFSET cost for small pages.

SQL OFFSET versus keyset

Press Run (or Ctrl + Enter) to see the result here.

Both approaches return rows 4001 to 4005, but the keyset plan reads SEARCH ... (rowid>?), jumping straight to the right place. The last two queries show the tie-breaker: several events share a timestamp, and the second page continues exactly after id 4998 without repeating or skipping.

Avoid SELECT * and fetch less

SELECT * is convenient and costly:

  • It reads every column, including wide TEXT and BLOB values that live off-page in InnoDB (see InnoDB Architecture).
  • It rules out covering indexes, because the query needs columns no index contains.
  • It sends more data over the network and uses more application memory.
  • It breaks callers when columns are added or reordered.

Name the columns you need. Also fetch fewer rows: put a LIMIT on queries that feed a screen, test existence with EXISTS (stops at the first match) instead of COUNT(*) > 0 (counts everything), and avoid ORDER BY RAND(), which sorts the entire table. Finally, avoid the N+1 pattern, where the application runs one query for a list and then one more per row. Replace it with a join or a single WHERE id IN (...) query.

Rewriting subqueries

MySQL 8.0 optimizes many subqueries well: IN (SELECT ...) and EXISTS can be turned into semijoins, NOT EXISTS into an antijoin (8.0.17+), and simple derived tables are merged into the outer query. Still, some forms are worth rewriting by hand.

A scalar subquery in the SELECT list that references the outer row runs once per outer row:

SQL
SELECT c.name,
       (SELECT SUM(o.total) FROM orders o WHERE o.customer_id = c.id) AS spent
FROM customers c;

A join with aggregation reads orders once:

SQL
SELECT c.name, SUM(o.total) AS spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Be careful with NOT IN. If the subquery can return a NULL, x NOT IN (...) can never be true, and the query silently returns nothing. NOT EXISTS is safe and usually faster:

SQL NOT IN versus NOT EXISTS with NULL

Press Run (or Ctrl + Enter) to see the result here.

The first query returns no rows at all, which is the NULL trap, and the second returns Cy. Always confirm a rewrite returns the same rows as the original before comparing speed, and use EXPLAIN to confirm the plan improved. Rewrites are not guaranteed wins: sometimes the optimizer already did the same thing internally.

Optimizer hints, last

When statistics are good, an index exists and the optimizer still picks a poor plan, MySQL lets you steer it. Hints go in a special comment right after SELECT:

SQL
SELECT /*+ INDEX(o idx_customer_placed) */ o.id, o.total
FROM orders o
WHERE o.customer_id = 7;

SELECT /*+ MAX_EXECUTION_TIME(2000) */ COUNT(*) FROM big_log WHERE level = 'ERROR';

SELECT /*+ JOIN_ORDER(c, o) */ c.name, o.total
FROM orders o JOIN customers c ON c.id = o.customer_id;

-- Older style, still common:
SELECT * FROM orders FORCE INDEX (idx_customer_placed) WHERE customer_id = 7;

INDEX and NO_INDEX pick indexes, JOIN_ORDER and JOIN_PREFIX fix join order, MAX_EXECUTION_TIME kills a query after a number of milliseconds (a good guard for ad-hoc reporting), and SET_VAR changes a session variable for one statement. These statements are read-only here, because they use MySQL's own syntax.

A hint freezes today's plan. When the data grows or its distribution changes, the hinted plan may become the slow one while the optimizer would have adapted. Use hints as a documented, temporary fix: first try fresh statistics (ANALYZE TABLE), a better index and a rewrite, and revisit every hint during upgrades.

A checklist

  1. Is every filter column compared bare, with no function, arithmetic or mismatched type?
  2. Does an index start with the equality columns and then one range or sort column?
  3. Can the query be covering by naming only the columns it needs?
  4. Is pagination keyset-based for deep or unbounded lists?
  5. Do EXPLAIN and EXPLAIN ANALYZE agree that the work shrank?

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which rewrite makes WHERE YEAR(placed_at) = 2025 able to use an index on placed_at?

    A half-open range compares the bare column with constants, so the index can seek. The other forms still wrap the column or are not equivalent.
  2. 2. In MySQL, why can WHERE phone = 5551234 be slow when phone is a VARCHAR column with an index?

    When types differ, MySQL converts the column side, which prevents an index seek and can also match unexpected values like '5551234abc'. Pass a string: WHERE phone = '5551234'.
  3. 3. Why is keyset pagination faster than OFFSET for deep pages?

    OFFSET N must produce N rows and throw them away. Keyset paging uses WHERE key > last_value, which is an index seek with constant cost.
  4. 4. A subquery in NOT IN can return NULL. What happens?

    x NOT IN (..., NULL) evaluates to NULL for every x not found, which counts as not true. Use NOT EXISTS or filter the NULLs out.
  5. 5. What is the main risk of an optimizer hint such as FORCE INDEX?

    The optimizer normally adapts its plan to statistics. A hint overrides that choice permanently, even after it stops being the best one.

Summary

  • Keep predicates sargable: compare the bare column to a constant. Move functions and arithmetic to the constant side, and use half-open ranges for dates.
  • Avoid hidden conversions: match column types, charsets and collations, and pass strings for string columns. Check SHOW WARNINGS after EXPLAIN.
  • Replace deep OFFSET with keyset pagination, using a unique tie-breaker column for non-unique sorts.
  • Name your columns instead of SELECT *, limit rows, use EXISTS for existence, and avoid N+1 queries.
  • Prefer NOT EXISTS to NOT IN, and consider joins for per-row scalar subqueries, but always verify that the results and the plan improved.
  • Treat optimizer hints as a last resort and revisit them as data changes.

Next: Finding Slow Queries, where you learn how to discover which queries deserve this attention.