Database Maintenance - MySQL / MariaDB (Connect 2024)

Reorganize Database Indexes

The automatic clean-up functionality of Connect removes a significant number of records resulting in fragmentation of the corresponding database indexes. To address this issue, the following MySQL script should be scheduled for daily database maintenance:

OPTIMIZE TABLE ContextIndices;
OPTIMIZE TABLE ContextLogs;
OPTIMIZE TABLE Contexts;
OPTIMIZE TABLE PerformanceRecords;

To reorganize all Connect application-specific indexes, use the following MySQL script:

OPTIMIZE TABLE ConnectObjects;
OPTIMIZE TABLE ConnectObjectsHistory;
OPTIMIZE TABLE ContextIndices;
OPTIMIZE TABLE ContextLogs;
OPTIMIZE TABLE Contexts;
OPTIMIZE TABLE PerformanceRecords;
OPTIMIZE TABLE ScenarioRegistrations;
OPTIMIZE TABLE SecureStoreItems;

Delete Obsolete Data

If there is a need to delete all Contexts and Performance Records created before a specific date, the following script can be employed. Be aware that fragmented indexes may cause poor performance during deletion. If required, optimize these indexes prior to deleting data. Ensure to adjust the date in the first line of the script as needed:

SET @DeleteBefore = '2024-10-01 00:00:00';

DELETE c1 FROM Contexts c1 JOIN (SELECT id FROM Contexts WHERE ContextType = 'FlowContext' AND CreateTime < @DeleteBefore) c2 ON c1.FlowContextId = c2.id;
DELETE FROM ContextIndices WHERE FlowContextId IN (SELECT id FROM Contexts WHERE ContextType = 'FlowContext' AND CreateTime < @DeleteBefore);
DELETE FROM ContextLogs WHERE FlowContextId IN (SELECT id FROM Contexts WHERE ContextType = 'FlowContext' AND CreateTime < @DeleteBefore);
DELETE FROM Contexts WHERE ContextType = 'FlowContext' AND CreateTime < @DeleteBefore;
DELETE FROM PerformanceRecords WHERE Time < @DeleteBefore;

OPTIMIZE TABLE Contexts;
OPTIMIZE TABLE ContextIndices;
OPTIMIZE TABLE ContextLogs;
OPTIMIZE TABLE PerformanceRecords;