Can my DB?

-- B-Tree (default): ordered data, range queries, equality
CREATE INDEX idx_btree ON orders (customer_id);

-- Hash: equality-only lookups, smaller than B-Tree for equality
CREATE INDEX idx_hash ON sessions USING hash (session_token);

-- GIN: inverted index for arrays, JSONB, full-text search
CREATE INDEX idx_gin_tags ON posts USING gin (tags);
CREATE INDEX idx_gin_json ON events USING gin (payload jsonb_path_ops);
CREATE INDEX idx_gin_fts  ON docs  USING gin (to_tsvector('english', body));

-- GiST: geometric types, range types, custom operator classes
CREATE INDEX idx_gist_geo  ON locations USING gist (coordinates);
CREATE INDEX idx_gist_range ON reservations USING gist (during);

-- SP-GiST: space-partitioned structures (quadtree, kd-tree, trie)
CREATE INDEX idx_spgist ON points USING spgist (location);

-- BRIN: block range index, tiny footprint for insert-ordered data
CREATE INDEX idx_brin ON logs USING brin (created_at);
CREATE INDEX idx_brin_custom ON logs USING brin (created_at) WITH (pages_per_range = 32);

-- Bloom filter: probabilistic multi-column equality testing
CREATE INDEX idx_bloom ON t USING bloom (col1, col2, col3);

• B-Tree is the default and most versatile: supports =, <, >, <=, >=, BETWEEN, IN, IS NULL, LIKE 'prefix%'.
• Hash indexes are smaller and faster for pure equality lookups but do not support range queries or sorting.
• GIN (Generalized Inverted Index) is essential for JSONB containment (@>), array overlap (&&), and full-text search (@@).
• GiST (Generalized Search Tree) is used by PostGIS for spatial queries and supports custom operator classes.
• BRIN stores only the min and max values per range of heap pages, which keeps it very small (hundreds of bytes vs. gigabytes for B-Tree) for monotonically increasing columns like timestamps.
• SP-GiST (Space-Partitioned GiST) supports space-partitioned data structures like quadtrees, kd-trees, and tries.
• The USING clause in CREATE INDEX selects the index type; B-Tree is the default if omitted.

-- B-Tree (default for InnoDB): equality, range, prefix
CREATE INDEX idx_email ON users (email);

-- Hash: only available for MEMORY storage engine tables
CREATE TABLE cache (
  key_col VARCHAR(255) PRIMARY KEY,
  value   TEXT
) ENGINE=MEMORY;
CREATE INDEX idx_hash ON cache USING HASH (key_col);

-- FULLTEXT: natural language and boolean full-text search (InnoDB, MyISAM)
CREATE FULLTEXT INDEX idx_fts ON articles (title, body);
SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('database indexing' IN BOOLEAN MODE);

-- SPATIAL: for geometry columns (MyISAM and InnoDB with spatial data types)
CREATE SPATIAL INDEX idx_spatial ON locations (coordinates);

-- Multi-value index: index each element of a JSON array (MySQL 8.0.17+)
CREATE INDEX idx_tags ON posts ((CAST(tags->>'$' AS UNSIGNED ARRAY)));

• InnoDB uses B-Tree for all regular indexes. The primary key index is a clustered index, so the table data is physically organized by the primary key.
• FULLTEXT indexes support IN NATURAL LANGUAGE MODE (default) and IN BOOLEAN MODE searches.
• Hash indexes are only available for the MEMORY (in-memory) storage engine, not InnoDB.
• SPATIAL indexes require columns with GEOMETRY or sub-type data types and use R-Tree internally.
• Multi-value indexes (MySQL 8.0.17+) index each element of a JSON array, enabling efficient array membership queries.

-- B-Tree (InnoDB default)
CREATE INDEX idx_email ON users (email);

-- FULLTEXT
CREATE FULLTEXT INDEX idx_fts ON articles (title, body);

-- SPATIAL (R-Tree)
CREATE SPATIAL INDEX idx_geo ON locations (coordinates);

-- Hash (MEMORY/Aria engines)
CREATE TABLE cache (k VARCHAR(100) PRIMARY KEY) ENGINE=MEMORY;
CREATE INDEX idx_hash ON cache USING HASH (k);

-- Mroonga: Groonga-based full-text search engine for Japanese text
-- (requires Mroonga storage engine plugin)
CREATE TABLE docs (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  body TEXT,
  FULLTEXT INDEX (body) COMMENT 'parser "TokenBigram"'
) ENGINE=Mroonga;

