In ApsaraDB for SelectDB, bitmap indexes accelerate query performance by allowing the database engine to filter rows using bitwise operations instead of scanning every row. Use bitmap indexes on columns that appear frequently in equality and range filter conditions to reduce the volume of data read during query execution.
Create a bitmap index
CREATE INDEX [IF NOT EXISTS] <index_name> ON <table_name> (<column>) USING BITMAP;| Parameter | Description |
|---|---|
index_name |
The name of the bitmap index. |
table_name |
The name of the table on which to create the bitmap index. |
column |
The name of the column on which to create the bitmap index. |
View index information
To list the indexes on a table, run:
SHOW INDEX FROM [db_name.]<table_name>;Delete a bitmap index
DROP INDEX [IF EXISTS] <index_name> ON [db_name.]<table_name>;Limitations
Scope
-
Bitmap indexes apply to a single column only. Multi-column bitmap indexes are not supported.
-
Bitmap indexes take effect only on data stored in Segment V2 format. When you create a bitmap index on a table, the table's storage format is automatically converted to Segment V2.
Supported table models and columns
| Table model | Supported columns |
|---|---|
| Duplicate Key model | All columns |
| Unique Key model | All columns |
| Aggregate Key model | Key columns only |
Supported data types
| Category | Data types |
|---|---|
| Numeric | TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DECIMAL |
| String | CHAR, VARCHAR |
| Date and time | DATE, DATETIME |
| Other | BOOL |