Random bits and bobs when learning MySQL

ALTER TABLE DLL

Temporary tables are usually spun up

Storage, Performance, and Concurrency Considerations

In most cases, ALTER TABLE makes a temporary copy of the original table. MySQL waits for other operations that are modifying the table, then proceeds. It incorporates the alteration into the copy, deletes the original table, and renames the new one. While ALTER TABLE is executing, the original table is readable by other sessions. Updates and writes to the table that begin after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table without any failed updates. The temporary table is created in the database directory of the new table. This can differ from the database directory of the original table for ALTER TABLE operations that rename the table to a different database.

ALGORTHIM=COPY blocks the table

An ALTER TABLE operation that uses the COPY algorithm prevents concurrent DML operations. Concurrent queries are still allowed. That is, a table-copying operation always includes at least the concurrency restrictions of LOCK=SHARED (allow queries but not DML). You can further restrict concurrency for operations that support the LOCK clause by specifying LOCK=EXCLUSIVE, which prevents DML and queries.

Cheatsheet

Getting the size of the databases in a server in MB:

SELECT table_schema, sum((data_length+index_length)/1024/1024) AS MB
FROM information_schema.tables
GROUP BY 1;