56 lines
2.2 KiB
SQL
56 lines
2.2 KiB
SQL
-- BDS report performance indexes
|
|
-- Run manually on the application database (e.g. nhance_live).
|
|
-- Safe to re-run: each statement checks information_schema before creating.
|
|
|
|
-- policy_transaction: default 90-day filter + active flag
|
|
SET @idx := (
|
|
SELECT COUNT(1) FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'policy_transaction'
|
|
AND index_name = 'idx_pt_active_created'
|
|
);
|
|
SET @sql := IF(@idx = 0,
|
|
'CREATE INDEX idx_pt_active_created ON policy_transaction (is_active, created_at)',
|
|
'SELECT ''idx_pt_active_created already exists'' AS info'
|
|
);
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- pt_co_share_details: join from policy_transaction
|
|
SET @idx := (
|
|
SELECT COUNT(1) FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'pt_co_share_details'
|
|
AND index_name = 'idx_pcsd_pt_active'
|
|
);
|
|
SET @sql := IF(@idx = 0,
|
|
'CREATE INDEX idx_pcsd_pt_active ON pt_co_share_details (pt_id, is_active)',
|
|
'SELECT ''idx_pcsd_pt_active already exists'' AS info'
|
|
);
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- co_share_stmt_details: billed/reward aggregate joins
|
|
SET @idx := (
|
|
SELECT COUNT(1) FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'co_share_stmt_details'
|
|
AND index_name = 'idx_cssd_coshare_stmt_active'
|
|
);
|
|
SET @sql := IF(@idx = 0,
|
|
'CREATE INDEX idx_cssd_coshare_stmt_active ON co_share_stmt_details (co_share_id, statement_id, is_active)',
|
|
'SELECT ''idx_cssd_coshare_stmt_active already exists'' AS info'
|
|
);
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- insurer_statements: month + invoice filters used by aggregates
|
|
SET @idx := (
|
|
SELECT COUNT(1) FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'insurer_statements'
|
|
AND index_name = 'idx_insq_active_month_invoice'
|
|
);
|
|
SET @sql := IF(@idx = 0,
|
|
'CREATE INDEX idx_insq_active_month_invoice ON insurer_statements (is_active, month, invoice_status)',
|
|
'SELECT ''idx_insq_active_month_invoice already exists'' AS info'
|
|
);
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|