PHP Loyalty Optimization

A legacy PHP-based loyalty platform was struggling with crippling latency as its user base grew, primarily due to unoptimized database queries and monolithic data processing.

The existing system used complex SQL joins that fetched entire datasets (millions of rows) into memory for processing, leading to frequent timeouts and high server costs. There was no pagination at the database level, and the system lacked modern APIs for integration with third-party partners. The goal was to modernize the codebase, optimize the data layer for sub-second responses, and implement a new suite of performant REST APIs.

Our approach focused on deep-level SQL optimization and structural modernization of the application's data access layer.

Key performance improvements included:

• Replacing "Select All" logic with precise database-level pagination using LIMIT/OFFSET.
• Refactoring complex 10+ table joins into efficient, indexed sub-queries and CTEs.
• Implementing query caching for high-read loyalty point lookups.
• Developed 20+ new REST endpoints using modern PHP standards (PSR-12).

By optimizing the SQL queries and reducing memory overhead, we achieved a 95% reduction in page load times—from 15+ seconds to under 400ms. The new API layer allowed the client to integrate with mobile apps and POS systems, opening new revenue streams while cutting infrastructure costs by 40%.