JSON

Database Index Types - JSON

Database indexes are data structures used to speed up data retrieval. Various types exist depending on the use case, including B-Tree indexes, Hash indexes, and Bitmap indexes, each optimized for specific query patterns and data characteristics. Selecting the appropriate index is a crucial element in database performance optimization.

database index B-Tree Hash Bitmap SQL performance optimization

[
  {
    "code": "1",
    "slug": "b-tree-index",
    "name": "B-Tree Index",
    "description": "The most commonly used balanced tree structure index.",
    "category": "General-purpose Index",
    "useCases": [
      "Equality searches",
      "Range queries",
      "Sorting operations",
      "Primary keys",
      "Foreign keys"
    ],
    "supportedDatabases": [
      "MySQL",
      "PostgreSQL",
      "Oracle",
      "SQL Server",
      "MongoDB"
    ]
  },
  {
    "code": "2",
    "slug": "hash-index",
    "name": "Hash Index",
    "description": "An index providing constant-time access using hash functions.",
    "category": "Key-value Index",
    "useCases": [
      "Equality searches",
      "Point lookups",
      "Transaction processing",
      "Key-value access"
    ],
    "supportedDatabases": [
      "Redis",
      "MySQL (MEMORY)",
      "PostgreSQL",
      "Some NoSQL databases"
    ]
  },
  {
    "code": "3",
    "slug": "bitmap-index",
    "name": "Bitmap Index",
    "description": "An index using bit arrays optimized for low-cardinality columns.",
    "category": "Analytical Index",
    "useCases": [
      "Low-cardinality columns",
      "Data warehousing",
      "Complex filtering",
      "Read-heavy environments"
    ],
    "supportedDatabases": [
      "Oracle",
      "PostgreSQL (extensions)",
      "Data warehouse systems"
    ]
  },
  {
    "code": "4",
    "slug": "full-text-index",
    "name": "Full-Text Index",
    "description": "An index specialized for searching unstructured text data.",
    "category": "Text Search Index",
    "useCases": [
      "Full-text search",
      "Partial match search",
      "Similar word search",
      "Document search"
    ],
    "supportedDatabases": [
      "MySQL",
      "PostgreSQL",
      "SQL Server",
      "Elasticsearch"
    ]
  },
  {
    "code": "5",
    "slug": "vector-index",
    "name": "Vector Index",
    "description": "An index specialized for similarity search of high-dimensional vectors.",
    "category": "AI/ML Index",
    "useCases": [
      "Similarity search",
      "Image search",
      "Recommendation systems",
      "Semantic search"
    ],
    "supportedDatabases": [
      "PostgreSQL (pgvector)",
      "Pinecone",
      "Milvus",
      "Weaviate"
    ]
  },
  {
    "code": "6",
    "slug": "spatial-index",
    "name": "Spatial Index",
    "description": "An index specialized for searching geospatial data.",
    "category": "Geospatial Index",
    "useCases": [
      "Neighbor search",
      "Range search",
      "Distance calculation",
      "Geographic information search"
    ],
    "supportedDatabases": [
      "PostgreSQL (PostGIS)",
      "MySQL",
      "Oracle Spatial",
      "MongoDB"
    ]
  }
]