Mysql very slow for alter table query

sql

alter-table

performance

best-practices

Supercharge MySQL ALTER TABLE operations with:

ALGORITHM=INPLACE: Decreases locking, sidesteps full table copy.
LOCK=NONE: Grants other sessions table access during alteration.
Index Management: Kick out obsolete indexes before, bring in essential ones after.
Chunky Operations: For behemoth tables, tweak in chunks to cut downtime.

-- Drop the dead weight, bring in the MVP
ALTER TABLE my_table DROP INDEX old_index, ADD INDEX new_index, ALGORITHM=INPLACE, LOCK=NONE;

Always trial run to ensure snappy execution and data safety!

Scrutinizing slow ALTER TABLEs

MySQL's ALTER TABLE can sometimes be slower than a turtle wading in molasses due to reasons such as:

Staggering table size: With a sea of rows, the database must scribble tons of data.
Resource scarcity: Limited memory or I/O power can throttle performance.
Index infestation: Extra indexes balloon the quantity of data to process.
Simultaneous operations: Other operations on the same table can add to locking woes and time taken.

Before hitting a large alteration, it's prudent to tidy up:

pt-online-schema-change: A lifesaver from the Percona Toolkit that lets you alter tables with negligible production impact.
gh-ost: A handy tool from GitHub's desk that allows for online schema changes with minimal load, even during high traffic.
lhm: The Large Hadron Migrator provides a virtual stage for large-scale database alterations, allowing changes on the fly.

Normalize Your Data: Simplify your data model to shrink table complexity and size.
Foreign Key Gymnastics: Temporarily disable foreign_key_checks to speed up foreign key maneuvers.
Unique Index Trickery: Similar to above, turning off unique_checks can hasten the rebuild of unique indexes.

A step-wise methodology with pt-online-schema-change:

When you can't afford even the slightest downtime:

Certain data types and table engines:

Text/BLOB columns: Their storage nature can create a roadblock in alters process.
MyISAM Engine: Unlike InnoDB, MyISAM is "allergic" to concurrent alterations due to absence of row-level locking.