What's New in MariaDB Xpand 6?

Columnar Indexes

MariaDB Xpand 6 adds support for Columnar indexes. A single Columnar index can be created on each table, though the index can contain multiple columns.

Columnar indexes are best suited for queries that "scan" much or all of a table. This includes queries that perform aggregations on large amounts of data, queries that select a subset of columns from large tables, and queries that perform a full table scan of a fact table. Queries tend to match this pattern in analytical, online-analytical processing (OLAP), and data warehousing workloads.

With composite (multi-column) Columnar indexes, filtration is not dependent on column order. If a Columnar index is defined with COLUMNAR INDEX (a, b, c), and the query filters with WHERE b = 1 AND c = 2, the Columnar index can be used to filter the results.

To add a Columnar index to an existing table:

ALTER TABLE hq_sales.invoices
   ADD COLUMNAR INDEX cols (invoice_date, invoice_total);

For best performance, MariaDB recommends loading data into the table prior to adding a Columnar index.

The cost_read_column system variable is used for costing columnar reads. The default value of cost_read_column in Xpand 5.3 was 0. For deployments upgrading to Xpand 6 from previous versions, the value of cost_read_column should be set to 1.

For additional information, see "MariaDB Xpand Columnar Indexes".