Join us on
Star us on
Get Started
Slack
GitHub
Get Started
v2.5 (latest) v2.2 (stable) v2.1 (earlier version) v2.0 (earlier version) v1.3 (earlier version)
  • GET STARTED
    • Quick start
      • 1. Install YugabyteDB
      • 2. Create a local cluster
      • 3. Explore YSQL
      • 4. Build an application
        • Java
        • NodeJS
        • Go
        • Python
        • Ruby
        • C#
        • PHP
        • C++
        • C
    • Introduction
    • Explore core
      • 1. Linear scalability
      • 2. Fault tolerance
      • 3. Global distribution
      • 4. Auto sharding
      • 5. Tunable reads
      • 6. Observability
  • USER GUIDES
    • Develop
      • Learn app development
        • 1. SQL vs NoSQL
        • 2. Data modeling
        • 3. Data types
        • 4. ACID transactions
        • 5. Aggregations
        • 6. Batch operations
        • 7. Date and time
        • 8. Strings and text
      • Ecosystem integrations
        • Apache Kafka
        • Apache Spark
        • JanusGraph
        • KairosDB
        • Presto
        • Metabase
      • Real-world examples
        • E-Commerce App
        • IoT Fleet Management
        • Retail Analytics
      • Explore sample applications
    • Deploy
      • Checklist
      • Manual deployment
        • 1. System configuration
        • 2. Install software
        • 3. Start YB-Masters
        • 4. Start YB-TServers
        • 5. Verify deployment
      • Kubernetes
        • Helm Chart
        • Helm configuration
        • Local SSD
      • Docker
      • Public clouds
        • Amazon Web Services
        • Google Cloud Platform
        • Microsoft Azure
      • Pivotal Cloud Foundry
      • Yugabyte Platform
        • 1. Prepare cloud environment
        • 2. Install Admin Console
        • 3. Configure Admin Console
        • 4. Configure Cloud Providers
    • Benchmark
      • Performance
      • YCSB
      • Large datasets
    • Secure
      • Security checklist
      • Authentication
      • Authorization
        • 1. RBAC Model
        • 2. Create Roles
        • 3. Grant permissions
      • TLS encryption
        • 1. Prepare nodes
        • 2. Server-server encryption
        • 3. Client-server encryption
        • 4. Connect to cluster
      • Encryption at Rest
    • Manage
      • Backup and restore
        • Backing up data
        • Restoring data
      • Data migration
        • Bulk import
        • Bulk export
      • Change cluster config
      • Upgrade deployment
      • Diagnostics reporting
      • Yugabyte Platform
        • Create universe - Multi-zone
        • Create universe - Multi-region
        • Edit universe
        • Edit config flags
        • Health checking and alerts
        • Create and edit instance tags
        • Node status and actions
        • Read replicas
        • Back up and restore
        • Upgrade universe
        • Delete universe
    • Troubleshoot
      • Troubleshooting overview
      • Cluster level issues
        • YCQL connection issues
        • YEDIS connection Issues
      • Node level issues
        • Check processes
        • Inspect logs
        • System statistics
      • Yugabyte Platform
        • Troubleshoot universes
  • REFERENCE
    • APIs
      • YSQL
        • Statements
          • ABORT
          • ALTER DATABASE
          • ALTER DOMAIN
          • ALTER TABLE
          • BEGIN
          • COMMENT
          • COMMIT
          • COPY
          • CREATE DATABASE
          • CREATE DOMAIN
          • CREATE INDEX
          • CREATE SCHEMA
          • CREATE SEQUENCE
          • CREATE TABLE
          • CREATE TABLE AS
          • CREATE TYPE
          • CREATE USER
          • CREATE VIEW
          • DEALLOCATE
          • DELETE
          • DROP DATABASE
          • DROP DOMAIN
          • DROP SEQUENCE
          • DROP TABLE
          • DROP TYPE
          • END
          • EXECUTE
          • EXPLAIN
          • GRANT
          • INSERT
          • LOCK
          • PREPARE
          • RESET
          • REVOKE
          • ROLLBACK
          • SELECT
          • SET
          • SET CONSTRAINTS
          • SET TRANSACTION
          • SHOW
          • SHOW TRANSACTION
          • TRUNCATE
          • UPDATE
        • Data types
          • Binary
          • Boolean
          • Character
          • Date-time
          • Json
          • Money
          • Numeric
          • Serial
          • UUID
        • Expressions
          • currval()
          • lastval()
          • nextval()
        • Keywords
        • Reserved Names
      • YCQL
        • Quick Start YCQL
        • ALTER KEYSPACE
        • ALTER ROLE
        • ALTER TABLE
        • CREATE INDEX
        • CREATE KEYSPACE
        • CREATE ROLE
        • CREATE TABLE
        • CREATE TYPE
        • DROP INDEX
        • DROP KEYSPACE
        • DROP ROLE
        • DROP TABLE
        • DROP TYPE
        • GRANT PERMISSION
        • GRANT ROLE
        • REVOKE PERMISSION
        • REVOKE ROLE
        • USE
        • INSERT
        • SELECT
        • UPDATE
        • DELETE
        • TRANSACTION
        • TRUNCATE
        • Simple Value
        • Subscript
        • Function Call
        • Operator Call
        • BLOB
        • BOOLEAN
        • MAP, SET, LIST
        • FROZEN
        • INET
        • Integer & Counter
        • Non-Integer
        • TEXT
        • Date & Time Types
        • UUID & TIMEUUID
        • JSONB
        • Date and time functions
    • CLIs
      • yb-ctl
      • yb-docker-ctl
      • yb-master
      • yb-tserver
      • ysqlsh
      • cqlsh
    • Sample data
      • Chinook
      • Northwind
      • PgExercises
      • SportsDB
    • Tools
      • TablePlus
  • RELEASES
    • Release history
      • v1.3.1
      • v1.3.0
      • v1.2.12
      • v1.2.11
      • v1.2.10
      • v1.2.9
      • v1.2.8
      • v1.2.6
      • v1.2.5
      • v1.2.4
  • CONCEPTS
    • Architecture
      • Design goals
      • Layered architecture
      • Basic concepts
        • Universe
        • YB-TServer
        • YB-Master
        • Acknowledgements
      • Query layer
        • Overview
      • DocDB store
        • Sharding
        • Replication
        • Persistence
        • Performance
      • DocDB transactions
        • Isolation Levels
        • Single row transactions
        • Distributed transactions
        • Transactional IO path
  • FAQ
    • Comparisons
      • CockroachDB
      • Google Cloud Spanner
      • MongoDB
      • FoundationDB
      • Amazon DynamoDB
      • Azure Cosmos DB
      • Apache Cassandra
      • Redis in-memory store
      • Apache HBase
    • Other FAQs
      • Product
      • Architecture
      • Yugabyte Platform
      • API compatibility
  • CONTRIBUTOR GUIDES
    • Get involved
  • Misc
    • YEDIS
      • Quick start
      • Develop
        • Client drivers
          • C
          • C++
          • C#
          • Go
          • Java
          • NodeJS
          • Python
      • API reference
        • APPEND
        • AUTH
        • CONFIG
        • CREATEDB
        • DELETEDB
        • LISTDB
        • SELECT
        • DEL
        • ECHO
        • EXISTS
        • EXPIRE
        • EXPIREAT
        • FLUSHALL
        • FLUSHDB
        • GET
        • GETRANGE
        • GETSET
        • HDEL
        • HEXISTS
        • HGET
        • HGETALL
        • HINCRBY
        • HKEYS
        • HLEN
        • HMGET
        • HMSET
        • HSET
        • HSTRLEN
        • HVALS
        • INCR
        • INCRBY
        • KEYS
        • MONITOR
        • PEXPIRE
        • PEXPIREAT
        • PTTL
        • ROLE
        • SADD
        • SCARD
        • RENAME
        • SET
        • SETEX
        • PSETEX
        • SETRANGE
        • SISMEMBER
        • SMEMBERS
        • SREM
        • STRLEN
        • ZRANGE
        • TSADD
        • TSCARD
        • TSGET
        • TSLASTN
        • TSRANGEBYTIME
        • TSREM
        • TSREVRANGEBYTIME
        • TTL
        • ZADD
        • ZCARD
        • ZRANGEBYSCORE
        • ZREM
        • ZREVRANGE
        • ZSCORE
        • PUBSUB
        • PUBLISH
        • SUBSCRIBE
        • UNSUBSCRIBE
        • PSUBSCRIBE
        • PUNSUBSCRIBE
