Solving High Page Load Times with Freento Operations Counter + Freento SQL Log
This case study demonstrates how we identified and resolved severe performance issues on category pages experiencing 10-second load times. Using Freento's diagnostic tools, we discovered excessive database queries caused by inefficient code loops, ultimately achieving a 4x performance improvement.
Client Story
The client operates a Magento store with numerous customer groups. Despite having Full Page Cache enabled, category pages were loading slowly for all visitors, including guests. The issue was particularly noticeable during high-traffic periods, with page load times reaching 10-12 seconds.
Investigation Process
Initial page load time measurements showed critical performance issues. New Relic monitoring revealed significant performance spikes during peak hours, making investigation urgent.
New Relic monitoring showing performance spikes during peak hours
Full Page Cache was working, but analysis using Freento Full Page Cache Analyzer revealed approximately 100 uncached page loads per day for each category page. This happened due to cache invalidations from customer group switches and post-order processes.
Full Page Cache Analyzer showing cache miss patterns
Database Query Analysis
The most concerning discovery was the 10-second load time for uncached pages. Using Freento Operations Counter, we measured and logged MySQL and Redis operations on the category pages.
Investigation Summary
| Metric | Before | After |
|---|---|---|
| MySQL Queries | ~7,000 | ~800 |
| Redis Operations | ~1,200 | ~600 |
| Page Load Time | 10s | 2.5s |
These numbers significantly exceeded expectations for a standard Magento installation. Further investigation with Freento SQL Log revealed numerous duplicate and similar SQL queries, indicating inefficient loop-based operations instead of bulk queries.
Key Findings
Freento SQL Log trace analysis identified the root cause:
🔴 Identical SQL queries executed multiple times
The same queries were being run hundreds of times per page load, fetching the same data repeatedly instead of caching or reusing results.
🔴 Loop-based single queries instead of collection bulk operations
Instead of loading multiple records in one query, the code was executing individual queries inside loops, creating thousands of unnecessary database round-trips.
🔴 Missing query result caching for repeated data access
Frequently accessed data was not being cached, forcing the application to re-query the database for information that rarely changes.
Duplicate queries overview in Freento SQL Log
Solutions Implemented
Primary Optimizations
- Replaced single queries in loops with bulk collection operations
- Implemented proper Magento collection usage patterns
- Added query result caching for frequently accessed data
- Optimized product attribute loading
Secondary Findings
- Identified Full Page Cache invalidation patterns
- Created separate optimization ticket for cache issues
- Documented all performance bottlenecks found
- Established monitoring for query counts
Performance Improvements
Performance Metrics Comparison
Results
9x Query Reduction
MySQL queries reduced from ~7,000 to ~800 per page load, dramatically decreasing database load.
4x Speed Improvement
Uncached page load time reduced from 10 seconds to 2.5 seconds, improving user experience.
Conclusion
The combination of Freento Operations Counter and SQL Log provided crucial visibility into performance bottlenecks. By transforming inefficient loop-based queries into bulk operations, we achieved a 4x improvement in page load times. The secondary discovery of cache invalidation issues, addressed in a follow-up case, demonstrates the value of comprehensive performance analysis.
Technical Implementation Details
Detailed analysis of SQL query traces and performance monitoring data allowed us to pinpoint exact code locations causing the performance degradation. Using Freento's diagnostic tools, we were able to identify not only the primary issue but also related performance problems that compounded the slow page loads.
Let us analyze your database queries and optimize your code for maximum performance. Get a comprehensive performance audit with actionable solutions.