CODEWITHSUNDEEP
indexingclickhousesecondary-indexes
Divyarao
Divyarao

Reputation: 133

Creating index on existing table in Clickhouse

I am trying to add index on an existing table , with the below syntax.

create table contact_in..... ( ....... domain string, topic string, category string ...... ...... ..... ..... ) ENGINE = MergeTree PARTITION BY category ORDER BY (topic, domain) SETTINGS index_granularity = 8192

I want to create an index on the topic column (granularity is 6020)

tried syntax from the documentation but unable to understand since there is no examples explaining the fields in it.

Can somebody quickly help me with this please.

Upvotes: 5

Views: 22127

Answers (3)

supernova
supernova

Reputation: 2070

Adding an index

This will add metadata to your table. Old data is not indexed! New data will be indexed! Operation is lightweight.

ALTER TABLE [db].name ADD INDEX name expression TYPE type GRANULARITY value [FIRST|AFTER name]

Drop an index

Metadata and index files are deleted. Lightweight operation as this is easy for the DB.

ALTER TABLE [db].name DROP INDEX name

Materializing an index

This recreate the mentioned index. All data in table will be indexed. Normally you'll run this after adding an index to the table for including pre-existing data to the index.

ALTER TABLE [db.]table MATERIALIZE INDEX name IN PARTITION partition_name

Rebuilding all indexes

You can enforce recreation of all indexes with all table data by optimizing the table:

OPTIMIZE TABLE [db].name FINAL;

Testing Indexes

Clickhouse doesn't have query hints but settings, which is similar and can be added to any query. There's some settings for controlling index:

use_skip_indexes - indexes during query execution (>=v21.11). Possible values:

  • 0 — Disabled
  • 1 — Enabled (DEFAULT)

force_data_skipping_indices Disables query execution if passed data skipping indices wasn't used (>=v20.6.8.5).

Examples:

SELECT * FROM my_table WHERE my_column=1 SETTINGS use_skip_indexes=0;
SELECT * FROM my_table WHERE my_column=1 SETTINGS force_data_skipping_indices='my_index_name';

-- if index is not exiting DB will throw an error:
SELECT * FROM my_table WHERE my_column=1 SETTINGS force_data_skipping_indices='my_non_existing_index_name';
-- > DB::Exception: Index `my_non_existing_index_name` is not used and setting 'force_data_skipping_indices' contains it

Clickhouse docs

Upvotes: 9

Andrew Berezin
Andrew Berezin

Reputation: 86

I'll leave it here in case someone will be looking for same info.
Syntax is:

ALTER TABLE  
[db name].[table name]  
ADD INDEX  
[any name for index]([columns to use])  
TYPE [index type: minmax, set, ngrambf_v1, tokenbf_v1 or bloom_filter - more info below]  
GRANULARITY [you can use default value - 8192]

More info about index types here

Upvotes: 5

Anatoly Rugalev
Anatoly Rugalev

Reputation: 1168

Yes, you can.

The following operations are available:

ALTER TABLE [db].name ADD INDEX name expression TYPE type GRANULARITY value AFTER name [AFTER name2] - Adds index description to tables metadata.

ALTER TABLE [db].name DROP INDEX name - Removes index description from tables metadata and deletes index files from disk.

ALTER TABLE [db.]table MATERIALIZE INDEX name IN PARTITION partition_name - The query rebuilds the secondary index name in the partition partition_name. Implemented as a mutation.

https://clickhouse.tech/docs/en/sql-reference/statements/alter/index/

Upvotes: 2

Related Questions