MySQL reclaim index space after large delete?

Question

After performing a large delete in MySQL, I understand you need to run a NULL ALTER to reclaim disk space, is this also true for reclaiming index space?

We have tables using > 10G of index space and have deleted/archived large chunks of this data and unsure if we need to rebuild the table in order to decrease the size of the index.

Can anyone offer any advice?

We are trying to avoid rebuilding the table since it would take quite awhile and lock the table.

Thanks!

You don't "need" to... unless you "need" the space. It will eventually be reused. — Michael - sqlbot, Oct 23 '13 at 01:05

score 3 · Answer 1 · answered Oct 22 '13 at 21:45

You're using InnoDB. Unfortunately, in its default configuration, InnoDB never gives back disk space unless you are using innodb_file_per_table, in which case disk space can be re-claimed by running optimize table foo.

If you are not using innodb_file_per_table, then you need to do the following:

Use mysqldump to dump your databases to a file.
Drop your databases.
Stop mysql.
Delete your innodb files (or move them elsewhere).
Start up mysql.
Re-create your databases and re-import from the mysqldump file.

Needless to say, you had better make sure you have a very good backup before doing this. Also, strongly consider enabling innodb_file_per_table while you have mysql stopped.

score 0 · Answer 2 · edited Mar 03 '21 at 18:17

I have this approach.

Change name table to tmp:

rename table pppoe_auth_users to pppoe_auth_users_tmp;

Disable keys:

ALTER TABLE pppoe_auth_users_tmp DISABLE KEYS;

Recreate table:

CREATE TABLE `pppoe_auth_users` (
  `host` varchar(128) CHARACTER SET latin1 DEFAULT NULL,
  `username` varchar(128) CHARACTER SET latin1 DEFAULT NULL,
  `vlan` int(20) DEFAULT NULL,
  `ipaddr` varchar(128) CHARACTER SET latin1 DEFAULT NULL,
  `interface` varchar(256) CHARACTER SET latin1 DEFAULT NULL,
  `qos_in_enable` int(4) DEFAULT NULL,
  `qos_out_enable` int(4) DEFAULT NULL,
  `bw_in` bigint(20) DEFAULT NULL,
  `bw_out` bigint(20) DEFAULT NULL,
  `uptime` int(20) DEFAULT '0',
  `clock` int(20) DEFAULT '0',
  KEY `t1` (`host`,`clock`,`ipaddr`),
  KEY `t2` (`host`,`clock`,`username`),
  KEY `auth_users` (`clock`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;*

Disable keys:

ALTER TABLE pppoe_auth_users DISABLE KEYS;

Insert data with time slice needed in this case last 60 days:

insert into pppoe_auth_users (select * from pppoe_auth_users_tmp where clock>=(unix_timestamp()-(86400*60)));

Enable keys:

ALTER TABLE pppoe_auth_users ENABLE KEYS;

Drop if needed old data:
```
drop table pppoe_auth_users_tmp;
```

MySQL reclaim index space after large delete?

2 Answers2