I think that this is a reasonable idea and not too hard to implement inside InnoDB.
However, I would prefer to introduce some distinguished syntax for this. OPTIMIZE TABLE can already mean a number of different things. Originally it maps to some storage engine API that InnoDB never implemented, and falls back to rebuilding the table by ALTER TABLE. MySQL 5.6 introduced online OPTIMIZE TABLE to InnoDB (and additional ALTER TABLE…FORCE syntax) in a minor release well after GA. MariaDB 10.1 had tied B-tree index defragmentation to the OPTIMIZE TABLE statement.
How about one of the following:
OPTIMIZE TABLE foo INDEXES;
|
ALTER TABLE foo REBUILD INDEXES;
|
ALTER TABLE foo OPTIMIZE INDEXES;
|
Note: Normal ALTER TABLE foo ENGINE=InnoDB (just like ALTER TABLE foo FORCE) is a table-rebuilding operation, and it can be performed online (LOCK=NONE) except when certain limitations apply. So, this feature request is to have some syntax to skip rebuilding the clustered index, and only rebuild the secondary indexes.
Also note that MySQL 5.6 and 5.7 (and I assume also MariaDB 10.x) will optimize away a statement like ALTER TABLE foo DROP INDEX col, ADD INDEX(col) if the metadata does not change. So, you cannot use that to request indexes to be rebuilt.
Actually ALTER TABLE foo row_format = .... works already 
but you need to know.
aadant, doesn’t ALTER TABLE foo ROW_FORMAT=… rebuild the clustered index as well? I understood that this request is specifically about rebuilding the secondary indexes while leaving the clustered index alone.
On a related note, MySQL 5.7 (and MariaDB 10.2.2) skip the merge sort for the clustered index if the ordering does not change. But the clustered index would still be fully rebuilt.
@Marko Mäkelä : this request is to make sure that the size on disk is "optimized". Curiously optimize table does not do that, it rebuilds the table but without the fast index creation method for secondary indices. alter table ... row_format does the trick but most people do not know. Why not map optimize table to alter table row_format ?
aadant, I was confused by the mention of secondary indexes in the description. A table rebuild (rebuilding the clustered index) can happen online, but it will obviously consume more space than merely dropping and creating the secondary indexes, because a new copy of all data will be made. Actually, if the desire is to save disk space, then rebuilding the secondary indexes could have an undesired effect of causing the data files to contain lots of freed pages.
If we ignore the request about secondary indexes, this sounds like a duplicate of MDEV-10453.
An implementation of
ALTER TABLE foo REBUILD INDEXES; |
should be possible without any InnoDB code changes. Basically, the SQL layer would fill in the information for invoking handler::check_if_supported_inplace_alter() and friends, asking for each and every secondary index to be dropped and added. If the storage engine cannot support that, then it would fall back to ALGORITHM=COPY, as usual.
In discussion we decided that it should be ALTER TABLE foo REBUILD INDEXES;
SQL layer implementation is clear - drop and recreate.
Engine can optimize the process when see list for drop and creation of indexes
1. Why would one want to rebuild indexes?
2. falling back to ALGORITHM=COPY is an overkill, there is generally no need to copy the data just to rebuild indexes
- Secondary indexes in InnoDB may contain garbage records due to some earlier bug, such as
MDEV-14799. That garbage would be invisible to SQL, but wasting of CPU and storage. - InnoDB never supported native creation of multiple FULLTEXT INDEX in one statement. If an InnoDB table contained multiple FULLTEXT INDEX, the ALTER TABLE foo REBUILD INDEXES would fall back to ALGORITHM=COPY.
An intermediate fallback step could be to invoke DISABLE KEYS and ENABLE KEYS for engines that support it, if the ENABLE KEYS involves rebuilding the indexes.