Insights:Website Architecture and Database Tools & Commands: Ready to Reuse
Discovery: Identify Where It's Slow
- Enable the slow query log with a threshold of 200ms and monitor for one week.
- Record page TTFB baselines: take the average of 10 measurements each for the homepage, listing page, and detail page.
- Check when server load peaks occur.
Interpretation: If slow queries are concentrated in a few SQL statements, the problem is query-related; if they're spread across many SQL statements, it indicates overall pressure.
Diagnostics: The Queries Themselves
- Run
EXPLAINon slow SQL, focusing on: - Whether
typeisALL(full table scan). - Whether
keyis empty (index not used). - Whether
rowsscanned is much larger than rows returned. - Whether
ExtrashowsUsing filesortorUsing temporary.
If you see ALL combined with a large rows value, the issue is likely confirmed.
Diagnostics: Index Usage
- Check the index list to confirm field names and order match expectations.
- Review index statistics to identify unused indexes and consider removing them.
- Compare query conditions against the leftmost column of each index.
Common Misdiagnosis: An index may exist but with incorrect column order, causing the execution plan to still fall back to a full table scan.
Diagnostics: Connections and Concurrency
- Check current connections versus the maximum to determine if the limit is near.
- Examine waiting connections to identify long-running transactions holding locks.
- Verify whether the connection pool configuration aligns with the database's connection limits.
Diagnostics: Caching
- Check cache hit rate and key count growth trends.
- A low hit rate paired with high traffic indicates that hot data isn't staying in cache.
Validation: Was It Effective?
- Run the execution plan for the same SQL query both before and after the change, then compare
rowsandtype. - Average the TTFB across 10 measurements for the same page, before and after the modification.
- Monitor the weekly trend in slow query log entries.
Without a baseline, there's no way to validate results. Always record numbers before making changes.
A Fixed Diagnostic Sequence
- Slow query log → identify the top 10 entries.
- Execution plan → determine whether a full table scan is happening.
- Index statistics → assess whether indexes are effective.
- Connections and locks → check for resource contention.
- Cache → evaluate whether caching could be introduced.
- Retest → compare against the baseline.
Boundaries
These tools can only tell you "where the bottleneck is" — not "how the business should be designed." Adjustments to the data model and access patterns still require sound business judgment.
Related Reading: