Database Optimization·2026-02-10·By Md. Kamrul Islam
Relational Schema Optimization for Multi-Tier Municipal & Government Portals
Lessons learned optimizing large-scale MySQL schemas for the Department of Environment and municipal holding tax systems.
The Challenge of Large-Scale Government Records
When automating the Department of Environment's site clearance and laboratory testing dossiers, hundreds of thousands of documents, inspection dates, and multi-tier approval states must be queried simultaneously.
Without carefully composed compound indexes and normalized entity partitions, administrative filtering by district, approval stage, and inspection date degrades quickly.
Strategies Implemented
- Composite Index Ordering:
- When queries filter on `status` and order by `created_at`, creating composite indexes with equality columns first (`status, created_at DESC`) avoids filesort operations.
- Selective Eager Loading:
- Eliminating N+1 queries in complex multi-tier relational models by specifying explicit foreign key relationships and using Laravel's `with(['applicant', 'documents:id,application_id,file_type'])`.
- Audit Trails Without Bloat:
- Keeping transactional tables clean by separating heavy historical audit logs and document change logs into dedicated append-only tables.