Question: For example, I issued an ALTER TABLE statement to create an index on a MEDIUMTEXT field in an InnoDB table that has 134k rows where the size of the index was 255 bytes and the average size of the data in the field is 30k. This command has been running for the last 15 minutes or so (and is the only thing running on the database). Is there any way for me to determine if it is going to finish in closer to 5 minutes, 5 hours, or 5 days?

Answer: 
I made a query that estimates the time to finish an alter command on an innodb table. You have to run it at least twice on the same session since it compares stats from consecutive runs to make the estimatation.
Don't forget to change *<tableName>* to the correct table name on the fourth line. It gives you two estimations. Local estimation uses only data between runs while Global estimation uses the entire transaction time.

```
select 
beginsd, now(), qRuns, qTime, tName, trxStarted, trxTime, `rows`, modified, locked, hoursLeftL, estimatedEndL, modifiedPerSecL, avgRows, estimatedEndG, modifiedPerSecG, hoursLeftG
from (
select 
    (@tname:='<table>') tName,
    @beginsd:=sysdate() beginsd,
    @trxStarted:=(select trx_started from information_schema.innodb_trx where trx_query like concat('alter table %', @tname, '%')) trxStarted, 
    @trxTime:=timediff(@beginsd, @trxStarted) trxTime,
    @rows:=(select table_rows from information_schema.tables where table_name like @tname) `rows`,
    @runs:=(ifnull(@runs, 0)+1) qRuns,
    @rowsSum:=(ifnull(@rowsSum, 0)+@rows),
    round(@avgRows:=(@rowsSum / @runs)) avgRows,
    @modified:=(select trx_rows_modified from information_schema.innodb_trx where trx_query like concat('alter table %', @tname, '%')) modified, 
    @rowsLeftL:=(cast(@rows as signed) - cast(@modified as signed)) rowsLeftL,
    round(@rowsLeftG:=(cast(@avgRows as signed) - cast(@modified as signed)), 2) rowsLeftG,
    @locked:=(select trx_rows_locked from information_schema.innodb_trx where trx_query like concat('alter table %', @tname, '%')) locked,
    @endsd:=sysdate() endsd,
    --
    time_to_sec(timediff(@endsd, @beginsd)) qTime,
    @modifiedInc:=(cast(@modified as signed) - cast(@p_modified as signed)) modifiedInc,
    @timeInc:=time_to_sec(timediff(@beginsd, @p_beginsd)) timeInc,
    round(@modifiedPerSecL:=(@modifiedInc/@timeInc)) modifiedPerSecL,
    round(@modifiedPerSecG:=(@modified/time_to_sec(@trxTime))) modifiedPerSecG,
    round(@minutesLeftL := (@rowsLeftL / @modifiedPerSecL / 60)) minutesLeftL,
    round(@minutesLeftG := (@rowsLeftG / @modifiedPerSecG / 60)) minutesLeftG,
    round(@hoursLeftL := (@minutesLeftL / 60), 2) hoursLeftL,
    round(@hoursLeftG := (@minutesLeftG / 60), 2) hoursLeftG,
    (@beginsd + INTERVAL @minutesLeftL MINUTE) estimatedEndL,
    (@beginsd + INTERVAL @minutesLeftG MINUTE) estimatedEndG,
    --
    @p_rows:=@rows,
    @p_modified:=@modified,
    @p_beginsd:=@beginsd
) sq;

```
[Percona Server](http://www.percona.com/software/percona-server), which is a branched version of MySQL with some enhancements, has this feature.

You can observe extra columns in SHOW PROCESSLIST for ROWS\_SENT and ROWS\_EXAMINED. For example, if your table has 1000000 rows, and you see ROWS\_EXAMINED of 650000, then it's 65% finished.

See <http://www.percona.com/doc/percona-server/5.6/diagnostics/process_list.html>