Introducing indexes

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 TEXT and BLOB columns, the index is mandatory.

Tip

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 of characters has a special meaning. However, we will probably only save a considerable amount of space if we have a very high number of rows, or if several column prefixes can be indexed.

There are two important index types: BTREE and HASH. Another...