Before discussing the
EXPLAIN statement and how the MariaDB optimizer chooses an execution plan, it is important to understand how MariaDB uses indexes.
An index can be defined on one or more columns and their order is relevant. An index that involves string columns can be defined on their prefixes (the leftmost part of the data). For the
BLOB columns, the index is mandatory.
Rarely can the use of an index prefix speedup queries. However, sometimes we may want to reduce the disk space occupied by indexes. If only the leftmost characters of a string column are used in
WHERE clauses, we can choose to use a partial index on it; for example, this can be feasible if a column contains codes, where each character or group ...