How to Optimize Database-Heavy Applications

How to Optimize Database-Heavy Applications

Database queries are the most common performance bottleneck in web applications. When your Breeze-hosted application spends most of its response time waiting on the database, systematic optimization of queries, indexes, and access patterns can reduce page load times by orders of magnitude.

Step 1: Identify Slow Queries

Enable the MySQL slow query log to find problematic queries:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1

sudo systemctl restart mysql

Analyze the slow query log with mysqldumpslow:

# Show top 10 slowest queries by average time
sudo mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

# Show queries sorted by count (most frequent slow queries)
sudo mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

Step 2: Analyze Query Execution Plans

Use EXPLAIN to understand how MySQL executes a query:

EXPLAIN SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 20;

Key things to look for in the EXPLAIN output:

• type: ALL — full table scan; needs an index
• rows — high row counts indicate inefficient scans
• Using temporary; Using filesort — extra disk operations that slow the query
• key: NULL — no index is being used

Step 3: Add Strategic Indexes

Create indexes based on your query patterns:

-- Index for WHERE clauses
CREATE INDEX idx_users_status ON users(status);

-- Composite index for JOIN + WHERE
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Covering index (includes all columns the query needs)
CREATE INDEX idx_orders_covering ON orders(user_id, status, created_at, total);

-- Index for ORDER BY
CREATE INDEX idx_orders_created ON orders(created_at DESC);

Check existing indexes before adding new ones:

SHOW INDEX FROM orders;

Step 4: Optimize Query Patterns

Rewrite common anti-patterns:

-- BAD: SELECT * fetches unnecessary columns
SELECT * FROM orders WHERE user_id = 123;

-- GOOD: Select only needed columns
SELECT id, total, created_at FROM orders WHERE user_id = 123;

-- BAD: N+1 queries in a loop
-- PHP: foreach ($users as $u) { $orders = query("SELECT * FROM orders WHERE user_id = ?", [$u['id']]); }

-- GOOD: Single JOIN query
SELECT u.*, o.id AS order_id, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active';

-- BAD: Using functions on indexed columns
SELECT * FROM orders WHERE YEAR(created_at) = 2025;

-- GOOD: Use range comparison
SELECT * FROM orders WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

-- BAD: LIKE with leading wildcard
SELECT * FROM products WHERE name LIKE '%widget%';

-- GOOD: Use FULLTEXT index
ALTER TABLE products ADD FULLTEXT(name);
SELECT * FROM products WHERE MATCH(name) AGAINST('widget');

Step 5: Tune MySQL Configuration

Adjust MySQL buffer and cache settings for your workload:

[mysqld]
# InnoDB buffer pool - set to 70-80% of available RAM for dedicated DB servers
innodb_buffer_pool_size = 2G

# Log file size - larger values improve write performance
innodb_log_file_size = 512M

# Flush method for better I/O on Linux
innodb_flush_method = O_DIRECT

# Query cache (deprecated in MySQL 8, use ProxySQL or application cache instead)
# For MariaDB:
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M

# Connection handling
max_connections = 200
thread_cache_size = 16

# Temp table sizes
tmp_table_size = 64M
max_heap_table_size = 64M

Step 6: Implement Application-Level Caching

Cache frequently accessed query results to reduce database load:

// PHP example with Redis
$redis = new Redis();
$redis->connect('127.0.0.1');

$cacheKey = "user_orders:" . $userId;
$orders = $redis->get($cacheKey);

if ($orders === false) {
    $orders = $db->fetchAll("SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC", [$userId]);
    $redis->setex($cacheKey, 300, serialize($orders)); // Cache for 5 minutes
} else {
    $orders = unserialize($orders);
}

Monitoring and Continuous Improvement

• Monitor query performance — use SHOW PROCESSLIST and SHOW STATUS regularly
• Track index usage — query sys.schema_unused_indexes to find and remove unused indexes
• Set up alerts — notify when slow query count exceeds a threshold
• Load test — simulate production traffic to find bottlenecks before they affect users
• Review periodically — as data grows, queries that were fast may become slow and need re-optimization