mariadb-create-index

MariaDB requires a mandatory prefix length when indexing TEXT or BLOB columns, otherwise it raises ER_BLOB_KEY_WITHOUT_LENGTH. Use syntax like CREATE INDEX i ON t (body(255)) to index the first 255 characters.

MariaDB does not support expression or functional indexes in CREATE INDEX; index columns must be plain column names. Instead, add a generated column holding the expression and index that column, for example ALTER TABLE t ADD c INT AS (col1 + col2) PERSISTENT, ADD INDEX (c).

No, CREATE INDEX cannot create a primary key in MariaDB. Use ALTER TABLE t ADD PRIMARY KEY or define it in CREATE TABLE, which is preferable for InnoDB since it clusters on the primary key and would otherwise rebuild the table.

Yes, descending index parts are physically real since MariaDB 10.8. CREATE INDEX i ON t (a ASC, b DESC) stores b in reverse order, which can serve mixed-direction ORDER BY queries without a filesort.

Drop a primary key using the reserved name PRIMARY with required backtick quoting: DROP INDEX `PRIMARY` ON t. DROP INDEX maps to ALTER TABLE DROP INDEX, and IF EXISTS downgrades a missing index to a warning.

Wrapping an indexed column in a function like DATE(ts) or LOWER(email) defeats the index. Rewrite the predicate as a range condition, or index a generated column holding the expression so the optimizer can use it.