• MariaDB supports the same base index types as MySQL (B-Tree, FULLTEXT, SPATIAL, Hash for MEMORY engine).
• The Aria storage engine (MariaDB's crash-safe MyISAM replacement) supports B-Tree and FULLTEXT indexes.
• Mroonga is a MariaDB storage engine plugin that integrates the Groonga full-text search engine, offering enhanced Japanese and n-gram tokenization.
• ColumnStore storage engine uses columnar storage with its own internal indexing for analytical workloads.
• Spider storage engine for federated/sharded tables creates indexes on remote nodes.

-- B-Tree (default)
CREATE INDEX idx_email ON users (email);

-- Bitmap: low-cardinality columns in data warehouses
CREATE BITMAP INDEX idx_status ON orders (status);

-- Function-based: index on an expression
CREATE INDEX idx_email_upper ON users (UPPER(email));

-- Reverse key: prevents right-side contention on sequence-based keys
CREATE INDEX idx_id_reverse ON orders (id) REVERSE;

-- Partitioned local index: one index partition per table partition
CREATE INDEX idx_date ON sales (sale_date) LOCAL;

-- Bitmap join index: pre-join bitmap index across tables
CREATE BITMAP INDEX idx_region
  ON sales (customers.region)
  FROM sales, customers
  WHERE sales.customer_id = customers.id;

-- Oracle Text (full-text) index
CREATE INDEX idx_fts ON articles (body) INDEXTYPE IS CTXSYS.CONTEXT;

• Bitmap indexes store a bitstring per distinct value, which makes them very efficient for AND/OR/NOT combinations on low-cardinality columns (status, region, gender) in data warehouse queries.
• Bitmap indexes are not suitable for OLTP; they require row-level locking of the entire bitmap entry on DML, causing severe contention.
• Reverse key indexes byte-reverse the key to distribute sequential inserts across leaf blocks, preventing right-side contention.
• Bitmap join indexes pre-join data from multiple tables into a single bitmap structure, useful for star-schema queries.
• Oracle Text (CTXSYS.CONTEXT) provides full-text indexing with linguistic analysis, thesaurus support, and proximity search.
• IOT (Index-Organized Tables) store all row data in a B-Tree, eliminating the separate heap segment.

-- Non-clustered B-Tree (default)
CREATE INDEX idx_email ON users (email);

-- Clustered B-Tree: the table is physically organized by this index
CREATE CLUSTERED INDEX idx_pk ON orders (id);
-- Note: PRIMARY KEY creates a clustered index by default in SQL Server

-- Non-clustered columnstore: add analytical acceleration to an OLTP table
CREATE NONCLUSTERED COLUMNSTORE INDEX idx_cs ON orders (customer_id, order_date, total);

-- Clustered columnstore: pure analytical/DW table (replaces the heap)
CREATE CLUSTERED COLUMNSTORE INDEX idx_cs ON fact_sales;

-- Hash index: only for memory-optimized (In-Memory OLTP) tables
CREATE TABLE hot_sessions (
  session_id UNIQUEIDENTIFIER NOT NULL,
  data       NVARCHAR(MAX),
  INDEX idx_hash HASH (session_id) WITH (BUCKET_COUNT = 1000000)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

-- Spatial index: for geometry/geography columns
CREATE SPATIAL INDEX idx_geo ON locations (coordinates)
  USING GEOMETRY_GRID WITH (BOUNDING_BOX = (0, 0, 100, 100));

• Clustered indexes define the physical row order of the table; each table can have only one clustered index (usually the primary key).
• Columnstore indexes compress data column-by-column and process queries in vectorized batches, delivering 10-100x speedup for analytical aggregations (COUNT, SUM, AVG, GROUP BY).
• Non-clustered Columnstore can coexist with B-Tree indexes on the same OLTP table for mixed workloads (Operational Analytics).
• Hash indexes for In-Memory OLTP tables provide O(1) equality lookup with no range scan support.
• Full-Text indexes (CREATE FULLTEXT INDEX) enable linguistic full-text search via CONTAINS() and FREETEXT() predicates.
• XML indexes (PRIMARY XML INDEX, SECONDARY XML INDEX) optimize XQuery and XPath expressions on XML columns.

-- B-Tree (only index type for regular tables)
CREATE INDEX idx_email ON users (email);

-- Multi-column B-Tree
CREATE INDEX idx_name ON users (last_name, first_name);

-- R*Tree extension: spatial and multi-dimensional range queries
-- (requires FTS5 / R*Tree module, usually compiled in)
CREATE VIRTUAL TABLE geo_index USING rtree(
  id,
  min_lat, max_lat,
  min_lon, max_lon
);
INSERT INTO geo_index VALUES (1, 37.7, 37.8, -122.5, -122.4);
SELECT id FROM geo_index
WHERE min_lat > 37.6 AND max_lat < 37.9
  AND min_lon > -122.6 AND max_lon < -122.3;

• SQLite uses B-Tree for all regular indexes; there is no USING clause in CREATE INDEX syntax.
• The R*Tree extension module (usually compiled into standard SQLite distributions) provides multi-dimensional spatial range queries via virtual tables.
• FTS5 (full-text search) is implemented as a virtual table extension: CREATE VIRTUAL TABLE docs USING fts5(title, body).
• SQLite does not have Hash, GIN, BRIN, or other specialized index types.
• All regular indexes, including UNIQUE indexes, use the same B-Tree implementation.

-- Redshift has no user-defined indexes.
-- Sort keys define physical row order for zone map pruning:
CREATE TABLE orders (
  id          BIGINT,
  customer_id INT,
  order_date  DATE,
  total       DECIMAL(10,2)
)
DISTKEY (customer_id)
COMPOUND SORTKEY (customer_id, order_date);

-- Zone maps (automatic): each 1MB disk block stores min/max per column.
-- Queries with WHERE clauses matching the sort key skip irrelevant blocks.

-- Analyze sort key effectiveness:
SELECT tbl, col, sortkey
FROM   svv_table_info
WHERE  table = 'orders';

• Redshift uses zone maps (automatic min/max metadata per ~1MB block per column) for block-level pruning.
• Sort keys (COMPOUND or INTERLEAVED) control physical row ordering; queries aligned with the sort key skip entire block groups.
• Distribution keys (DISTKEY) determine how rows are distributed across compute nodes for join colocation.
• ENCODE (compression encoding per column) is the main storage optimization: AZ64, ZSTD, LZO, BYTEDICT, RUNLENGTH, DELTA.
• No B-Tree, bitmap, hash, or other user-defined index types exist in Redshift.

-- B-Tree (default)
CREATE INDEX idx_email ON users (email);

-- Bidirectional B-Tree (for DESC and ASC queries on the same column)
CREATE INDEX idx_date_desc ON orders (order_date DESC);

-- Clustering index: data is physically ordered by this index
CREATE INDEX idx_cluster ON orders (customer_id) CLUSTER;

-- MDC (Multi-Dimensional Clustering): physical clustering on multiple columns
-- (defined at table creation or ALTER TABLE)
CREATE TABLE fact_sales (
  year_id   INT,
  region_id INT,
  amount    DECIMAL(12,2)
) ORGANIZE BY DIMENSIONS (year_id, region_id);

-- Text Search index (requires DB2 Text Search feature)
-- CREATE INDEX idx_fts ON articles (body) FOR TEXT;
-- (CLI: db2ts CREATE INDEX FOR TEXT ON mydb.articles (body))

-- BLU Acceleration: column-organized table (analytical)
CREATE TABLE fact_orders (
  id          BIGINT,
  customer_id INT,
  total       DECIMAL(12,2)
) ORGANIZE BY COLUMN;

• DB2 B-Tree indexes are the standard type for OLTP queries.
• Clustering indexes physically order the table data by the index key, reducing I/O for range scans (one clustering index per table).
• MDC (Multi-Dimensional Clustering) maintains physical clustering on multiple dimension columns simultaneously using extents.
• ORGANIZE BY COLUMN (BLU Acceleration) converts a table to columnar storage, similar to SQL Server Columnstore, for analytical workloads.
• DB2 Text Search uses an Apache Lucene-based index for full-text queries.
• Spatial Extender (optional component) provides R-Tree based spatial indexes for geographic data types.

-- Snowflake has no user-defined traditional index types.
-- Micro-partition pruning (automatic zone maps per column per partition):
-- Enabled automatically for all tables.

-- Cluster keys co-locate related rows for better pruning:
ALTER TABLE orders CLUSTER BY (order_date, customer_id);

-- Check clustering depth and effectiveness:
SELECT SYSTEM$CLUSTERING_INFORMATION('orders', '(order_date, customer_id)');

-- Search Optimization Service: accelerates selective equality/range lookups
ALTER TABLE users ADD SEARCH OPTIMIZATION ON EQUALITY(email);
ALTER TABLE docs  ADD SEARCH OPTIMIZATION ON SUBSTRING(body);

• Snowflake uses automatic micro-partitioning: each ~16MB compressed partition stores min/max metadata per column (zone maps).
• Queries with selective WHERE clauses automatically skip irrelevant micro-partitions without any user-defined indexes.
• Cluster keys instruct Snowflake to physically reorganize micro-partitions by the specified column(s) for better pruning.
• Search Optimization Service is an accelerator for selective equality, range, and substring queries on specific columns.
• No B-Tree, bitmap, hash, GIN, BRIN, or other traditional user-defined index types exist.

-- Partitioned table: time-based row pruning
CREATE TABLE orders
PARTITION BY DATE(order_date)
AS SELECT * FROM staging.orders;

-- Clustered table: physical co-location for column pruning
CREATE TABLE orders
PARTITION BY DATE(order_date)
CLUSTER BY customer_id, status
AS SELECT * FROM staging.orders;

-- Search Index: managed index for SEARCH() function
CREATE SEARCH INDEX idx ON mydataset.orders (ALL COLUMNS);

-- Query using SEARCH index
SELECT * FROM orders
WHERE SEARCH(orders, 'urgent');

-- Check search index status
SELECT * FROM mydataset.INFORMATION_SCHEMA.SEARCH_INDEXES;

• BigQuery manages its own columnar internal storage; there are no user-defined B-Tree, bitmap, or hash indexes.
• Partitioned tables restrict query scans to specific time or range partitions, reducing bytes billed.
• Clustered tables physically co-locate rows with similar values in up to 4 clustering columns, enabling block-level pruning.
• Search Indexes (CREATE SEARCH INDEX, GA 2023) support the SEARCH() function for full-text and substring lookups across all or selected columns.
• Vector indexes (CREATE VECTOR INDEX) are available for nearest-neighbor search using the VECTOR_SEARCH() function.
• All traditional index management (vacuum, rebuild, analyze) is handled automatically by BigQuery.