What types of indexes exist in SQL?

• B-Tree (B-tree): The most common type. Suitable for most read operations, including WHERE, LIKE, ORDER BY.

  CREATE INDEX idx_column ON table_name (column_name);
  

• Hash: Optimized for exact matches (=). Does not support range queries or sorting.

  -- Syntax example, may vary depending on the DBMS
  CREATE INDEX idx_column ON table_name USING HASH (column_name);
  

• Full-Text: Used for text search in large text fields (VARCHAR, TEXT).

  -- Syntax example in MySQL
  CREATE FULLTEXT INDEX idx_text ON table_name (text_column);
  -- Syntax example in PostgreSQL
  CREATE INDEX idx_text ON table_name USING GIN (to_tsvector('russian', text_column));
  

• Spatial: For indexing geographic data (points, lines, polygons). Used with data types like GEOMETRY or GEOGRAPHY.

  -- Syntax example in MySQL
  CREATE SPATIAL INDEX idx_geo ON table_name (spatial_column);
  -- Syntax example in PostgreSQL
  CREATE INDEX idx_geo ON table_name USING GIST (spatial_column);
  

• GIN (Generalized Inverted Index) and GiST (Generalized Search Tree) in PostgreSQL: Flexible index types suitable for various tasks, including full-text search, array indexing, and JSON.

• Clustered: Defines the physical order of rows in a table. A table can have only one clustered index. Often based on the primary key.

  -- When declaring a primary key, a clustered index is often created
  CREATE TABLE table_name (
      id INT PRIMARY KEY CLUSTERED, -- In SQL Server
      ...
  );
  

• Non-Clustered: A separate data structure storing pairs "column value(s) + pointer to data row". A table can have multiple non-clustered indexes.

Comparison:

Index Type	Application	Operations
B-Tree	General, most data types	=, <, >, <=, >=, BETWEEN, LIKE (prefix)
Hash	Exact match (=)	=
Full-Text	Text search	MATCH AGAINST (MySQL), to_tsquery (PostgreSQL)
Spatial	Geographic data	Spatial functions
Clustered	Physical ordering of rows	Accelerates range queries
Non-Clustered	Speeds up search on indexed columns	=, <, >, <=, etc.