Scenario

When onboarding new users, the system initializes account balances. Existing accounts require balance updates, while new accounts are created. All transactions are recorded in a detailed log.

The objective is to insert new user data by computing the set difference between input records and existing system data.

Performance Issue

Testing with 300,000+ simulated records on limited local hardware revealed excessive latency in the insertion query, raising production environment concerns.

Initial query structure:

INSERT INTO user_credit_log (
    log_id,
    source_id,
    credit_type,
    event_code,
    credit_amount,
    operation_flag,
    user_identifier,
    validity_category,
    start_date,
    end_date,
    transaction_time,
    operator,
    data_origin,
    entry_type,
    notes,
    deletion_flag
)
SELECT
    CONCAT(DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), CAST(FLOOR(RAND() * 10000000) AS CHAR)),
    CONCAT(DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), CAST(FLOOR(RAND() * 10000000) AS CHAR)),
    4,
    'REG_20210530',
    1,
    1,
    u.user_id,
    1,
    CURDATE(),
    DATE_ADD(CURDATE(), INTERVAL 1 YEAR),
    NOW(),
    'SYS_INIT',
    'BATCH_LOAD',
    'B',
    'New user registration bonus',
    0
FROM new_user_batch AS u
WHERE NOT EXISTS (
    SELECT 1
    FROM user_credit_summary AS s
    WHERE s.user_identifier = u.user_id AND s.credit_type = 4
)
GROUP BY u.user_id;

Anaylsis

Execution analysis showed the NOT EXISTS subquery caused full index scans on user_credit_summary. While indexes were utilized, they performed exhaustive scans rather than targeted lookups. Profiling identified the Sending Data phase as the primary bottleneck.

Profiling Methodology

1. Monitor active threads: SHOW FULL PROCESSLIST;
2. Execute target query
3. Review query history: SHOW PROFILES;
4. Analyze performance breakdown: SHOW PROFILE ALL FOR QUERY <ID>;

Sending Data Phase Breakdown

1. Data Collection: Index-based retrieval followed by primary key lookups for non-indexed columns
2. Data Transmission: Transferring result sets to client

Findings

Primary contributors to latency:

• Index scans requiring secondary lookups
• High-volume data transfer

Resolution Decision

No optimization implemented due to:

• Query already leveraging indexes effectively
• One-time initialization script nature with inherent large-data processing
• Superior production hardware (16-core/32GB RAM)
• Off-peak executoin scheduling

Outcome

Production execution completed in ~20 seconds versus ~35 seconds locally.