Defines a new search index for an existing table.

Defines a new search index for an existing table. Automatically creates the search index schema and configuration, then generates an index.
Restriction: Command available only on DSE Search nodes. Running search index management commands on large datasets can take longer than the CQLSH default timeout of 10 minutes. Increase the CQLSH client timeout as required.
Important: This command runs with a consistency level of LOCAL_QUORUM because this command is only executed in the current datacenter.


CREATE SEARCH INDEX [ IF NOT EXISTS ] ON [keyspace_name.]table_name
  [ WITH [ COLUMNS column_list { option : value } [ , ... ] ]
  [ [ AND ] PROFILES profile_name [ , ... ] ]
  [ [ AND ] CONFIG { option : value } [ , ... ] ]
  [ [ AND ] OPTIONS { option : value } [ , ... ] ] ] ;

If the CREATE SEARCH INDEX statement specifies no options all columns are indexed using the default values.

Table 1. Legend
Syntax conventions Description
UPPERCASE Literal keyword.
Lowercase Not literal.
Italics Variable value. Replace with a user-defined value.
[] Optional. Square brackets ( [] ) surround optional command arguments. Do not type the square brackets.
( ) Group. Parentheses ( ( ) ) identify a group to choose from. Do not type the parentheses.
| Or. A vertical bar ( | ) separates alternative elements. Type any one of the elements. Do not type the vertical bar.
... Repeatable. An ellipsis ( ... ) indicates that you can repeat the syntax element as often as required.
'Literal string' Single quotation ( ' ) marks must surround literal strings in CQL statements. Use single quotation marks to preserve upper case.
{ key : value } Map collection. Braces ( { } ) enclose map collections or key value pairs. A colon separates the key and the value.
<datatype1,datatype2> Set, list, map, or tuple. Angle brackets ( < > ) enclose data types in a set, list, map, or tuple. Separate the data types with a comma.
cql_statement; End CQL statement. A semicolon ( ; ) terminates all CQL statements.
[--] Separate the command line options from the command arguments with two hyphens ( -- ). This syntax is useful when arguments might be mistaken for command line options.
' <schema> ... </schema> ' Search CQL only: Single quotation marks ( ' ) surround an entire XML schema declaration.
@xml_entity='xml_entity_type' Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files.


EBNF syntax:
createSearchIndex ::= 'CREATE' 'SEARCH' 'INDEX' ('IF' 'NOT' 'EXISTS')?
                      'ON' tableName
                      ('WITH' indexOptions)?
tableName         ::= (keyspace '.')? table
indexOptions      ::= indexOption ('AND' indexOption)*
indexOption       ::= 'COLUMNS' columnList
                      | 'PROFILES' profileName (',' profileName)*
                      | 'CONFIG' optionMap
                      | 'OPTIONS' optionMap
columnList        ::= column (',' column)*
column            ::= (columnName | '*')('{' optionMap '}')?
optionMap         ::= '{' (optionName ':' optionValue (',' optionName ':'
                      optionValue)*)? '}'

Railroad diagram:

Figure 1. createSearchIndex
Image shows a railroad diagram for the CREATE SEARCH INDEX search CQL command
Figure 2. tableName
Image shows a railroad diagram of tableName
Figure 3. indexOptions
Image shows a railroad diagram of indexOptions
Figure 4. indexOption
Image shows a railroad diagram of indexOption
Figure 5. columnList
Image shows a railroad diagram of columnList
Figure 6. column
Image shows a railroad diagram of column
Figure 7. optionMap
Image shows a railroad diagram of optionMap


Defines which fields to include in index, sets index type, and creates non-tokenized fields for faceted search.
COLUMNS column_list { copyField : true | false }, 
column_list { docValues : true | false }, 
column_list { excluded : true | false }, 
column_list { indexed : true | false }
A comma-separated list or * (for all columns). You can include tuple fields and subfields. Any column not listed is excluded from the index by default, except PRIMARY KEY columns, which must be indexed.
  • A comma-separated list of all of the column_names, tuplefield, or tuplefield.subfield to include in the search index. When a subfield is selected for inclusion, parent fields are always included.
  • For each column in the column_list, optionally specify true or false for copyField, docValues, excluded, or indexed.
  • Asterisk (*) to select all columns
For example:
COLUMNS column_name1, column_name2, tuplefield
COLUMNS column_name1, column_name2 {copyField:true}
copyField: (true | false)
Set to true to create a new field copied from the specified columns with type StrField. Duplicates the data from the original field into the new field. Use for columns that require both search and faceting.

Default value is false.

docValues: ( true | false)
Creates a forward index on each specified column. Setting is only valid on Solr types that extend TrieField, UUIDField, and StrField. Use on columns that are sorted or grouped (faceted). Default is true for TrieField and UUIDField types and false StrField types.

Due to SOLR-7264, setting docValues to true on a boolean field in the Solr schema does not work. A workaround for boolean docValues is to use 0 and 1 with a TrieIntField.

Note: Using spaceSavings profiles disables auto generation of DocValues.
excluded: (true | false)
When using the COLUMNS option, exclude column from index:
  • true - exclude the listed columns and all fields in the columns from the index.
  • false - do not exclude the listed columns from the index. You must specify columns to include to include the columns in the index. Default when not specified.
Excluded columns are not present in HTTP query results and singlePass queries.
indexed: (true | false)
When using the COLUMNS option:
  • true - include the specified fields in the index. Default when not specified.
  • false - exclude the specified fields from the index.
Important: Non-indexed columns are present in HTTP query results and singlePass queries only if they are included in the Solr schema.xml file with the property stored=true during the Solr core creation. For more information, see Solr single-pass CQL queries.


Apply space saving options to minimize index size on initial creation. Specify spaceSavingAll or a comma separated list of profiles to apply.
PROFILES profile_name [, profile_name, ...]
Note: Profiles only apply to the initial index generation, and do not apply to the ALTER SEARCH INDEX SCHEMA command.
Applies all profiles.
Excludes the hidden partition key required for joins across tables on search queries from the index. When used table joins on search index queries are not allowed.
Indexes text fields as untokenized string fields.
Sets trie fields precisionStep to '0', allowing for greater space saving but slower querying.


Configuration options override values in the config file. The CONFIG option map can pass options with this syntax:
CONFIG { shortcut_name:value [, shortcut_name:value, ...] }
Shortcuts for configuration elements:
  • autoCommitTime Default value is 10000 ms.
  • defaultQueryField Name of the field. Default not set.
    Note: Use SET to add. Use DROP to remove.
  • directoryFactory Can be used as an alternative to the directoryFactoryClass option. The options are:
    • 'standard'
    • 'encrypted'
  • directoryFactoryClass Specifies the fully-qualified name of the directory factory. Use in place of the directoryFactory option for directory factories other than the standard or encrypted directory factory.
  • filterCacheLowWaterMark Default is 1024 MB.
  • filterCacheHighWaterMark Default is 2048 MB.
  • mergeFactor Default is 10.
  • mergeMaxThreadCount Must configure with mergeMaxMergeCount. Default is the maximum number of concurrent asynchronous indexing threads per DSE Search index as configured by max_solr_concurrency_per_core in dse.yaml.
  • mergeMaxMergeCount Must configure with mergeMaxThreadCount. The default is the calculated value:
    max(max(<maxThreadCount * 2>, <num_tokens * 8>), <maxThreadCount + 5>)
    where num_tokens is the number of token ranges to assign to the virtual node (vnode) as configured in cassandra.yaml.
  • ramBufferSize Default is 512.
  • realtime Default is false.


Request options configure the entire request. The OPTIONS map can pass options with this syntax:
OPTIONS { option:value [, option:value, ...] }
The request options are boolean values:
  • true - if the search core is unable to load due to corrupted index, recovers it by deleting and recreating the index. The deleteAll flag is set based on the recovery flag unless deleteAll is specifically set.
  • false - no recovery. Default.
  • true - reindexes the data. Keeps the current index (accepting reads) while the new index is building. Default.
  • false - does not reindex the data.
  • true - The SchemaGenerator silently ignores fields of type SpatialRecursivePrefixTreeFieldType (CQL PointType).
  • false - Do not ignore fields of type SpatialRecursivePrefixTreeFieldType (CQL PointType).


The search index is created with the wiki.solr keyspace and table, and the specified options.

Create search index if it does not exist

ON wiki.solr WITH COLUMNS id, body { excluded : false };

Create real time (RT) search index, but don't reindex the data

ON wiki.solr
WITH CONFIG { realtime:true } AND OPTIONS { reindex : false };

Create search index with transparent data encryption (TDE)

ON wiki.solr
WITH COLUMNS c1,c2 { docValues:true }
AND PROFILES spaceSavingAll, spaceSavingNoTextfield
AND CONFIG { directoryFactory:'encrypted' };

Create search index with docValues set for all columns with a supported type

ON wiki.solr
WITH COLUMNS * { docValues:true };

Create search index to specify the columns to include and exclude from index

ON wiki.solr
WITH COLUMNS field1 { indexed:true }, field2 { indexed:false };

Non-indexed columns are included in present in HTTP query results and single pass query results. To exclude, use the excluded option.

Create search index with controls for tuple and UDT fields

ON wiki.solr
WITH COLUMNS tuplefield.field1 { docValues:true };
Parent fields are included when the subfield is selected for inclusion.

Create search index to specify the columns to exclude from HTTP query results and singlePass queries

ON wiki.solr
WITH COLUMNS field1 { excluded:true }, field2 { excluded:false };
Excluded columns are not present in HTTP query results, but non-indexed columns are included.

Create search index with space saving no join option

ON wiki.solr
WITH PROFILES spaceSavingNoJoin;

The example avoids indexing the _partitionKey field. See Identifying the partition key.