mysql

Order composite index columns with equality predicates first, then range or sort columns, following the leftmost prefix rule. Range predicates stop index usage for subsequent columns, so place them last. Verify usage with EXPLAIN and check key_len to confirm how many columns are used.

Run SHOW ENGINE INNODB STATUS and inspect the LATEST DETECTED DEADLOCK section to see the conflicting transactions. Common causes are opposite row access ordering, gap locks under REPEATABLE READ, and missing indexes on WHERE columns. Fix by indexing filtered columns, keeping transactions short, and retrying error 1213 with backoff.

Prefer BIGINT UNSIGNED AUTO_INCREMENT for write-heavy OLTP tables because InnoDB clusters rows by primary key and sequential inserts avoid page splits. If external IDs are needed, store the UUID as BINARY(16) in a secondary unique column rather than as the clustered primary key.

It depends on the algorithm: INSTANT is metadata-only, INPLACE rebuilds in the background with brief metadata locks, and COPY blocks writes entirely. Always specify ALGORITHM and LOCK=NONE explicitly so the statement fails loudly instead of silently falling back to a blocking COPY on large tables.

Common causes are functions on indexed columns, implicit type conversions, leading-wildcard LIKE patterns, and OR conditions across different columns. Rewrite predicates to be sargable, such as replacing YEAR(created_at) = 2024 with a range comparison, then confirm with EXPLAIN.

Stay on the REPEATABLE READ default for general OLTP since it prevents phantoms via next-key locks. Switch to READ COMMITTED per-session only when gap-lock deadlocks are confirmed or during high-contention bulk imports, and never change it globally.