Mysql – Why would I ever prefer ALGORITHM=COPY to ALGORITHM=INPLACE

alter-tableddlMySQLonline-operations

Since MySQL 5.6 introduced online DDL, the ALTER TABLE command can optionally have either ALGORITHM=INPLACE or ALGORITHM=COPY specified. The overview of online DDL notes that, by default, INPLACE is used wherever possible, and implies (without ever quite stating it) that the INPLACE algorithm is cheaper than the COPY one is.

So what reason would I ever have to specify ALGORITHM=COPY on an ALTER TABLE statement?

Best Answer

Yes, there are cases when you may specify COPY, but it would be for other reasons than performance.

It is important to understand that MySQL introduced new feature - Online DLL processing in version 5.6. It did not remove offline processing. So there is a need to differentiate between these 2 modes:

Some operations still work in Offline mode only. See Table 15.10, “Summary of Online Status for DDL Operations” for a list of the DDL operations that can or cannot be performed in-place.
Operations in Online and Offline modes have slightly different behavior, so you can choose "old" one for compatibility reasons.

Some examples (please suggest more):

InnoDB tables created before MySQL 5.6 do not support ALTER TABLE ... ALGORITHM=INPLACE for tables that include temporal columns (DATE, DATETIME or TIMESTAMP) and have not been rebuilt using ALTER TABLE ... ALGORITHM=COPY. In this case, an ALTER TABLE ... ALGORITHM=INPLACE operation returns error.
ADD PRIMARY KEY clause in COPY mode silently converts NULL to default values for that data type (0 for INT, empty string for varchar), whereas IN_PLACE does not do that.

With the ALGORITHM=COPY clause, the operation succeeds despite the presence of NULL values in the primary key columns; the data is silently changed, which could cause problems.

Another reason to prefer COPY:

Operations for which you specify ALGORITHM=COPY or old_alter_table=1, to force the table-copying behavior if needed for precise backward-compatibility in specialized scenarios.

Although MySQL manual doesn't talk about actual scenarios, you can imagine some. E.g. developer relied on table being locked during ALTER INDEX operation so table is read-only or fully locked and there is a process that reads static table during index rebuild.

Related Solutions

Mysql – Why would anyone ever disable `index_condition_pushdown` (ICP)

There are two related things...

Covering index: When the index is "covering" because all columns mentioned anywhere in the SELECT are contained in the index, the query has always been performed via "index-only scan". MySQL's EXPLAIN indicates it with

   Using index

Think of it as treating the Index BTree as a 'table'.

Index Condition Pushdown: The relatively new (5.6) 'ICP' applies to other situations -- when the part of the index being used for filtering is not sufficient.

Version 4.0 split the "Handler" from the "Storage Engines". The "Handler" does parsing, optimization, etc, etc. The "Engine" talks to the disk. This implies that rows are often fetched from disk by the engine, then handed off to the handler for more processing. In some situations, this handoff can be avoided -- by pushing more processing down from the handler into the engine. ICP is such a case. In the past, stuff that was not in the index, but tested in the WHERE, was handled only by the Handler. Now, the engine can handle it. EXPLAIN indicates it via

   Using index condition

With ICP, InnoDB tries to read the row and make the rest of the WHERE tests. This (usually) avoids handing off rows that are to be filtered out.

I have not heard of a case to turn ICP off. I suspect most 'modes' exist "just in case" the Optimizer has a surprise bug for which the best solution is to turn off a feature.

Your query

Are there only 3 columns in the table? If so, then a "table scan" is identical to an "index scan"; either would suffice. Given a choice, the optimizer would pick an index starting with zipcode. But it would not use the other 2 columns in the index for filtering -- because of the leading wildcard.

If there are only 3 columns in the table then a 3-column index would be "covering".

If there are more then 3 columns in the table, ICP lets all the filtering is done in the storage engine. Without ICP, only the filtering by zipcode is done in the storage engine; the rest would be handed off to the Handler.

Mysql – How innodb alter table inplace algorithm inner work

There are too many different cases. I suggest you read https://dev.mysql.com/doc/refman/8.0/en/innodb-create-index-overview.html , then come back with a more specific question. (Back up the version if you are using something older.)

My feeling in watching MySQL evolve over the years is this last round of Optimizations of ALTER has added ways to speed up just about everything in ALTER. That includes avoiding rebuilding the table (when practical) and avoiding locks (when practical).

"says mysql can dml when modify column" -- Well, that depends on the modify column -- add/remove NULL, change ENUM / DEFAULT / etc, ADD/DROP, etc.

The first ALTER optimization was for adding an option to ENUM at the end and not exceeding 255. That was added many versions ago -- It was rather trivial in that it changed the "meta" data only. That can even be INSTANT in the new 8.0 terminology (even better than INPLACE).

Best Answer

Related Solutions

Mysql – Why would anyone ever disable `index_condition_pushdown` (ICP)

Mysql – How innodb alter table inplace algorithm inner work

Related Question