Database Optimization Techniques

1. Database Indexing

Database indexing is perhaps the most powerful technique for improving query performance. Indexes work similarly to a book's index, allowing the database engine to find data without scanning the entire table.

1.1 Understanding Index Types

Different database systems offer various types of indexes, each with specific use cases:

• B-Tree Indexes: The most common type, ideal for equality and range queries.
• Hash Indexes: Optimized for equality comparisons but not for range queries.
• Full-Text Indexes: Designed for text search operations across large text fields.
• Spatial Indexes: Used for geographical data and location-based queries.
• Composite Indexes: Created on multiple columns, useful for queries that filter on several fields.

1.2 Index Creation Best Practices

Creating effective indexes requires careful consideration of your query patterns:

1.3 Creating Indexes in SQL

Here are examples of creating different types of indexes in SQL:

-- Simple index on a single column
CREATE INDEX idx_user_email ON users(email);

-- Composite index on multiple columns
CREATE INDEX idx_product_category_price ON products(category_id, price);

-- Unique index to enforce data integrity
CREATE UNIQUE INDEX idx_unique_username ON users(username);
-- Full-text index for text searching
CREATE FULLTEXT INDEX idx_article_content ON articles(title, content);

2. Query Optimization

Even with proper indexing, poorly written queries can still perform badly. Query optimization involves restructuring your SQL statements to make them more efficient.

2.1 Understanding Query Execution Plans

The first step in optimizing queries is understanding how the database executes them. Most database systems provide tools to view execution plans:

-- MySQL/MariaDB
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

-- SQL Server
SET SHOWPLAN_ALL ON;
GO
SELECT * FROM orders WHERE customer_id = 123;
GO
SET SHOWPLAN_ALL OFF;

2.2 Common Query Optimization Techniques

// Prepare once
$stmt = $pdo->prepare('SELECT * FROM products WHERE category_id = ? AND price < ?');
// Execute multiple times with different values
$stmt->execute([1, 100]);
$cheapElectronics = $stmt->fetchAll();

$stmt->execute([2, 50]);
$cheapClothing = $stmt->fetchAll();

3. Caching Strategies

Caching reduces database load by storing frequently accessed data in memory, providing much faster access times compared to disk-based database operations.

3.1 Database Query Cache

Many database systems include built-in query caching mechanisms that store the results of frequently executed queries:

-- MySQL query cache configuration
SET GLOBAL query_cache_size = 67108864; -- 64MB
SET GLOBAL query_cache_type = 1; -- ON

-- Check cache status
SHOW STATUS LIKE 'Qcache%';

3.2 Application-Level Caching

Implementing caching at the application level gives you more control over what gets cached and for how long:

function getProductDetails($productId) {
    $redis = new Redis();
    $redis->connect('127.0.0.1', 6379);
    
    // Try to get from cache first
    $cacheKey = "product:$productId";
    $cachedResult = $redis->get($cacheKey);
    
    if ($cachedResult) {
        return json_decode($cachedResult, true);
    }
    
    // If not in cache, query the database
    $db = new PDO('mysql:host=localhost;dbname=shop', 'username', 'password');
    $stmt = $db->prepare('SELECT * FROM products WHERE product_id = ?');
    $stmt->execute([$productId]);
    $product = $stmt->fetch(PDO::FETCH_ASSOC);
    
    // Store in cache for future requests (expire after 1 hour)
    $redis->setex($cacheKey, 3600, json_encode($product));
    
    return $product;
}

import pymemcache
import json
import pymysql

def get_user_profile(user_id):
    # Connect to memcached
    client = pymemcache.client.base.Client(('localhost', 11211))
    
    # Try to get from cache
    cache_key = f'user_profile:{user_id}'
    cached_data = client.get(cache_key)
    
    if cached_data:
        return json.loads(cached_data.decode('utf-8'))
    
    # If not in cache, query database
    connection = pymysql.connect(
        host='localhost',
        user='username',
        password='password',
        database='users_db'
    )
    
    try:
        with connection.cursor(pymysql.cursors.DictCursor) as cursor:
            sql = 'SELECT * FROM users WHERE user_id = %s'
            cursor.execute(sql, (user_id,))
            user_data = cursor.fetchone()
            
            # Store in cache (expire after 15 minutes)
            if user_data:
                client.set(cache_key, json.dumps(user_data).encode('utf-8'), expire=900)
                
            return user_data
    finally:
        connection.close()

