COLUMNAR Indexes with MariaDB Xpand

Overview

MariaDB Xpand 6 supports Columnar indexes for hybrid workloads that need to leverage Columnar indexes and row-based secondary indexes concurrently:

  • Xpand 6 allows Columnar indexes to be defined on normal tables with most data types

  • Columnar indexes can be used for ad-hoc queries, where the fields in a query are not known in advance

  • Columnar indexes can be used for operational analytics, where real-time analytics are performed on transactional data

  • Columnar indexes are compressed by default

  • A Delta Store is used to optimize updates to Columnar indexes

This page explains how to use Columnar indexes. Before using Columnar indexes, ensure that the special cases for Columnar indexes do not apply to your environment.

For information on the architecture for Columnar indexes, see "MariaDB Xpand Columnar Index Architecture".

Compatibility

  • MariaDB Xpand 6.0

  • MariaDB Xpand 6.1

CREATE COLUMNAR INDEX

With MariaDB Xpand, the CREATE COLUMNAR INDEX statement can create a Columnar index on an existing table:

CREATE COLUMNAR INDEX idx_invoices_date_total
   ON hq_sales.invoices (invoice_date, invoice_total);

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

DROP INDEX

With MariaDB Xpand, the DROP INDEX statement can be used to drop a Columnar index from an existing table:

DROP INDEX idx_invoices_date_total
   ON hq_sales.invoices;

CREATE TABLE

With MariaDB Xpand, the CREATE TABLE statement can be used to create a new table with a Columnar index using the COLUMNAR INDEX clause:

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY (invoice_id),
   COLUMNAR INDEX idx_invoices_date_total (invoice_date, invoice_total)
);

ALTER TABLE

ALTER TABLE .. ADD COLUMNAR INDEX

With MariaDB Xpand, the ALTER TABLE statement can be used to create a new Columnar index on an existing table using the ADD COLUMNAR INDEX clause:

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

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

ALTER TABLE .. ALTER INDEX

With MariaDB Xpand, the ALTER TABLE statement can be used to alter an existing Columnar index using the ALTER INDEX clause:

ALTER TABLE hq_sales.invoices
   ALTER COLUMNAR INDEX idx_invoices_date_total (invoice_date, invoice_total)
   INVISIBLE;

ALTER TABLE .. DROP INDEX

With MariaDB Xpand, the ALTER TABLE statement can be used to drop a Columnar index from an existing table using the DROP INDEX clause:

ALTER TABLE hq_sales.invoices
   DROP INDEX idx_invoices_date_total;

ALTER TABLE .. RENAME INDEX

With MariaDB Xpand, the ALTER TABLE statement can be used to rename a Columnar index using the RENAME INDEX clause:

ALTER TABLE hq_sales.invoices
   RENAME INDEX idx_invoices_date_total
   TO idx_invoices_date_total_old;

Bulk Data Loads and Columnar Indexes

When performing a bulk data load into a table with a Columnar index, performance issues can occur due to frequent Delta Store merges.

For best performance, MariaDB recommends loading data into the table before adding a columnar index using the following procedure:

  1. Create the table without a Columnar index

  2. Load data into the table

  3. Add the columnar index

If you still want to create a table with a Columnar index, please keep in mind that the Columnar index can lead to performance issues for bulk data loads due to frequent Delta Store merges.

Upgrades

When upgrading from MariaDB Xpand 5.3 to Xpand 6, there are some upgrade considerations:

  • After upgrading, the value of cost_read_column should be set to 0.9

    • 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.01

Special Cases

  • Xpand does not support columnar tables, only columnar indexes on row-based tables

  • Xpand only permits a single columnar index per table (but that index can contain multiple columns)

  • The variable consistent_order has no effect on tables with columnar indexes

  • The UNIQUE index option is not available for columnar indexes

  • Columnar indexes can't be defined with prefix lengths

  • Columnar indexes can't be defined for temporary tables

  • Generated columns can't be indexed with Columnar indexes

  • Foreign keys can't be defined on columns in Columnar indexes

  • All data types can be indexed in Columnar indexes, except:

  • Columnar indexes are not supported on older versions of Xpand or MariaDB Enterprise Server, so cannot be used in replication or with backup/restore to those versions and databases