I am running MariaDB 10.2.13 on Linux (Debian).
I am setting up an InnoDB table that will record a lot of data but I will keep only the last one-hour rows. Thus, the number of rows will remain constant.
I should expect on this table:
- many INSERTs
- many UPDATEs
- some DELETE (rows > 1 hour) from time to time
Table is defined with fixed-length fields only and some indexes.
CREATE TABLE `tbl_log` ( `ip` int(4) unsigned NOT NULL, `date` datetime NOT NULL, `external_id` smallint(6) unsigned NOT NULL, `counter` smallint(6) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (`ip`,`external_id`), KEY `external_id` (`external_id`), KEY `counter` (`counter`), KEY `date_idx` (`date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Inserts (and updates, through
on duplicate key) may look like this (ip and external_id will vary):
INSERT INTO tbl_log SET ip = INET_ATON('192.168.1.1'), date = now(), external_id = 123, counter = 0 ON DUPLICATE KEY UPDATE counter=counter+1;
Finally, deleting old rows will be done with a query:
DELETE FROM tbl_log WHERE date < DATE_SUB(NOW(), INTERVAL 1 HOUR);
Will such a table fragment over time?
If so, I think I should defragment it. If necessary, I planned to run
OPTIMIZE TABLE tbl_log; (with option
innodb-defragment=1) right after delete...