mysql - online index creation

Personally, i'm quite a huge fan of document databases, mostly because the mindset behind those is around a schemaless dataset and the operations are meant to scale horizontally. In addition, many if not all operations on the data are non-blocking.

Relational databases tend to be more complex, often relaed to the intrinsic demand to be ACID compliant. Luckily, sometimes there are options 😄

Online structural change - index creation

A simple (yet seemingly very often not utilized) option to alter a database is for example to create indices online (or possibly rebuild, reorganize, etc. them).

So, when you're in mysql / mariadb, how just use the ALGORITHM and LOCK flags and you can process large amounts of data without (b)locking your tables.

CREATE INDEX IF NOT EXISTS myOnlineCreateIndex ON MySuperBusyTable (Id, Transaction, Date) ALGORITHM=INPLACE LOCK=NONE;

That's it - simple yet powerful.

Side node: IF NOT EXISTS is a part of mariadb's SQL features and not available on mysql as of today.