Reduced database query time from 8-9 minutes to ~1 minute in our Frappe ERPNext system.
Here’s how strategic composite indexing transformed our General Ledger report performance:
The Challenge:
Our custom General Ledger report was taking 8-9 minutes to load, impacting user productivity. With millions of GL Entry records, standard queries were doing full table scans.
The Solution:
We implemented composite indexes that match our query filter patterns, achieving an 88% performance improvement.
Key Optimization Techniques:
Composite Index Strategy
- Created index matching exact query filter order
- Index: (company, is_cancelled, posting_date, account, voucher_type, voucher_no)
- WHERE clause conditions MUST match index column order
Query Optimization
- Used index hints: USE INDEX (idx_gl_entry_report_query)
- Implemented cursor-based pagination (better than OFFSET)
- Batch fetching for related data (5,000 records per batch)
Code-Level Improvements
- Filter order enforced to match index structure
- Minimal field selection for better index coverage
- Proper parameterized queries
Results:
Query Time: 8-9 minutes → ~1 minute (88% improvement)
Database Load: Significantly reduced full table scans
User Experience: Reports now load in real-time
Best Practices for Frappe ERPNext Database Optimization:
- Analyze slow queries first (EXPLAIN, slow query log)
- Create composite indexes matching filter patterns
- Order index columns by selectivity (most selective first)
- Ensure WHERE clause order matches index column order
- Use cursor-based pagination for large datasets
- Implement batch processing for related data fetches
- Monitor index usage and remove unused indexes
Key Takeaway: Strategic indexing isn’t just about adding indexes—it’s about understanding your query patterns and designing indexes that match them perfectly.