Tiens, voici quelques commandes que j’avais noté pour travailler sur mon archivage
select table_name as `table` , round(((data_length+index_length)/1024/1024),2) as mb, table_rows as ligne from information_schema.tables where table_schema = 'jeedom' order by (data_length+index_length) desc
Pour connaitre les commandes les plus gourmandes dans HystoryArch
SELECT id, name, COUNT(historyArch.cmd_id) AS nbEnregistrements FROM cmd LEFT JOIN historyArch ON historyArch.cmd_id = cmd.id GROUP BY historyArch.cmd_id ORDER BY nbEnregistrements DESC LIMIT 50
Et connaitre le délai rétention:
SELECT id, name, JSON_VALUE(configuration, '$.historyPurge') as histo FROM cmd WHERE configuration LIKE "%historyPurge%" ORDER BY histo DESC LIMIT 50
Optimiser la taille d'une table:
ALTER TABLE jeedom.historyArch ENGINE=MyISAM;
OPTIMIZE TABLE jeedom.historyArch;
ALTER TABLE jeedom.historyArch ENGINE=InnoDB;