Following worked for me so that sharing for others use. Please do remember to take a full backup of the db using root
mysqldump -u root -p --single-transaction --routines --triggers astpp > /opt/astpp_backup_$(date +%Y%m%d_%H%M%S).sql
-- Pure Partitioning for ASTPP CDRs Table
-- Uses only callstart column - ignores reseller_id and other constraints
-- ============================================================================
-- PHASE 1: VERIFY CURRENT STRUCTURE
-- ============================================================================
-- Check current index (we'll ignore reseller_id for partitioning)
SHOW INDEX FROM cdrs;
-- Verify callstart data distribution
SELECT
YEAR(callstart) as year,
MONTH(callstart) as month,
COUNT(*) as record_count
FROM cdrs
GROUP BY YEAR(callstart), MONTH(callstart)
ORDER BY year DESC, month DESC;
-- ============================================================================
-- PHASE 2: PURE PARTITIONING (ONLY CALLSTART)
-- ============================================================================
-- Partition using only callstart column
-- This ignores reseller_id and other constraints completely
ALTER TABLE cdrs PARTITION BY RANGE (YEAR(callstart) * 100 + MONTH(callstart)) (
-- 2024 partitions (historical data)
PARTITION p2024_07 VALUES LESS THAN (202407),
PARTITION p2024_08 VALUES LESS THAN (202408),
PARTITION p2024_09 VALUES LESS THAN (202409),
PARTITION p2024_10 VALUES LESS THAN (202410),
PARTITION p2024_11 VALUES LESS THAN (202411),
PARTITION p2024_12 VALUES LESS THAN (202412),
-- 2025 partitions (current data)
PARTITION p2025_01 VALUES LESS THAN (202501),
PARTITION p2025_02 VALUES LESS THAN (202502),
PARTITION p2025_03 VALUES LESS THAN (202503),
PARTITION p2025_04 VALUES LESS THAN (202504),
PARTITION p2025_05 VALUES LESS THAN (202505),
PARTITION p2025_06 VALUES LESS THAN (202506),
PARTITION p2025_07 VALUES LESS THAN (202507),
PARTITION p2025_08 VALUES LESS THAN (202508),
PARTITION p2025_09 VALUES LESS THAN (202509),
PARTITION p2025_10 VALUES LESS THAN (202510),
PARTITION p2025_11 VALUES LESS THAN (202511),
PARTITION p2025_12 VALUES LESS THAN (202512),
-- Future partition for data beyond 2025
PARTITION p_future VALUES LESS THAN MAXVALUE
);
-- ============================================================================
-- PHASE 3: VERIFY PARTITIONING
-- ============================================================================
-- Check partition distribution
SELECT
PARTITION_NAME,
TABLE_ROWS,
ROUND(DATA_LENGTH/1024/1024, 2) as data_size_mb
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'astpp'
AND TABLE_NAME = 'cdrs'
ORDER BY PARTITION_ORDINAL_POSITION;
-- Verify data distribution across partitions
SELECT
PARTITION_NAME,
COUNT(*) as record_count
FROM cdrs
GROUP BY PARTITION_NAME
ORDER BY PARTITION_NAME;
-- ============================================================================
-- PHASE 4: TEST PARTITION PRUNING
-- ============================================================================
-- Test partition pruning for July 2025 (should only scan p2025_07)
EXPLAIN SELECT COUNT(*) FROM cdrs
WHERE callstart >= '2025-07-01' AND callstart < '2025-08-01';
-- Test partition pruning for August 2025 (should only scan p2025_08)
EXPLAIN SELECT COUNT(*) FROM cdrs
WHERE callstart >= '2025-08-01' AND callstart < '2025-09-01';
-- ============================================================================
-- PHASE 5: PERFORMANCE TESTING
-- ============================================================================
-- Test July 2025 query performance
SELECT
'July 2025' as period,
COUNT(*) as total_calls,
SUM(billseconds) as total_duration,
SUM(debit) as total_revenue
FROM cdrs
WHERE callstart >= '2025-07-01' AND callstart < '2025-08-01';
-- Test August 2025 query performance
SELECT
'August 2025' as period,
COUNT(*) as total_calls,
SUM(billseconds) as total_duration,
SUM(debit) as total_revenue
FROM cdrs
WHERE callstart >= '2025-08-01' AND callstart < '2025-09-01';
-- ============================================================================
-- PHASE 6: MONITORING
-- ============================================================================
-- Monitor partition sizes
SELECT
PARTITION_NAME,
TABLE_ROWS,
ROUND(DATA_LENGTH/1024/1024, 2) as data_size_mb,
ROUND(INDEX_LENGTH/1024/1024, 2) as index_size_mb
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'astpp'
AND TABLE_NAME = 'cdrs'
ORDER BY PARTITION_ORDINAL_POSITION;
-- Check partition pruning effectiveness
EXPLAIN SELECT COUNT(*) FROM cdrs
WHERE callstart >= '2025-07-01' AND callstart < '2025-08-01';
-- ============================================================================
-- PHASE 7: ROLLBACK (IF NEEDED)
-- ============================================================================
-- If partitioning causes issues, remove it (keeps data intact)
-- ALTER TABLE cdrs REMOVE PARTITIONING;
-- ============================================================================
-- USAGE EXAMPLES
-- ============================================================================
/*
-- Query specific date range (automatic partition pruning)
SELECT COUNT(*) FROM cdrs
WHERE callstart >= '2025-07-01' AND callstart < '2025-08-01';
-- Daily call counts for July 2025
SELECT
DATE(callstart) as call_date,
COUNT(*) as daily_calls,
SUM(billseconds) as total_bill_seconds,
SUM(debit) as total_debit
FROM cdrs
WHERE callstart >= '2025-07-01' AND callstart < '2025-08-01'
GROUP BY DATE(callstart)
ORDER BY call_date;
-- Account analysis for July 2025
SELECT
accountid,
COUNT(*) as call_count,
SUM(billseconds) as total_bill_seconds,
SUM(debit) as total_debit
FROM cdrs
WHERE callstart >= '2025-07-01' AND callstart < '2025-08-01'
GROUP BY accountid
ORDER BY call_count DESC
LIMIT 10;
*/