Optimizing Forge SQL on a 600K database with TiDB EXPLAIN

This post details how to optimize Forge SQL queries for large datasets (600K+ rows) by leveraging TiDB's EXPLAIN and EXPLAIN ANALYZE tools. It demonstrates a step-by-step optimization process, starting with identifying performance bottlenecks caused by full table scans and expensive joins in a pagination query. The author then introduces adding indexes to target foreign keys and sorting columns, which improved execution time by nearly 50% but still hit memory limits. The final optimization involves rewriting the query using Common Table Expressions (CTEs) to retrieve a small subset of rows first, significantly reducing memory usage and enabling scalability beyond Forge's memory and time constraints. The post also provides practical setup instructions and code examples for reproducing the optimizations.