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
