0

I am searching for a long time on net. But no use. Please help or try to give some ideas how to achieve this.Now, I can get all table indexes by parsing create_table_query filed,is there any table that directly stores indexs info,like MySQL information_schema.STATISTICS


 select t.*,splitByString(' TYPE ',trim(index_column)) as index_info,
 mid(index_info[1],POSITION (index_info[1],' ') + 1 ,100) index_columns,
 index_info[2] as index_type
 from (
 select database ,name as table_name,engine,create_table_query ,total_rows, partition_key ,sorting_key ,primary_key ,sampling_key ,
 extractAll(create_table_query,'INDEX(.*?)GRANULARITY') index_arr
 from `system`.tables t where database  in ('ods','uds','efc','dss','old')
 and engine not in ('View')
 ) t 
 left ARRAY JOIN index_arr as index_column
bill
  • 5
  • 3

1 Answers1

1

Only tables with ENGINE *MergeTree family have indexes, every *MergeTree table have primary index (not unique) you can know which fields contains in primary index via SELECT * FROM system.columns WHERE is_in_primary_key=1

Also, secondary data skip indexes are available, https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/mergetree/#table_engine-mergetree-data_skipping-indexes

but unfortunately, no system.* table available to show secondary data skip indexes present in table

If you want to known which and how secondary data skip indexes used during query you can use follow SQL queries

SET send_logs_level='trace';
SELECT ... your query;

and look to

Index `index_name` has dropped X/Y granules
Slach
  • 1,672
  • 13
  • 21