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

-- 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;