> APIs > YCQL >

CREATE INDEX

Attention

This page documents an earlier version. Go to the latest (v2.3) version.
  • Synopsis
  • Syntax
    • Diagram
    • Grammar
  • Semantics
    • PARTITION KEY
    • CLUSTERING KEY
    • INCLUDED COLUMNS
    • UNIQUE INDEX
  • Examples
    • Create a table to be indexed
    • Create an index for query by the order_date column
    • Create an index for query by the warehouse_id column
    • Insert some data
    • Query by the partition column customer_id in the table
    • Query by the partition column order_date in the index orders_by_date
    • Query by the partition column warehouse_id column in the index orders_by_warehouse
    • Create a table with a unique index
    • Insert values into the table and verify no duplicate userid is inserted
  • See also

Synopsis

The CREATE INDEX statement is used to create a new index on a table. It defines the index name, index columns, and additional columns to include.

Syntax

Diagram

create_index

CREATEUNIQUEINDEXIFNOTEXISTSindex_nameONtable_name(index_columns)included_columnsclustering_key_column_ordering

index_columns

partition_key_columnsclustering_key_columns

partition_key_columns

column_name(,column_name)

clustering_key_columns

,column_name

included_columns

INCLUDE(,column_name)

clustering_key_column_ordering

WITHCLUSTERINGORDERBY(,column_nameASCDESC)

Grammar

create_index ::= CREATE [ UNIQUE ] INDEX [ IF NOT EXISTS ] index_name ON table_name '(' index_columns ')'
                     [ included_columns ] [ clustering_key_column_ordering ];

index_columns ::= partition_key_columns [ clustering_key_columns ]

partition_key_columns ::= column_name | '(' column_name [ ',' column_name ...] ')'

clustering_key_columns ::= column_name [ ',' column_name ...]

included_columns ::= INCLUDE '(' column_name { ',' column_name } ')'

clustering_key_column_ordering ::= WITH CLUSTERING ORDER BY '(' column_name [ ASC | DESC ] [ ',' column_name [ ASC | DESC ] ...] ')'

Where

  • index_name, table_name, and column_name are identifiers. table_name may be qualified with a keyspace name. index_name cannot be qualified with a keyspace name because an index must be created in the table's keyspace.

Semantics

  • An error is raised if transactions have not be enabled using the WITH transactions = { 'enabled' : true } clause on the table to be indexed. This is because secondary indexes internally use distributed transactions to ensure ACID guarantees in the updates to the secondary index and the associated primary key. More details here.
  • An error is raised if index_name already exists in the associated keyspace unless the IF NOT EXISTS option is used.
  • Indexes do not support TTL. An error is raised if data is inserted with TTL into a table with indexes.
  • Currently, when an index is created on a table, the existing data in the table is not indexed. Therefore, the index should be created before any data is inserted into the table.

PARTITION KEY

  • Partition key is required and defines a split of the index into partitions.

CLUSTERING KEY

  • Clustering key is optional and defines an ordering for index rows within a partition.
  • Default ordering is ascending (ASC) but can be set for each clustering column as ascending or descending using the CLUSTERING ORDER BY property.
  • Any primary key column of the table not indexed explicitly in index_columns is added as a clustering column to the index implicitly. This is necessary so that the whole primary key of the table is indexed.

INCLUDED COLUMNS

  • Included columns are optional table columns whose values are copied into the index in addition to storing them in the table. When additional columns are included in the index, they can be used to respond to queries directly from the index without querying the table.

UNIQUE INDEX

  • A unique index disallows duplicate values from being inserted into the indexed columns. It can be used to ensure uniqueness of index column values.

Examples

Create a table to be indexed

'customer_id' is the partitioning column and 'order_date' is the clustering column.

cqlsh:example> CREATE TABLE orders (customer_id INT,
                                    order_date TIMESTAMP,
                                    warehouse_id INT,
                                    amount DOUBLE,
                                    PRIMARY KEY ((customer_id), order_date))
               WITH transactions = { 'enabled' : true };

Create an index for query by the order_date column

cqlsh:example> CREATE INDEX orders_by_date ON orders (order_date) INCLUDE (amount);

Create an index for query by the warehouse_id column

cqlsh:example> CREATE INDEX orders_by_warehouse ON orders (warehouse_id, order_date) INCLUDE (amount);

Insert some data

cqlsh:example> INSERT INTO orders (customer_id, order_date, warehouse_id, amount)
               VALUES (1001, '2018-01-10', 107, 100.30);
cqlsh:example> INSERT INTO orders (customer_id, order_date, warehouse_id, amount)
               VALUES (1002, '2018-01-11', 102, 50.45);
cqlsh:example> INSERT INTO orders (customer_id, order_date, warehouse_id, amount)
               VALUES (1001, '2018-04-09', 102, 20.25);
cqlsh:example> INSERT INTO orders (customer_id, order_date, warehouse_id, amount)
               VALUES (1003, '2018-04-09', 108, 200.80);

Query by the partition column customer_id in the table

cqlsh:example> SELECT SUM(amount) FROM orders WHERE customer_id = 1001 AND order_date >= '2018-01-01';
  sum(amount)
-------------
      120.55

Query by the partition column order_date in the index orders_by_date

cqlsh:example> SELECT SUM(amount) FROM orders WHERE order_date = '2018-04-09';
 sum(amount)
-------------
      221.05

Query by the partition column warehouse_id column in the index orders_by_warehouse

cqlsh:example> SELECT SUM(amount) FROM orders WHERE warehouse_id = 102 AND order_date >= '2018-01-01';
 sum(amount)
-------------
        70.7

Create a table with a unique index

You can do this as shown below.

cqlsh:example> CREATE TABLE emp (enum INT primary key,
                                 lastname VARCHAR,
                                 firstname VARCHAR,
                                 userid VARCHAR)
               WITH transactions = { 'enabled' : true };
cqlsh:example> CREATE UNIQUE INDEX emp_by_userid ON emp (userid);

Insert values into the table and verify no duplicate userid is inserted

cqlsh:example> INSERT INTO emp (enum, lastname, firstname, userid)
               VALUES (1001, 'Smith', 'John', 'jsmith');
cqlsh:example> INSERT INTO emp (enum, lastname, firstname, userid)
               VALUES (1002, 'Smith', 'Jason', 'jsmith');
InvalidRequest: Error from server: code=2200 [Invalid query] message="SQL error: Execution Error. Duplicate value disallowed by unique index emp_by_userid
INSERT INTO emp (enum, lastname, firstname, userid)
       ^^^^
VALUES (1002, 'Smith', 'Jason', 'jsmith');
 (error -300)"
cqlsh:example> INSERT INTO emp (enum, lastname, firstname, userid)
               VALUES (1002, 'Smith', 'Jason', 'jasmith');
cqlsh:example> SELECT * FROM emp;
 enum | lastname | firstname | userid
------+----------+-----------+---------
 1002 |    Smith |     Jason | jasmith
 1001 |    Smith |      John |  jsmith

See also

CREATE TABLE DROP INDEX Other CQL Statements

  • Synopsis
  • Syntax
    • Diagram
    • Grammar
  • Semantics
    • PARTITION KEY
    • CLUSTERING KEY
    • INCLUDED COLUMNS
    • UNIQUE INDEX
  • Examples
    • Create a table to be indexed
    • Create an index for query by the order_date column
    • Create an index for query by the warehouse_id column
    • Insert some data
    • Query by the partition column customer_id in the table
    • Query by the partition column order_date in the index orders_by_date
    • Query by the partition column warehouse_id column in the index orders_by_warehouse
    • Create a table with a unique index
    • Insert values into the table and verify no duplicate userid is inserted
  • See also
Ask our community
  • Slack
  • Github
  • Forum
  • StackOverflow
Yugabyte
Contact Us
Copyright © 2017-2020 Yugabyte, Inc. All rights reserved.