CREATE INDEX

Syntax

CREATE [UNIQUE] INDEX <index_name> [index_type] ON <table_name> (<index_column_name>,...) [<index_option>]

<index_column_name>:
    <column_name> [ASC | DESC]

<index_type>:
    USING { BTREE | HASH }

<index_option>:
  | <index_type>
  | COMMENT '<string>'
  | UNENFORCED [RELY | NORELY]

Remarks

• <index_name> is the name of the index you want to create.

• <table_name> is the name of a table in a SingleStore database.

• Columnstore indexes cannot be created using the CREATE INDEX statement. Columnstore indexes must be defined when the table is created using the CREATE TABLE statement (see CREATE TABLE).

• For more information about unenforced unique constraints (UNENFORCED [RELY | NORELY]). see Specifying Unenforced Unique Constraints.

• Only hash indexes can be created on columnstore tables. These hash indexes are non-unique and single-column, only.

• This command can be run on the master aggregator node, or a child aggregator node (see Node Requirements for SingleStore Commands ).

• This command causes implicit commits. Refer to COMMIT for more information.

• This command will attempt to run as an online operation, but in certain cases cannot. See ALTER TABLE for more information. Operations which must be run offline cannot be run on distributed tables.

• Every unique index must have all the columns of the shard key. Without it, the database cannot guarantee uniqueness across distributed database partitions.

• Adding or altering a unique index is not supported on existing columnstore tables.

• SingleStore supports online CREATE INDEX, which means that you can read and write while the table is being indexed. CREATE INDEX on a sharded table is always executed online. Note that online CREATE INDEX will not begin indexing the table, but it will wait until all DML queries that were already running on the table finish. This allows any in-progress queries to complete execution before indexing the table, and ensures consistency of results from queries on the table since the time of execution of CREATE INDEX. As soon as the in-progress reads and writes complete and the CREATE INDEX command begins indexing the table, new reads and writes will proceed as normal. This blocking period usually lasts approximately for milliseconds.

  If you are running frequent CREATE INDEX statements on a table and have a lot of long-running queries on that table, then your normal workload may experience some periods of delay since it blocks other queries from starting while it waits for completion of long-running queries.

  You can monitor the progress of the CREATE INDEX statement by running the following statement:

  SQL

  Copy

  SELECT * FROM INFORMATION_SCHEMA.MV_PROCESSLIST 
  WHERE INFO LIKE '%CREATE%' ORDER BY NODE_ID;

  This query contains information about currently running CREATE commands for all nodes on a cluster. Since DDL commands like CREATE run on the master aggregator only, the order by NODE_ID clause ensures the master aggregator is the top result. Note the STATE column as it displays the state of the process.

  A common state for DDL commands like CREATE is: waiting on the leaves. This is a normal state in most cases since creating an index on a table happens on the leaves. If the execution of the DDL command is waiting on a metadata lock on the leaves and doesn't progress, there could be a long-running query preventing the lock from being taken. A metadata lock is taken when DDL commands are executed to ensure a consistent state on all nodes.

  Refer to the Query Errors topic for resolving query timeout errors due to long running queries in a workload.

• Refer to the Permission Matrix for the required permission.

Example

In the following example, you add a single-column index to the articles table, which is a columnstore table defined as follows.

CREATE TABLE articles (
    id INT UNSIGNED,
    month int UNSIGNED,
    year int UNSIGNED,
    title VARCHAR(200),
    body TEXT,
    SHARD KEY(title),
    SORT KEY (id),     
    UNIQUE KEY (title) USING HASH);

Add a single-column unique index:

CREATE INDEX id_index ON articles(id) USING HASH;