Plan MySQL online index creation around locks and rebuild cost
For MySQL online index creation, begin by proving which system and state you are about to affect. InnoDB DDL algorithms and lock clauses control whether data is rebuilt and which concurrent operations are allowed, but support depends on the exact alteration. The safest useful answer is therefore an evidence sequence: identify the target, capture its effective state, make one bounded change, and verify through a path independent of the command that accepted the change.
TL;DR: The server accepts the explicit algorithm and lock level, replication remains healthy, and the optimizer can use the new index. Start with mysql -e 'SHOW CREATE TABLE orders\\G', stop if the evidence instead shows that the operation copies the table, waits on metadata locks, fills storage, or amplifies replica lag, and keep this recovery action ready: cancel before commit when safe, drop the candidate index only after confirming no workload uses it, and restore capacity or replica health.
Assumed audience: database operators who can use a dedicated diagnostic account, read execution plans, and separate query cancellation from durable data changes. This field guide assumes a controlled maintenance window and working recovery access.
Direct answer and success condition
The success condition for MySQL online index creation is not a zero exit code. It is that the server accepts the explicit algorithm and lock level, replication remains healthy, and the optimizer can use the new index. A command may return successfully while a controller is still converging, a client is using cached state, a remote dependency is unavailable, or the wrong account, namespace, region, host, database, repository, or process accepted the request.
Use the task boundary—client session, transaction, optimizer, InnoDB concurrency, binary log, replica applier, storage, and authentication policy—to decide what the result proves. The candidate action belongs only at the first layer whose observed state contradicts the desired outcome. If the baseline cannot locate that contradiction, do not make the action broader.
Working model
For MySQL operations, use this operational model: MySQL parses and optimizes a statement, executes it through storage-engine iterators, coordinates concurrent transactions, records durable changes, and may stream binary log events to replicas. This model matters for MySQL online index creation because InnoDB DDL algorithms and lock clauses control whether data is rebuilt and which concurrent operations are allowed, but support depends on the exact alteration. It also separates four forms of evidence that are often collapsed:
- Declared state: files, manifests, playbooks, policies, arguments, or API requests say what should happen.
- Effective state: the running tool or service reports what it actually loaded and selected.
- Resource state: processes, objects, data, network paths, and queues reflect the change.
- Outcome state: the original user, automation, or recovery workflow succeeds from the relevant vantage point.
Declared state without effective state is only intent. Effective state without outcome state is only partial convergence. Keep these labels in the change record so another operator can tell what was measured.
Preflight: identity, scope, and recovery
Use a dedicated session with explicit timeouts, capture the server version and topology, and test restore or DDL behavior on a representative copy first.
Record the current UTC time, operator identity, tool version, exact target identifiers, last successful execution, recent related changes, and the owner of the workload. Save the effective configuration or object state using its supported read-only interface. Do not store credentials, complete environment dumps, private customer data, or unrestricted topology in the ticket.
The recovery path for this guide is explicit: cancel before commit when safe, drop the candidate index only after confirming no workload uses it, and restore capacity or replica health. Rehearse the targeting syntax and confirm that recovery does not depend on the same account, network path, key, state file, database, repository, or process being changed.
Stop before changing state when any of these statements is true:
- The target can be selected by a default or ambiguous alias.
- The current configuration or binding cannot be reconstructed.
- The only privileged or remote session would be put at risk.
- The command affects an unbounded host, key, object, snapshot, branch, or resource set.
- The expected output cannot be distinguished from stale, cached, or partial state.
Capture the baseline
Run the following commands individually after replacing example identifiers deliberately:
mysql -e 'SHOW CREATE TABLE orders\\G'
mysql -e 'SHOW INDEX FROM orders'
mysql -e 'EXPLAIN ALTER TABLE orders ADD INDEX idx_customer(customer_id)'
mysql -e 'SELECT * FROM performance_schema.metadata_locks'For MySQL online index creation, classify the output before proposing a fix:
- Expected evidence: the server accepts the explicit algorithm and lock level, replication remains healthy, and the optimizer can use the new index.
- Abnormal evidence: the operation copies the table, waits on metadata locks, fills storage, or amplifies replica lag.
- Inconclusive evidence: no output, permission errors, incomplete history, disabled instrumentation, a different version, or a different control plane can all hide the relevant state. Confirm those assumptions rather than translating absence into health.
Preserve timestamps and exit statuses for decisive observations. Prefer machine-readable output when it can be filtered without collecting secrets. Compare a healthy peer only by equivalent effective fields; copying its entire configuration can introduce a second problem.
Controlled action
This is state-changing example syntax. It is intentionally presented after the baseline and must not be pasted with example targets:
mysql -e 'ALTER TABLE orders ADD INDEX idx_customer(customer_id), ALGORITHM=INPLACE, LOCK=NONE'The action is justified only if the baseline predicts its effect at the named boundary. For MySQL online index creation, the principal risk is that online does not mean free; long DDL consumes I/O, log space, and lock opportunities. Review the exact expansion of variables, globs, resource addresses, inventory patterns, database identities, repository locations, and cloud regions before approval.
Prefer a canary, dry run, saved plan, isolated restore, configuration validator, transaction, immutable artifact, or runtime drain when the platform supplies one. Record the command, approver, UTC time, and expected convergence interval. Do not stack unrelated cleanup, restart, permission, and configuration actions into the same observation window.
Independent verification
Repeat the state inspection and then exercise the original path:
mysql -e 'SHOW INDEX FROM orders'
mysql -e 'SELECT * FROM performance_schema.metadata_locks'
mysql -e 'EXPLAIN FORMAT=TREE SELECT * FROM orders WHERE customer_id=42'Verification for MySQL online index creation must answer five questions:
- Did the intended identity accept the operation?
- Did effective state converge to the reviewed value?
- Did the underlying resource or data path change as predicted?
- Did the real consumer succeed from an independent vantage point?
- Did adjacent safety signals—capacity, latency, errors, replication, audit, or persistence—remain healthy?
If mysql -e 'SHOW INDEX FROM orders' passes but the consumer still fails, the local boundary may be repaired while another layer remains broken. Keep the new evidence, stop making the change broader, and move to the next falsifiable boundary.
Decision branches
The baseline contradicts the guide
If the operation copies the table, waits on metadata locks, fills storage, or amplifies replica lag, the candidate action no longer follows from the evidence. Re-establish identity and scope, shorten the observation window, and formulate a mechanism that the next read-only command can disprove.
The command succeeds but nothing converges
A successful mysql -e 'ALTER TABLE orders ADD INDEX idx_customer(customer_id), ALGORITHM=INPLACE, LOCK=NONE' proves only that one interface accepted the request. It may not prove persistence, controller completion, process reload, data compatibility, replication, traffic admission, or client refresh. Inspect those transitions in order.
The change makes the outcome worse
Execute the prepared recovery: cancel before commit when safe, drop the candidate index only after confirming no workload uses it, and restore capacity or replica health. Preserve the failed candidate, event times, and relevant logs. Avoid repeated restarts, broad resets, garbage collection, pruning, history rewriting, or cleanup that can erase the evidence needed to explain the failure.
The result is mixed
Mixed results normally mean scope differs across hosts, workers, replicas, zones, clients, branches, or repositories. Partition the evidence by identity instead of averaging it. Hold further rollout until each partition has an explicit disposition.
Review checklist
- Confirm the operator, account, region, namespace, host, resource, repository, database, or branch.
- Print the installed tool version and resolve the effective configuration.
- Capture the baseline and name one observation that would disprove the proposed mechanism.
- Mark
mysql -e 'ALTER TABLE orders ADD INDEX idx_customer(customer_id), ALGORITHM=INPLACE, LOCK=NONE'as state-changing in review. - Keep recovery access independent and test the exact rollback target.
- Change one layer and wait for its documented convergence boundary.
- Repeat the original user or automation path, not only the control-plane query.
- Watch error rate, latency, capacity, data durability, audit, and persistence after the change.
- Remove temporary credentials, traces, restored data, debug settings, and candidate resources under policy.
- Update this field guide when observed behavior differs from the source-reviewed model.
Investigate it in Tryssh
$ mysql -e 'SHOW INDEX FROM orders' Expected: the server accepts the explicit algorithm and lock level, replication remains healthy, and the optimizer can use the new index.
Tryssh can help preserve this evidence trail and place a state-changing command behind human approval. It cannot decide the correct production target, authorize a cloud or database change, guarantee backup completeness, or replace independent recovery access.
Evidence and review status
This MySQL online index creation field guide was source-reviewed on 2026-07-29 against current upstream or first-party documentation. Commands use example identifiers and were not executed against every distribution, service version, provider, database topology, repository backend, network, or workload. Provider-managed services may expose a different control plane or restrict local commands.
The article makes no ranking guarantee and does not treat documentation review as reproduction. Validate installed versions, permissions, feature support, recovery behavior, and billing or data-retention consequences in your environment.
Limitations and trade-offs
Online does not mean free; long DDL consumes I/O, log space, and lock opportunities. A narrow safe action may take longer than a broad reset, and a strong verification plan may require temporary capacity or an isolated restore target. Those costs are part of reliable operations, not optional ceremony.
Do not use a search result as authority to change production. The live system, reviewed policy, upstream versioned documentation, and accountable operator remain the sources of truth.
Continue the cluster
Next, read MySQL replication lag or MySQL KILL QUERY vs CONNECTION. For broader context, use the MySQL operations foundation guide and the SSH hardening checklist.