Insights:Website Architecture and Database Refactoring Order: What to Do First
Step 0: Measure First, Don't Guess
- Record three numbers: homepage TTFB, listing page TTFB, and detail page TTFB.
- Open the slow query log and identify the top 10 slowest SQL statements.
- Check server metrics: CPU, memory, disk I/O, and connection count.
Without a baseline, you can't prove the fixes actually work.
Step 1: Fix Duplicate Queries at the Application Layer
Highest impact, lowest risk.
- Find looped queries inside list pages and replace them with a single batch fetch.
- For repeated queries on the same table within one request, add in-request caching.
- Lift nested queries from templates up to the controller layer.
Verify: SQL query count per page drops, and TTFB follows.
Step 2: Add Indexes
- Create indexes on frequently filtered fields: status, category, and time.
- In composite indexes, put filter columns on the left and sort columns on the right.
- Delete unused redundant indexes.
Note: More indexes isn't always better — be especially restrained with heavily written tables.
Step 3: Optimize SQL Writing Patterns
| Problematic Pattern | Fix | Effect |
|---|---|---|
WHERE ... ORDER BY on non-indexed column |
Add a sort index or restructure the query | Eliminates filesort |
SELECT * on large tables |
Select only the needed columns | Reduces table lookups and data transfer |
| Functions used in WHERE clause | Move the function to the value side | Allows index usage |
| Deeply nested subqueries | Switch to JOIN or a temporary table | Reduce duplicate scans |
Step 4: Add Page Caching and Application Caching
- Make list pages static, with short TTL per category.
- Put hot data in memory cache (Redis / APCu) with explicit expiration points.
- Actively clear relevant caches after content updates instead of relying on natural expiration.
Note: XML files like sitemaps and feeds are not suitable for page caching and can easily be overwritten with stale content.
Step 5: Only Then Consider Architecture Changes
Only discuss these after single-server optimizations hit their limits:
- Read-write separation.
- Table or database sharding.
- Dedicated search service (extract full-text search out of the database).
- Object storage for static assets.
Maintain Rollback Points at Every Step
- Export table schemas before modifying indexes.
- Keep original SQL statements before making changes.
- Add a toggle switch when introducing caching so it can be disabled immediately if issues arise.
Acceptance Criteria
- Homepage TTFB under 300ms.
- List page SQL queries kept to 10 or fewer.
- Slow query log count reduced by 80%.
- Error rate on critical pages unchanged or decreased.
Boundaries
Architecture changes address performance and capacity issues — they won't automatically improve content quality or rankings. Performance is just one aspect of search experience; don't raise system complexity just for benchmark scores.
Related Reading: