SQL Optimization

Query tuning starts from knowing what the database actually does with a statement. This note opens with the SELECT execution order. It then lists habits that cut work, such as naming columns explicitly, removing needless DISTINCT, turning sub-queries into joins, and indexing the columns used by WHERE, JOIN and ORDER BY. Keyset pagination gets its own section with the trade-offs against LIMIT and OFFSET. Prepared statements, obfuscated conditions and common table expressions follow. The closing case study walks through a MariaDB query that dropped from 50 seconds to 1.5 seconds on a table of 500K+ rows. It ends with the lessons behind that gain.

SELECT Execution Order

1. FROM and JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT/OFFSET

SQL Query Optimization

Resources

• Mít đặc và biết tuốt về tối ưu SQL (Everything about SQL optimization)
• 12 lý do khiến MySQL truy vấn chậm (12 reasons MySQL queries are slow)
• Performance Tuning SQL (Why your neighbor's SQL runs faster than yours)

Optimization Techniques

• Use explicit column names instead of SELECT *
• Consider removing DISTINCT when it is not needed
• Convert sub-queries into JOINs when possible
• Use UNION ALL instead of UNION
• Avoid OR across conditions on different columns
• Paging: use LIMIT/OFFSET or cursor-based pagination
• Put results in a cache
• Add indexes for WHERE/JOIN/ORDER BY
• Use full-text search for text search
• Deferred Join: JOIN against a subset that has already been filtered and limited

Keyset Pagination (Query by Last ID)

• Overview: Keyset pagination is an efficient pagination method for large datasets. It uses a unique key to locate where the next page starts
• Comparison with offset pagination:
  • Offset pagination uses OFFSET and LIMIT, but becomes inefficient on large datasets because the database must scan past many records
  • Keyset pagination uses the value of a key column (usually the ID) to determine the start of the next page, which makes queries faster and more stable
• How it works: A WHERE condition on the key column value marks where the next page starts, combined with LIMIT to cap the number of returned results
• Advantages:
  • Better query performance on large datasets
  • Shorter response times
  • Avoids duplicated or missing rows when data changes during pagination
• Disadvantages:
  • Cannot jump to an arbitrary page
  • Requires a key column with a clear ordering and unique values
• Applications: Suited to systems with large data volumes that need efficient pagination, such as social networks, e-commerce sites, and APIs that list products, posts, or users

Prepared Statements

• Use bind variables instead of string concatenation
• Benefits: Better performance (the execution plan is reused) and security (protection against SQL injection)
• Reference

Avoid SELECT *

• Increases network traffic, CPU usage, and memory usage
• Hinders optimization; covering indexes cannot be used effectively

Obfuscated Conditions

• Comparing a numeric column with a numeric string
• Applying a function to an indexed column in WHERE
• Combining columns that have no suitable index

Common Table Expressions (CTE)

• Define a named, temporary result set
• Recursive CTE: For hierarchical and tree data
  • Anchor member: initialization
  • Recursive member: expansion
  • Reference
  • Recursive CTE

Case Study: MariaDB Performance Issue with Large Data Types

• Problem: A query took 50 seconds when paginating with a large OFFSET on a table with 500K+ records
• Problem query: SELECT * FROM posts WHERE deleted_at IS NULL ORDER BY group_id DESC, post_number DESC LIMIT 15 OFFSET 418000
• Root causes:
  • The content field (LONGTEXT) was loaded into memory during ORDER BY, consuming RAM and CPU
  • The large OFFSET forced the database to scan through 80% of the index rows
  • SELECT * loaded every column even when not needed
• Solution:
  • Split the table: a metadata table (id, group_id, post_number) and a separate content table
  • SELECT only the necessary fields instead of SELECT *
  • Improved from 50 seconds to 1.5 seconds
• Lessons:
  • Design the schema carefully: analyze query flows and data volume before building
  • Normalization: move large fields (LONGTEXT, BLOB) into separate tables to avoid unnecessary loading
  • Understand data types: TEXT/BLOB are heavier than VARCHAR/INT, especially when sorting or searching
  • Indexes are not a silver bullet: understand how the database engine works, since an index can slow down INSERT/UPDATE
  • Pagination with a large OFFSET is very bad: prefer keyset/cursor-based pagination
  • Database partitioning: can improve performance on large data
  • Monitoring: use Prometheus and Grafana to watch the system from the start
  • Caching: use Redis for static or rarely changing data
  • Consider storing text in files instead of the database for some use cases

See also: Storage Engines · Sharding Partitioning · MySQL