Create an index on the specified table.

Syntax

CREATE [UNIQUE] INDEX index_name [index_type] ON tbl_name (index_col_name,...) [index_option]

index_col_name:
    col_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.
• tbl_name is the name of a table in a MemSQL 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).
• This command must be run on the master aggregator node (see Node Requirements for MemSQL Commands).
• 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.
• MemSQL 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 on the order of 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.

Example

memsql> CREATE INDEX seq_index ON mytbl (seq);
Query OK, 0 rows affected (9.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

Was this Article Helpful?

Thanks For Your Feedback!