Table and event code to track and record mysql table statistics

Track MySQL table stats

DROP TABLE IF EXISTS stats_history;

-- Create table
CREATE TABLE `stats_history` (`id` bigint(20) NOT NULL AUTO_INCREMENT,
`runTime` datetime DEFAULT NULL,
`TABLE_SCHEMA` varchar(192) NOT NULL DEFAULT '',
`TABLE_NAME` varchar(192) NOT NULL DEFAULT '',
`ROWS_READ` bigint(21) unsigned NOT NULL DEFAULT '0',
`ROWS_CHANGED` bigint(21) unsigned NOT NULL DEFAULT '0',
`ROWS_CHANGED_X_INDEXES` bigint(21) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_runtime` (`runTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP EVENT IF EXISTS test.collect_stats;

-- Create event
CREATE EVENT test.collect_stats 
ON SCHEDULE EVERY 5 MINUTE STARTS CURRENT_TIMESTAMP
ON COMPLETION PRESERVE 
DO 
INSERT INTO test.stats_history select null, now(), s.* 
FROM information_schema.table_statistics s;

-- Query row delta over time
set @rcd = 0;
select runTime, 
table_name, 
rows_changed - @rcd as RowsChanged, 
@rcd:=rows_changed as PreviousValue 
FROM stats_history 
where table_schema = '' 
and table_name = '';

-- Stored procedure to query stats table
DROP PROCEDURE IF EXISTS getStats;
DELIMITER //
CREATE PROCEDURE getStats(db VARCHAR(25), dtStart DATETIME, dtEnd DATETIME, tab VARCHAR(100))
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE tabname VARCHAR(100);
DECLARE tbl VARCHAR(100);

DECLARE cur1 CURSOR FOR SELECT DISTINCT table_name FROM test.stats_history WHERE table_schema = db AND table_name LIKE  tab AND runTime BETWEEN dtStart AND dtEnd;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO tabname;
IF done THEN
LEAVE read_loop;
END IF;
set @rcd = 0;
SET @rrd = 0;
select runTime, 
table_name, 
rows_changed - @rcd as RowsChanged, 
rows_read - @rrd as RowsRead,
@rcd:=rows_changed as PreviousRowsChanged,
@rrd:=rows_read as PreviousRowsRead
FROM stats_history 
where table_schema = db
and table_name = tabname
AND runTime BETWEEN dtStart AND dtEnd
;
END LOOP;

CLOSE cur1;
END//
DELIMITER ;