Indexes

Index commands are used to create, build, inspect, and drop indexes on Lakehouse tables to accelerate specific query patterns.

Index Types

Lakehouse provides three index types — choose based on your query pattern:

Index Type	Applicable Queries	Typical Use Case
Bloom Filter Index	Equality filters on high-cardinality columns (`=`, `IN`)	Queries by user ID, order number, phone number
Inverted Index	Full-text search on text fields, supports Chinese tokenization	Log search, comment retrieval, keyword matching
Vector Index	Vector similarity search (ANN)	Semantic search, image similarity, recommendation systems

⚠️ Note: A newly created index only applies to data written after the index is created. For existing data, run BUILD INDEX to backfill.

In This Chapter

Page	Description
Bloom Filter Index	Bloom filter principles, creation, and usage
CREATE BLOOMFILTER INDEX	Full syntax for creating a bloom filter index
Inverted Index	Inverted index principles, analyzer configuration, and usage
CREATE INVERTED INDEX	Full syntax for creating an inverted index
Vector Index	Vector search principles, index types, and usage
CREATE VECTOR INDEX	Full syntax for creating a vector index
BUILD INDEX	Build an index for existing data
DESC INDEX	View index details
DROP INDEX	Drop an index
SHOW INDEX	List all indexes on a table

Common Operations

Create a Bloom Filter Index

-- Create a bloom filter on a high-cardinality column to accelerate equality queries CREATE BLOOMFILTER INDEX idx_user_id ON TABLE orders(user_id);

Create an Inverted Index

-- Create an inverted index on a text column to support full-text search CREATE INVERTED INDEX idx_content ON TABLE app_logs(content) PROPERTIES('analyzer' = 'chinese');

Build an Index for Existing Data

-- A new index does not cover historical data; trigger a build manually BUILD INDEX idx_content ON app_logs;

View and Drop Indexes

-- List all indexes on a table SHOW INDEX IN orders; -- Drop an index DROP INDEX idx_user_id;

Document	Description
SQL Commands Overview	Categorized navigation for all SQL commands
Index Usage Guide	Complete examples for creating and validating indexes by scenario
Full-Text Search Guide	Detailed usage of inverted index full-text search
Lakehouse Index Best Practices	Index selection, maintenance, and performance tuning recommendations