Skip to main content

Database Performance Tuning

Optimization strategies for Netasampark database performance.

Indexing Strategy​

Primary Indexes​

All tables have primary keys on id column.

Foreign Key Indexes​

-- Automatically created for foreign keys
CREATE INDEX idx_user_id ON voters(user_id);
CREATE INDEX idx_campaign_id ON messages(campaign_id);

Composite Indexes​

-- For common query patterns
CREATE INDEX idx_status_created ON messages(status, created_at);
CREATE INDEX idx_category_priority ON grievances(category, priority);
CREATE INDEX idx_politician_status ON campaigns(politician_id, status);

Covering Indexes​

-- Include frequently selected columns
CREATE INDEX idx_voter_lookup ON voters(phone, name, constituency);

Query Optimization​

Use EXPLAIN​

EXPLAIN SELECT * FROM voters WHERE phone = '1234567890';

Avoid SELECT *​

-- Bad
SELECT * FROM voters;

-- Good
SELECT id, name, phone, constituency FROM voters;

Use LIMIT​

-- For pagination
SELECT * FROM messages 
WHERE status = 'pending' 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 0;

Index Usage​

-- Ensure indexes are used
SELECT * FROM voters 
WHERE phone = '1234567890'  -- Uses index
AND constituency = 'ABC';   -- Uses index if composite index exists

Connection Pooling​

Configuration​

[mysqld]
max_connections=200
thread_cache_size=50
table_open_cache=4000

Laravel Configuration​

// config/database.php
'mysql' => [
    'options' => [
        PDO::ATTR_PERSISTENT => true,
    ],
],

Caching Strategy​

Query Caching​

// Cache expensive queries
$voters = Cache::remember('voters.active', 3600, function () {
    return Voter::where('status', 'active')->get();
});

Redis Caching​

// Use Redis for frequently accessed data
Cache::store('redis')->put('campaign.active', $campaign, 3600);

Table Optimization​

Analyze Tables​

ANALYZE TABLE voters;
ANALYZE TABLE messages;
ANALYZE TABLE campaigns;

Optimize Tables​

OPTIMIZE TABLE voters;
OPTIMIZE TABLE messages;

Check Table Status​

SHOW TABLE STATUS LIKE 'voters';

Partitioning​

Range Partitioning​

CREATE TABLE messages_partitioned (
    id BIGINT UNSIGNED,
    -- columns
    created_at TIMESTAMP
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Archiving Old Data​

Archive Strategy​

-- Move old messages to archive
INSERT INTO messages_archive 
SELECT * FROM messages 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- Delete archived messages
DELETE FROM messages 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

Monitoring​

Slow Query Log​

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

Performance Schema​

-- Enable performance schema
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 10;

Best Practices​

  1. Index Strategically: Not too many, not too few
  2. Monitor Queries: Use slow query log
  3. Regular Maintenance: ANALYZE and OPTIMIZE regularly
  4. Archive Old Data: Keep tables lean
  5. Use Connection Pooling: Reduce connection overhead
  6. Cache Aggressively: Cache frequently accessed data
  7. Partition Large Tables: Improve query performance

Performance Metrics​

Key Metrics to Monitor​

  • Query execution time
  • Number of slow queries
  • Index usage
  • Table size
  • Connection count
  • Cache hit ratio

Tools​

  • MySQL Workbench
  • phpMyAdmin
  • Percona Monitoring
  • New Relic
  • Datadog