Since innodb_fle_per_table is OFF, you cannot see the table and measure it progress.
I made an earlier post about how to do this for MyISAM. You can do this for InnoDB if and only if innodb_file_per_table was enabled and you rearchitect the InnoDB infrastructure. It still requires looking in the operating system at the sizes of the files in question.
Once you have fully implemented the InnoDB Cleanup and you have enabled innodb_file_per_table, you may want to perform the index updating as follows:
EXAMPLE you have the following
- MySQL Instance with /var/lib/mysql as datadir
- InnoDB table called
db.lotsofdata with 20 million names:
The table looks like this:
CREATE TABLE db.lotsofdata
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
Suppose you want to create a name index. You can do this:
CREATE TABLE db.lotsofdata_new LIKE db.lotsofdata;
ALTER TABLE db.lotsofdata_new ADD INDEX (name);
INSERT INTO db.lotsofdata_new SELECT * db.lotsofdata;
ALTER TABLE db.lotsofdata RENAME db.lotsofdata_old;
ALTER TABLE db.lotsofdata_new RENAME db.lotsofdata;
TRUNCATE TABLE db.lotsofdata_old;
ALTER TABLE db.lotsofdata_old ENGINE=InnoDB;
DROP TABLE db.lotsofdata_old;
While the INSERT is running, you go in the operating system and run this:
watch ls -l lotsofda*.ibd
This will give you a listing of the current size of
lotsofdata_new.ibd. When it gets bigger than
lotsofdata.ibd, then you know you are close to completion.
BTW MariaDB has a Progress Status implemented internally.