MariaDB Xpand Documentation

Space shortcuts

Page tree

The 6.0 version of Xpand offers the ability for a user to create a columnar index on their normal, row-based transactional tables. Creation of the columnar index improves execution for queries that require scanning of lots of data for a subset of columns of a table, and can be used in conjunction with the improvements to how aggregation queries are executed (also part of v6.0). This offers users the ability to run aggregation queries on their transactional data efficiently and orders of magnitude faster than with a row-based index. 

Xpand only permits a single columnar index per table (but that index can contain multiple columns). Each columnar index includes independent storage for each column, and columnar indexes are always compressed. 

To create a columnar index, you can either create an index explicitly on an existing table:

sql> CREATE INDEX J on T2 (J) USING COLUMNAR;

Or include it in the CREATE TABLE statement: 

sql> CREATE TABLE `t2` (
  `i` int(11),
  `j` varchar(20) CHARACTER SET utf8,
  COLUMNAR INDEX `j` (`j`) ,
  Primary KEY `ii` (`i`) /*$ DISTRIBUTE=1 */
) CHARACTER SET utf8 /*$ SLICES=1 */

The UNIQUE option is not available for columnar indexes. 

sql> CREATE UNIQUE INDEX J on T2 (J) USING COLUMNAR;
ERROR 1 (HY000): [1027] Bad Arguments: Columnar index cannot be unique.

Caveats for Xpand columnar indexes:

  • 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)
  • Xpand does not support the use of foreign keys on columns that have columnar indexes 
  • Columnar indexes cannot be created on generated columns, or columns that use the JSON or BLOB data type 
  • The variable consistent_order has no effect on tables with columnar indexes
  • Columnar indexes cannot be created on Temporary Tables 
  • Columnar indexes do not support prefix lengths 
  • Columnar indexes are not supported on older versions of Xpand or MariaDB, so cannot be used in replication or with backup/restore to those versions and databases. 
  • No labels