Creating and deleting secondary indexes

• Creating a secondary index
• Deleting a secondary index

The table index command lets you create and delete secondary indexes:

ydb [connection options] table index [subcommand] [options]

where [connection options] are database connection options

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

Creating a secondary index

A secondary index is created with table index add:

ydb [connection options] table index add <sync_async> <table> \
  --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 asynchronous updates.

<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.

--cover STR: Optional parameter that sets the structure of cover columns for the index. Their values won’t be included in the index key, but will be copied to the entries in the index to get their values when searching by index without having to access the table.

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 operation get 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 article on YQL 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 series table from the article on YQL in the “Getting started” section:

ydb -p db1 table index add global-async series \
  --index-name idx_rel_title --columns release_date,title --cover series_info

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

┌──────────────────────────────────┬───────┬────────┐
| id                               | ready | status |
├──────────────────────────────────┼───────┼────────┤
| 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:

┌──────────────────────────────────┬───────┬─────────┬───────┬──────────┬─────────────────┬───────────┐
| id                               | ready | status  | state | progress | table           | index     |
├──────────────────────────────────┼───────┼─────────┼───────┼──────────┼─────────────────┼───────────┤
| ydb://buildindex/7?id=2814749869 | true  | SUCCESS | Done  | 100.00%  | /local/episodes | idx_aired |
└──────────────────────────────────┴───────┴─────────┴───────┴──────────┴─────────────────┴───────────┘

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

ydb -p db1 operation forget ydb://buildindex/7?id=2814749869

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.

Deleting the idx_aired index built in the above index creation example from the episodes table:

ydb -p db1 table index drop episodes --index-name idx_aired