How do I do a `CREATE INDEX` with Percona's `pt-online-schema-change` tool?

Question

How do I do a CREATE INDEX with Percona's pt-online-schema-change tool? I want to do something like:

CREATE UNIQUE INDEX idx_name ON table_name (col_1, col_2, ...) USING BTREE

According to the documentation, I must use the --alter argument and then the appropriate ALTER TABLE statement, minus the preceding ALTER TABLE table_name phrase. However, CREATE INDEX does not start with ALTER TABLE, and the table name is embedded inside the CREATE INDEX statement. So how can I move forward?

Edyth

Answer 1

According to the documentation for MySQL CREATE INDEX:

CREATE INDEX is mapped to an ALTER TABLE statement to create indexes.

Thus, you can convert my example to the SQL statement:

ALTER TABLE table_name ADD UNIQUE INDEX idx_name (col_1, col_2, ...) USING BTREE

Resulting in the Percona schema modification statement:

ADD UNIQUE INDEX idx_name (col_1, col_2, ...) USING BTREE

Edyth

Answer 2

I use this one) pt-online-schema-change --alter 'add index ix_cdate (cdate)' D=database_name,t=table_name --dry-run --critical-load Threads_running=110

Rao

this helped with my syntax error while adding index – Hetaerism