Database Optimization for Legacy PHP Platforms

The database is usually the bottleneck. Your PHP application might be fast, but a poorly optimized database can slow everything down to a crawl. Users experience timeouts. Pages take 30 seconds to load. Administrators log out in frustration.

Across 20+ years of legacy PHP rescue work, database optimization has consistently been the single biggest performance gain available — often before any application code changes at all. The pattern holds up: roughly 80% of the improvement comes from 20% of the fixes, and that 20% is almost always strategic indexing and query restructuring, not new hardware.

Here is the step-by-step database optimization playbook we use.

Step 1: Establish a baseline—measure your current performance

Enable slow query logging in MySQL:

Add to your MySQL configuration (my.cnf or my.ini):

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 0.5
log_queries_not_using_indexes = 1
      

This logs all queries taking longer than 0.5 seconds. Run your application under realistic load for several hours to collect data.

Use EXPLAIN to analyze query performance:

For each slow query, use EXPLAIN to see the execution plan:

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND created_date > '2025-01-01';
      

Look for:

• type: ALL — Full table scan (bad). Should be RANGE, INDEX, or UNIQUE
• rows: huge number — Query examining too many rows (indicates missing index)
• key: NULL — No index used (critical problem)
• Extra: Using filesort — Sorting happening in memory instead of using index (slow for large datasets)

Tools for analysis:

• MySQLTuner: Script that analyzes your database configuration and recommends improvements
• Percona Monitoring & Management: Visual interface for slow query analysis
• MySQL Workbench: GUI tool with query analysis and profiling

Step 2: Fix missing indexes

Most slow queries are slow because they lack proper indexes. Adding indexes is often the single most effective optimization.

Which columns need indexes?

Identify and index columns that are:

• Used in WHERE clauses frequently
• Used in JOIN conditions
• Used in ORDER BY clauses
• Used in GROUP BY clauses
• Foreign key columns (always index these)

Indexing strategy:

Single-column indexes: For columns queried individually

ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);
ALTER TABLE orders ADD INDEX idx_created_date (created_date);
      

Composite indexes: For multi-column queries (order matters!)

ALTER TABLE orders ADD INDEX idx_customer_date (customer_id, created_date);
      

When creating composite indexes, put the most selective columns first (column that filters out the most rows).

Be careful with indexes:

• Each index slows down INSERT/UPDATE/DELETE operations (they must update the index too)
• Too many indexes (more than 5-7 per table) causes the optimizer to make poor choices
• Large indexes consume memory and disk space
• Regularly remove unused indexes

Find unused indexes:

SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage 
WHERE object_schema != 'mysql' AND count_star = 0 AND index_name != 'PRIMARY' 
ORDER BY object_schema, object_name;
      

Step 3: Optimize query structure

Common query anti-patterns to fix:

Anti-pattern: SELECT *

Bad:

SELECT * FROM users WHERE id = 123;
      

Good (only fetch columns you need):

SELECT id, email, name FROM users WHERE id = 123;
      

Fetching unnecessary columns wastes network bandwidth and memory.

Anti-pattern: N+1 queries

Bad:

$users = db_query("SELECT id FROM users LIMIT 100");
foreach ($users as $user) {
    $profile = db_query("SELECT * FROM profiles WHERE user_id = " . $user['id']);
}
      

This runs 101 queries! For 100 users, you execute 101 separate database calls.

Good (use JOIN):

SELECT u.id, u.email, p.bio FROM users u 
JOIN profiles p ON u.id = p.user_id LIMIT 100;
      

Single query, infinitely faster.

Anti-pattern: Unnecessary subqueries

Bad:

SELECT * FROM orders WHERE customer_id IN 
    (SELECT id FROM customers WHERE country = 'USA');
      

Good (JOIN):

SELECT o.* FROM orders o 
JOIN customers c ON o.customer_id = c.id 
WHERE c.country = 'USA';
      

Anti-pattern: LIKE with leading wildcard

Bad:

SELECT * FROM products WHERE name LIKE '%widget%';
      

This cannot use indexes and scans the entire table.

Better: use full-text search if available, or only use trailing wildcards:

SELECT * FROM products WHERE name LIKE 'widget%';
      

Step 4: Schema denormalization (selective, when justified)

Use only after profiling shows specific queries as bottlenecks.

Denormalization means storing redundant data to avoid expensive joins:

Example: Instead of joining order + customer + total every time:

Denormalized approach: Store customer_email and order_total directly in orders table. Trade-off: when customer email changes, update multiple rows. But common queries become faster.

Denormalization is complex and increases maintenance burden. Only use if profiling proves the benefit.

Step 5: Implement query result caching

Application-level caching (recommended):

Cache expensive query results in memory:

// Check cache first
$cache_key = 'customer_' . $customer_id;
$customer = cache_get($cache_key);

if (!$customer) {
    // Cache miss - run query
    $customer = db_query("SELECT * FROM customers WHERE id = " . $customer_id);
    cache_set($cache_key, $customer, 3600); // Cache for 1 hour
}
      

Caching tools: Redis, Memcached, or even file-based caching

Cache invalidation strategy: This is critical. When data changes, invalidate the cache:

// Update customer
db_query("UPDATE customers SET email = 'new@example.com' WHERE id = 123");

// Invalidate the cache
cache_delete('customer_123');
      

Database-level caching:

MySQL Query Cache (MySQL 5.7 and earlier) can cache query results automatically. However, it has significant overhead and is removed in MySQL 8.0+. For modern versions, rely on application-level caching.

Step 6: Table maintenance and cleanup

Bloat analysis: Old platforms accumulate junk data (deleted records, obsolete columns, unused tables)

// Check table sizes
SELECT table_name, round(((data_length + index_length) / 1024 / 1024), 2) as size_mb 
FROM information_schema.tables 
WHERE table_schema = 'your_database' 
ORDER BY size_mb DESC;
      

Are there tables you no longer use? Archive or delete them.

Table optimization: Defragment tables after large DELETE operations

OPTIMIZE TABLE large_table;
      

Step 7: Database server tuning

Critical MySQL settings to review:

• max_connections: Are you hitting connection limits?
• innodb_buffer_pool_size: Should be 50-70% of system RAM for dedicated database servers
• query_cache_size: Disable for MySQL 5.7+ (overhead outweighs benefit)
• log_slow_queries: Enable to identify problematic queries

Use MySQLTuner to get specific recommendations for your server.

When to rebuild instead of optimize

Sometimes optimization is not enough. Consider a database rebuild if:

• Schema is fundamentally broken (poor design, missing primary keys, bad normalization)
• Tables have millions of rows but queries still take 30+ seconds after optimization
• You find hundreds of unused indexes and columns
• Table locks are causing transaction gridlock

In these cases, a fresh database design with data migration is faster than piecemeal optimization.

Performance expectations after optimization

Well-executed database optimization typically delivers:

• Query response time: 50-90% reduction
• Server load: 30-60% reduction
• Database throughput: 2-5x improvement
• User experience: Significantly faster page loads, fewer timeouts

These improvements compound as your platform scales.

Next steps: Database assessment and optimization

If your legacy PHP platform is slow, database optimization should be your first step before considering platform migration. We can audit your database, identify bottlenecks, and implement targeted optimizations.