Hi Rob,

mysqlcheck -a analyses and reports, -o optimizes (which in innodb means writing all data out to a temporary table, then truncating and replacing the original. This can be slow and write-blocking whilst it’s underway).

Neither is destructive – they don’t delete or purge data.

There is no mariadb command to act upon a random database and purge old data, probably because mariadb itself does not record the age of rows (afaik)

The normal process for expiring aged data is to have a column in your table with a date (which can be automatically added at the time of insertion if specified in that table’s schema)

Then you would run something like;

“DELETE FROM TableName WHERE DateRowName < DATE_SUB(NOW(),INTERVAL 1 YEAR);”

Which would delete all rows older than a year based on the date in DateRowName.

Note: That may not release the disk space until an mysqlcheck -o is run, or the equivalent sql command “OPTIMIZE TABLE TableName;”. (See wider topics on innodb fragmentation)

From: robert k Wild via discuss <discuss@lists.mariadb.org>
Sent: Wednesday, June 28, 2023 7:52 PM
To: discuss@lists.mariadb.org
Subject: [MariaDB discuss] can you purge data older than 4 months old

hi all,

i was just wondering i know about the "mysqlcheck -a and -o" ie to optimize and to analyse ie to cut it down in size but NOT to purge any data

but is there like a purge option to delete all data older than 4 months old in the database

thanks,

rob

--

Regards,

Robert K Wild.