3.3 Cache Invalidation Strategies

Keeping cached data in sync with the database is a critical challenge. Here are common strategies:

4. Regular Maintenance Procedures

Regular database maintenance is essential for long-term performance and reliability. Implementing a consistent maintenance schedule helps prevent performance degradation over time.

4.1 Database Statistics Update

Database query optimizers rely on statistics about your data to create efficient execution plans. Regularly updating these statistics helps maintain optimal query performance:

-- MySQL/MariaDB
ANALYZE TABLE customers, orders, products;

-- PostgreSQL
ANALYZE VERBOSE customers;
ANALYZE VERBOSE orders;
ANALYZE VERBOSE products;

-- SQL Server
UPDATE STATISTICS dbo.customers;
UPDATE STATISTICS dbo.orders;
UPDATE STATISTICS dbo.products;

4.2 Table Optimization and Defragmentation

Over time, database tables can become fragmented, leading to wasted space and decreased performance. Regular optimization helps reclaim space and improve access efficiency:

-- MySQL/MariaDB
OPTIMIZE TABLE customers, orders, products;
-- PostgreSQL
VACUUM FULL ANALYZE customers;
VACUUM FULL ANALYZE orders;
VACUUM FULL ANALYZE products;

-- SQL Server
ALTER INDEX ALL ON dbo.customers REORGANIZE;
ALTER INDEX ALL ON dbo.orders REORGANIZE;
ALTER INDEX ALL ON dbo.products REORGANIZE;

4.3 Identifying and Removing Redundant Indexes

Redundant indexes waste space and slow down write operations without providing performance benefits. Regularly review and remove unnecessary indexes:

SELECT 
    t.TABLE_NAME, 
    i1.INDEX_NAME AS 'Index1',
    i1.COLUMN_NAME AS 'Column1',
    i2.INDEX_NAME AS 'Index2',
    i2.COLUMN_NAME AS 'Column2'
FROM 
    information_schema.STATISTICS i1
JOIN 
    information_schema.STATISTICS i2 
    ON i1.TABLE_SCHEMA = i2.TABLE_SCHEMA
    AND i1.TABLE_NAME = i2.TABLE_NAME
    AND i1.SEQ_IN_INDEX = i2.SEQ_IN_INDEX
    AND i1.COLUMN_NAME = i2.COLUMN_NAME
JOIN 
    information_schema.TABLES t 
    ON i1.TABLE_SCHEMA = t.TABLE_SCHEMA
    AND i1.TABLE_NAME = t.TABLE_NAME
WHERE 
    i1.INDEX_NAME != i2.INDEX_NAME
    AND t.TABLE_SCHEMA = 'your_database_name'
ORDER BY 
    t.TABLE_NAME, i1.INDEX_NAME, i1.SEQ_IN_INDEX;

4.4 Database Backup Strategy

While not directly related to performance, a robust backup strategy is a critical part of database maintenance:

5. Performance Monitoring

Continuous monitoring is essential for identifying performance issues before they impact users. Implementing a robust monitoring system helps you track database health and performance trends over time.

5.1 Key Performance Metrics

Focus on these critical metrics when monitoring database performance:

5.2 Slow Query Logging

Identifying and optimizing slow queries is one of the most effective ways to improve database performance:

-- MySQL/MariaDB: Enable slow query logging
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 1; -- Log queries that take more than 1 second

-- PostgreSQL: Enable slow query logging
ALTER SYSTEM SET log_min_duration_statement = 1000; -- 1000ms = 1 second
SELECT pg_reload_conf();

# Show the top 10 slowest queries
mysqldumpslow -t 10 /var/log/mysql/mysql-slow.log

# Show queries with the most impact (time × executions)
mysqldumpslow -t 10 -s t /var/log/mysql/mysql-slow.log
# Show queries that scan the most rows
mysqldumpslow -t 10 -s r /var/log/mysql/mysql-slow.log

5.3 Monitoring Tools

Several tools can help you monitor database performance: