Creating and deleting secondary indexes

• Creating a secondary index

where [connection options] are database connection options

For information about the purpose and use of secondary indexes for app development, see in the “Recommendations” section.

A secondary index is created with table index add:

  --index-name STR --columns STR [--cover STR]

Parameters:

<sync_async>: Secondary index type. Set global_sync to build an index with synchronous updates or global_async for an index with .

<table>: Path and name of the table that the index is being built for.

--index-name STR: Required parameter that sets the index name. We recommend setting such index names that clearly indicate which columns they include. Index names are unique in the context of a table.

--columns STR: Required parameter that sets the structure and order of columns included in the index key. A list of comma-separated column names, without a space. The index key will consist of these columns with the columns of the table primary key added.

If the command is successful, a background build index operation is run and the operation ID is returned in the id field with semigraphics formatting to further get information about its status with the command. To abort an incomplete build index operation, run the operation cancel command.

Once the index build is either completed or aborted, you can delete the build operation record by running the operation forget command.

To get information about the status of all build index operations, run the operation list buildindex command.

Examples

The examples use a profile named db1. For information about how to create it, see the Getting started with the YDB CLI article in the “Getting started “ section.

Adding a synchronous index by the air_date column to the episodes table from the in the “Getting started” section:

ydb -p db1 table index add global-sync episodes \
  --index-name idx_aired --columns air_date

Adding an asynchronous index by the release_date and title columns along with copying to the index the series_info column value for the table from the article on YQL in the “Getting started” section:

Output (the ID of the operation when it’s actually run will be different):

┌──────────────────────────────────┬───────┬────────┐
├──────────────────────────────────┼───────┼────────┤
| ydb://buildindex/7?id=2814749869 | false |        |
└──────────────────────────────────┴───────┴────────┘

Getting the operation status (use the actual operation ID):

ydb -p db1 operation get ydb://buildindex/7?id=281474976866869

Returned value:

Deleting information about the build index operation (use the actual operation ID):

Deleting a secondary index

A secondary index is deleted with table index drop:

ydb [connection options] table index drop <table> --index-name STR

Example

The examples use a profile named db1. For information about how to create it, see the Getting started with the YDB CLI article in the “Getting started “ section.