Performance Tweak - modular - openIMIS Nepal

1 1. Performance Tweak : Mutation Log
2 2. Performance tweak : Healthfacility page optimization

The performance issues in your mutation log system stem from using UUIDs as primary keys, which create fundamental database inefficiencies. Here's a comprehensive analysis and solution based on database engineering principles and the provided search results:

1. Performance Tweak : Mutation Log

Problem Analysis

Index Fragmentation
UUIDv4's randomness causes poor index locality, leading to expensive B+ tree rebalancing during inserts28. This manifests as:
- Slower insertion rates (15-30% degradation observed)3
- Increased page splits in indexes9
- 2x longer full table scans vs sequential IDs (1.2s vs 526ms in benchmarks)9
Storage Overhead
UUIDs (16 bytes) vs INT (4 bytes) creates:
- 4x larger primary key storage
- 75% more index pages required8
- Reduced cache efficiency (fewer records per memory page)3
Sorting Inefficiency
Non-sequential keys force expensive filesort operations for chronological queries:
sql SELECT * FROM core_Mutation_Log ORDER BY request_date_time DESC LIMIT 100
Requires full index scan despite date filter79

1. Switch to Integer Primary Key


python
class MutationLog(models.Model):
    id = models.BigAutoField(primary_key=True)  # 8-byte sequential key
    uuid = models.UUIDField(default=uuid.uuid4, editable=False, unique=True)
    # As it is fields...

Benefits:

50% reduction in index size58
Native chronological ordering via sequential inserts9
2-3x faster JOIN operations68

2. Add Composite Index for Temporal Queries


python
class Meta:
    indexes = [
        models.Index(fields=['-request_date_time', 'status'],
                     name='timestamp_status_idx')
    ]

Optimizes both dashboard queries and background workers accessing recent mutations.

3. Migration Strategy

Create new table with INT PK:
Batch copy data (500k records/batch):
Atomic switch during maintenance window:

Performance Expectations

Metric	UUID (v4)	INT PK	Improvement

Metric	UUID (v4)	INT PK	Improvement
Insert Throughput	2.3k ops/s	4.1k ops/s	78% faster`38`
Index Size	12GB	3GB	75% smaller`9`
ORDER BY date LIMIT 100	420ms	23ms	18x faster`9`

The following strategy works for better performance.

Mitigation strategy used in production server

Archive old mutationlog table
Create new mutationlog table

2. Performance tweak : Healthfacility page optimization

Before:

After:

The hospital name is sample for your convenience

Problem:

Query Parameter for fetching claims: healthFacility { id uuid name code }, joins to all nested health facility (parent and child related health-facility)

Solution:

The healthFacilityStr only returns name of the hospital required form the claim page.

Backend healthFacilityStr fix (string only):

Frontend changes: omit PublishedComponent and just put plain c.healthFacilityStr, in ClaimSearcher.js

Claim : actions.js

Result: This will avoid endless nested location call from landing pages which drastically improves openIMIS performance

openIMIS

Performance Tweak - modular - openIMIS Nepal

Analytics