MariaDB 11.8 LTS Native VECTOR INDEX (HNSW + SIMD) Blueprint

Supanote Core Team @supanote · 2 min read

plain (sql) 1 day ago · 39 lines · 4 views

Artifact

Verified Blueprint MariaDB 11.8 LTS

Build plugin-free HNSW semantic vector search in MariaDB 11.8 LTS using native VECTOR(N) columns, SIMD cosine distance, and hybrid SQL.

✓ Uses native MariaDB 11.8 LTS VECTOR data type and HNSW graph indexing with zero external extensions
✓ Tunes mhnsw_max_cache_size and ef_search for sub-10ms approximate nearest neighbor (ANN) recall
✓ Combines relational tenant/status ACID joins directly with VEC_DISTANCE_COSINE() ranking

Quick Fetch

-- MariaDB 11.8 LTS Native HNSW Vector Search & Hybrid RAG Schema
-- Requires MariaDB 11.7+ / 11.8 LTS (AVX2 / AVX-512 / ARM NEON SIMD hardware acceleration built in)

-- 1. Allocate Dedicated HNSW Graph Cache in Memory (Also set in my.cnf for persistence)
SET GLOBAL mhnsw_max_cache_size = 2147483648; -- 2 GB vector graph cache
SET SESSION mhnsw_ef_search = 64;             -- Higher = better recall, lower = faster latency

-- 2. Create Knowledge Chunks Table With Native VECTOR(768) & HNSW Cosine Index
CREATE TABLE IF NOT EXISTS kb_documents (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    workspace_id INT UNSIGNED NOT NULL,
    visibility  ENUM('public', 'internal') NOT NULL DEFAULT 'internal',
    doc_title   VARCHAR(255) NOT NULL,
    chunk_text  TEXT NOT NULL,
    embedding   VECTOR(4) NOT NULL, -- Use VECTOR(768) or VECTOR(1536) in production models
    updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_kb_workspace_vis (workspace_id, visibility),
    VECTOR INDEX idx_kb_embedding_hnsw (embedding) M=8 DISTANCE=cosine
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

-- 3. Insert Embeddings via VEC_FromText (or raw IEEE-754 float32 binary parameter bindings)
INSERT INTO kb_documents (workspace_id, visibility, doc_title, chunk_text, embedding)
VALUES
    (42, 'internal', 'Runbook: Replica Lag', 'Check parallel worker queues via SHOW SLAVE STATUS.', VEC_FromText('[0.12, -0.44, 0.81, 0.09]')),
    (42, 'internal', 'Runbook: TLS Rotation', 'Reload certificates online with FLUSH SSL.', VEC_FromText('[0.77, 0.19, -0.22, 0.54]'));

-- 4. Nearest-Neighbor Semantic Query With Relational Pre-Filter
-- ORDER BY VEC_DISTANCE_COSINE(...) + LIMIT triggers the HNSW index scan
SELECT
    id,
    doc_title,
    chunk_text,
    ROUND(1 - VEC_DISTANCE_COSINE(embedding, VEC_FromText('[0.11, -0.42, 0.79, 0.10]')), 4) AS cosine_similarity
FROM kb_documents
WHERE workspace_id = 42
  AND visibility = 'internal'
ORDER BY VEC_DISTANCE_COSINE(embedding, VEC_FromText('[0.11, -0.42, 0.79, 0.10]')) ASC
LIMIT 10;

1-- MariaDB 11.8 LTS Native HNSW Vector Search & Hybrid RAG Schema

2-- Requires MariaDB 11.7+ / 11.8 LTS (AVX2 / AVX-512 / ARM NEON SIMD hardware acceleration built in)

4-- 1. Allocate Dedicated HNSW Graph Cache in Memory (Also set in my.cnf for persistence)

5SET GLOBAL mhnsw_max_cache_size = 2147483648; -- 2 GB vector graph cache

6SET SESSION mhnsw_ef_search = 64; -- Higher = better recall, lower = faster latency

8-- 2. Create Knowledge Chunks Table With Native VECTOR(768) & HNSW Cosine Index

9CREATE TABLE IF NOT EXISTS kb_documents (

10 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

11 workspace_id INT UNSIGNED NOT NULL,

12 visibility ENUM('public', 'internal') NOT NULL DEFAULT 'internal',

13 doc_title VARCHAR(255) NOT NULL,

14 chunk_text TEXT NOT NULL,

15 embedding VECTOR(4) NOT NULL, -- Use VECTOR(768) or VECTOR(1536) in production models

16 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

17 INDEX idx_kb_workspace_vis (workspace_id, visibility),

18 VECTOR INDEX idx_kb_embedding_hnsw (embedding) M=8 DISTANCE=cosine

19) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

21-- 3. Insert Embeddings via VEC_FromText (or raw IEEE-754 float32 binary parameter bindings)

22INSERT INTO kb_documents (workspace_id, visibility, doc_title, chunk_text, embedding)

23VALUES

24 (42, 'internal', 'Runbook: Replica Lag', 'Check parallel worker queues via SHOW SLAVE STATUS.', VEC_FromText('[0.12, -0.44, 0.81, 0.09]')),

25 (42, 'internal', 'Runbook: TLS Rotation', 'Reload certificates online with FLUSH SSL.', VEC_FromText('[0.77, 0.19, -0.22, 0.54]'));

27-- 4. Nearest-Neighbor Semantic Query With Relational Pre-Filter

28-- ORDER BY VEC_DISTANCE_COSINE(...) + LIMIT triggers the HNSW index scan

29SELECT

30 id,

31 doc_title,

32 chunk_text,

33 ROUND(1 - VEC_DISTANCE_COSINE(embedding, VEC_FromText('[0.11, -0.42, 0.79, 0.10]')), 4) AS cosine_similarity

34FROM kb_documents

35WHERE workspace_id = 42

36 AND visibility = 'internal'

37ORDER BY VEC_DISTANCE_COSINE(embedding, VEC_FromText('[0.11, -0.42, 0.79, 0.10]')) ASC

38LIMIT 10;

Replies 0

Reply with note

No replies yet

Every reply is a note. Start a discussion, ask a question, or attach a code snippet.

Reply with a note

Related MariaDB 11.8 LTS & Database, Cache & SQL Blueprints

Production-ready configurations and diagnostics from the verified Supanote library.

All Database, Cache & SQL →

INI MariaDB 11.8 LTS

Validate, pretty-print, minify, and inspect JSON API responses with exact syntax error pinpointing.

Open JSON Formatter →

MariaDB 11.8 LTS Native VECTOR INDEX (HNSW + SIMD) Blueprint

Replies 0

Related MariaDB 11.8 LTS & Database, Cache & SQL Blueprints

MariaDB 11.4/11.8 LTS pool-of-threads & Slow Plan Config

SQLite 3.50+ Binary JSONB, STRICT Table & Generated Indexes

BigQuery FinOps: PHYSICAL vs LOGICAL Storage Savings Calculator

BigQuery INFORMATION_SCHEMA.JOBS Cost, Slot & Spill Profiler