What's New in MariaDB Xpand 6?
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.
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
For additional information, see "MariaDB Xpand Columnar Indexes".