Semantic Search Over a SQL Table

Objective

Keyword search fails the moment a user phrases a question in their own words. "How long do I have to send something back" does not contain the word refund, so LIKE '%refund%' finds nothing, and the help article that answers it sits one row away.

The usual fix is a second system: a vector database, an ingestion pipeline to keep it in sync, and two sources of truth. This recipe does it in one table with two SQL functions.

Step 1: A table with an embedding column

VECTOR(384) must match the embedding model's output. The default model, all-minilm, produces 384 dimensions. A mismatch here produces rows that never match anything, silently.

-- Drop first so the recipe is re-runnable.
DROP TABLE IF EXISTS recipe_sem_articles;

CREATE TABLE recipe_sem_articles (
  article_id  INT PRIMARY KEY,
  title       TEXT,
  body        TEXT,
  embedding   VECTOR(384)
);

Step 2: Write the rows and their meaning in one statement

EMBED() turns text into a vector at insert time. Embed the title and body together — the title alone is usually too short to carry the meaning.

INSERT INTO recipe_sem_articles (article_id, title, body, embedding) VALUES
  (1, 'Refund policy',
      'Unopened items can be returned within 30 days for a full refund.',
      EMBED('Refund policy. Unopened items can be returned within 30 days for a full refund.')),
  (2, 'Shipping times',
      'Standard delivery takes 3 to 5 business days within the country.',
      EMBED('Shipping times. Standard delivery takes 3 to 5 business days within the country.')),
  (3, 'Password reset',
      'Use the forgot-password link to receive a reset email.',
      EMBED('Password reset. Use the forgot-password link to receive a reset email.')),
  (4, 'Warranty',
      'Hardware is covered for 24 months against manufacturing defects.',
      EMBED('Warranty. Hardware is covered for 24 months against manufacturing defects.'));

Step 3: Ground truth — show that keyword search fails

Before claiming semantic search helps, prove the alternative does not:

SELECT COUNT(*) AS keyword_hits
FROM recipe_sem_articles
WHERE body LIKE '%send something back%';

Expected: 0. The refund article is the right answer and contains none of those words. This is the gap, in one query.

Step 4: Search by meaning

SELECT
  title,
  ROUND(COSINE_SIMILARITY(embedding, EMBED('how long do I have to send something back')), 4) AS similarity
FROM recipe_sem_articles
ORDER BY similarity DESC
LIMIT 3;

Expected, in this order:

The right article wins with no shared keywords. "Shipping times" placing second is correct behaviour rather than a bug — both are about getting goods moved — and the 0.22 gap between first and second is what you threshold on.

Alias the similarity and sort by the alias. ORDER BY COSINE_SIMILARITY(...) directly is refused: "ORDER BY cannot use AI functions". Computing it in the SELECT list with an alias, as above, is the supported form — and it is also faster, because the value is computed once per row rather than twice.

Step 5: Combine meaning with ordinary SQL

This is the part a separate vector database makes hard. Filters, joins and similarity in one statement, against one copy of the data:

SELECT
  article_id,
  title,
  ROUND(COSINE_SIMILARITY(embedding, EMBED('my device stopped working after a year')), 4) AS similarity
FROM recipe_sem_articles
WHERE article_id <> 3
ORDER BY similarity DESC
LIMIT 2;

Expected: Warranty first — a year-old device failing is a warranty question, and the model knows that without the word warranty appearing in the query.

The WHERE is the point. In a two-system setup you would search the vector store, get ids back, then query the database to filter them, and handle the case where filtering empties your top-k. Here the filter is just a predicate.

Cleanup (Optional)

DROP TABLE IF EXISTS recipe_sem_articles;

Performance: read this before you scale it

There is no vector index. CREATE INDEX … USING HNSW on a SQL column is refused — "SQL-level vector indexes are not implemented in this release" — so every similarity query is a full scan, and cost grows with rows × dimensions.

That is fine for thousands of rows and not fine for millions. Two levers:

1. Filter first. The WHERE in Step 5 can use an ordinary B-tree index, so narrowing by tenant, date or category before the similarity is computed is the most effective thing you can do.
2. Use a vector collection for large corpora. POST /v1/vectors/collections with index_type: "hnsw" gives you a real approximate-nearest-neighbour index built for scale. The trade-off is that it is separate storage from your SQL rows — you get speed and lose the single-statement join from Step 5.

Pick deliberately. For a help centre of a few thousand articles, the scan is simpler and the join is worth more than the milliseconds.

Use it from your agent

• REST/SDK: POST /v1/query/execute. Both SDKs also expose client.embed() if you want the vector client-side.
• Keep embeddings fresh: re-run EMBED() whenever the text changes. An embedding of stale text is worse than no embedding, because it still matches.
• Why in-DB: no second datastore, no sync job, and no window where the index disagrees with the table.

Key Concepts Learned

• VECTOR(N) must match the model's dimensions. 384 for all-minilm. A mismatch fails silently by never matching.
• Embed title and body together. Short strings carry little meaning.
• Alias the similarity; ORDER BY cannot call an AI function directly.
• Prove keyword search fails first. It makes the value concrete and takes one query.
• Similarity is a ranking, not a truth. Threshold on the gap between first and second, not on an absolute number.
• No SQL-level vector index exists, so scale by filtering first or by moving large corpora to a vector collection.