Website Architecture and Database Refactoring Order: What to Do First

The two things worst feared in a redesign are fixing the wrong thing and changing nothing after completion. Following a "measure first, act second" order ensure…

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: