MariaDB Xpand Unique Indexes

Tables use Unique indexes to speed up query execution and to enforce constraints to ensure that the column contains unique values.

Overview

MariaDB Xpand supports unique indexes:

  • The behavior of unique indexes with Xpand is identical to the behavior of unique indexes with InnoDB.

  • Xpand uses indexes to generate additional representations of table data. These representations are then sliced and replicated through the deployment.

  • Unique indexes are B+ trees, which are very efficient for searching for exact values, performing range scans, and checking uniqueness.

This documentation applies to both Xpand topologies. Most examples are designed for the Xpand Performance Topology. However, the examples explicitly mention any differences that are relevant for the Xpand Storage Engine Topology.

Examples

Create a Table with a Unique Index

To create a table with a unique index, use the CREATE TABLE statement with the UNIQUE INDEX clause:

CREATE TABLE hq_sales.customers (
   customer_id BIGINT AUTO_INCREMENT NOT NULL,
   customer_name VARCHAR(500),
   customer_email VARCHAR(200),
   PRIMARY KEY (customer_id),
   UNIQUE INDEX (customer_email)
);

Note

If you are using an Xpand Storage Engine Topology, you must also specify ENGINE=Xpand, unless default_storage_engine is set to Xpand.

Creating Tables with Single Column Unique Index

Let's create an Xpand table with a single column Unique index:

  1. Connect to the server using MariaDB Client:

    $ mysql --user=root --password --no-auto-rehash
    
  2. Use the CREATE DATABASE statement to create the database:

    CREATE DATABASE hq_sales;
    

    Note

    In a Xpand Storage Engine Topology, the ES nodes do not synchronize databases automatically. MariaDB Replication can be used to synchronize databases and other objects. Without replication, the database needs to be created separately on each ES node.

  3. Use the CREATE TABLE statement to create the table, and specify the UNIQUE INDEX clause to create a unique index:

    CREATE TABLE hq_sales.customers (
       customer_id BIGINT AUTO_INCREMENT NOT NULL,
       customer_name VARCHAR(500),
       customer_email VARCHAR(200),
       PRIMARY KEY (customer_id),
       UNIQUE INDEX (customer_email)
    );
    

    For a single column unique index, the unique index can also be specified with the UNIQUE column option:

    CREATE TABLE hq_sales.customers (
       customer_id BIGINT AUTO_INCREMENT NOT NULL,
       customer_name VARCHAR(500),
       customer_email VARCHAR(200) UNIQUE,
       PRIMARY KEY (customer_id)
    );
    

    Note

    If you are using an Xpand Storage Engine Topology, you must also specify ENGINE=Xpand, unless default_storage_engine is set to Xpand.

Creating Tables with Composite Unique Index

Let's create an Xpand table with a composite (multi-column) Unique index:

  1. Connect to the server using MariaDB Client:

    $ mysql --user=root --password --no-auto-rehash
    
  2. Use the CREATE DATABASE statement to create the database:

    CREATE DATABASE hq_sales;
    

    Note

    In a Xpand Storage Engine Topology, the ES nodes do not synchronize databases automatically. MariaDB Replication can be used to synchronize databases and other objects. Without replication, the database needs to be created separately on each ES node.

  3. Use the CREATE TABLE statement to create the table, and specify the UNIQUE INDEX clause to create a unique index:

    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),
       UNIQUE INDEX (invoice_date, customer_id, branch_id)
    );
    

    Note

    If you are using an Xpand Storage Engine Topology, you must also specify ENGINE=Xpand, unless default_storage_engine is set to Xpand.

Creating Tables with Unique Index on Column Prefix

Let's create an Xpand table with a unique index on a single column prefix:

  1. Connect to the server using MariaDB Client:

    $ mysql --user=root --password --no-auto-rehash
    
  2. Use the CREATE DATABASE statement to create the database:

    CREATE DATABASE hq_sales;
    

    Note

    In a Xpand Storage Engine Topology, the ES nodes do not synchronize databases automatically. MariaDB Replication can be used to synchronize databases and other objects. Without replication, the database needs to be created separately on each ES node.

  3. Use the CREATE TABLE statement to create the table, and specify the UNIQUE INDEX clause to create a unique index:

    CREATE TABLE hq_sales.products (
       product_id BIGINT AUTO_INCREMENT NOT NULL,
       product_name VARCHAR(500),
       product_brand VARCHAR(500),
       product_description TEXT,
       PRIMARY KEY (product_id),
       UNIQUE INDEX (product_description(1000))
    );
    
    • The Unique index is specified with the product_description(1000) prefix, so only the first 1000 characters of the product_description column for each row will be indexed and checked for uniqueness.

    Note

    If you are using an Xpand Storage Engine Topology, you must also specify ENGINE=Xpand, unless default_storage_engine is set to Xpand.

Adding Unique Index

Let's create an Xpand table without a unique index, and then add a unique index to it:

  1. Connect to the server using MariaDB Client:

    $ mysql --user=root --password --no-auto-rehash
    
  2. Use the CREATE DATABASE statement to create the database:

    CREATE DATABASE hq_sales;
    

    Note

    In a Xpand Storage Engine Topology, the ES nodes do not synchronize databases automatically. MariaDB Replication can be used to synchronize databases and other objects. Without replication, the database needs to be created separately on each ES node.

  3. Use the CREATE TABLE statement to create the table:

    CREATE TABLE hq_sales.customers (
       customer_id BIGINT AUTO_INCREMENT NOT NULL,
       customer_name VARCHAR(500),
       customer_email VARCHAR(200),
       PRIMARY KEY (customer_id)
    );
    

    Note

    If you are using an Xpand Storage Engine Topology, you must also specify ENGINE=Xpand, unless default_storage_engine is set to Xpand.

  4. Alter the table using the ALTER TABLE statement and specify the new unique index with the ADD UNIQUE INDEX clause:

    ALTER TABLE hq_sales.customers ADD UNIQUE INDEX (customer_email);
    

Dropping Unique Index

Let's drop the unique index from the table created in the Creating Table with Single Column Unique Index section:

  1. Alter the table using the ALTER TABLE statement and specify the DROP INDEX clause:

    ALTER TABLE hq_sales.customers DROP INDEX customer_email;
    

Rebuilding Unique Index

Let's rebuild the unique index in the table created in the Creating Table with a Single Column Unique Index section:

  1. Alter the table using the ALTER TABLE statement and specify the DROP INDEX clause:

    ALTER TABLE hq_sales.customers DROP INDEX customer_email;
    
  2. Alter the table using the ALTER TABLE statement and specify the unique index with the ADD UNIQUE INDEX clause:

    ALTER TABLE hq_sales.customers ADD UNIQUE INDEX (customer_email);