All Products
Search
Document Center

PolarDB:Build a document retrieval system

Last Updated:Mar 28, 2026

When you need to search large collections of text — such as internal wikis, research papers, or legal archives — keyword search often misses semantically relevant results. PolarDB for MySQL includes a built-in AI engine that converts documents into vector embeddings and retrieves results by semantic similarity. This topic shows you how to build a complete document retrieval system using PolarDB AI.

How it works

The system stores documents in two tables: a source table for raw text and a vector table for the embeddings. At query time, the system converts your query text into a vector using the _polar4ai_text2vec built-in model, then uses the distance() function to find the closest matches in the vector table.

The build process follows four steps:

  1. Create a document table and insert your source text.

  2. Create a vector table to store the embeddings.

  3. Vectorize the documents (offline for bulk data, online for single queries).

  4. Run a vector search to retrieve results.

Use cases

  • Enterprise knowledge management: Index internal documents, wikis, and policies so employees can find relevant information quickly.

  • Academic research: Search large collections of papers and publications to identify relevant literature.

  • News and media: Retrieve related articles and reports for a given topic or event.

  • Legal and medical archives: Search across legal documents and clinical literature.

Prerequisites

Before you begin, make sure you have:

  • A PolarDB for MySQL cluster with the PolarDB AI (polar4ai) extension enabled

  • Database write access to create tables and insert data

Step 1: Create the document table

Run the following SQL to create the source document table:

CREATE TABLE enterprise_context(
    id bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key ID',
    content text DEFAULT NULL COMMENT 'Content',
    PRIMARY KEY (id)
) ENGINE=InnoDB COMMENT='Text data';

Then insert your source documents:

INSERT INTO enterprise_context(id, content) VALUES
(1, 'Sinosoft Company Limited with the stock abbreviation "Sinosoft Technology" and the stock code "603927", a listed company, is the product of the knowledge innovation pilot project implemented by the Institute of Software Chinese Academy of Sciences, and is the result of the transformation of the technology research and development subject of the Institute. This company is headquartered in Beijing and has the registered capital of 593.6 million CNY. It is a knowledge-intensive high-tech enterprise specialized in the research and development, application, and service of computer software.'),
(2, 'Sinosoft Technology focuses on large-scale application software development and computer system integration as its core competencies, integrates self-developed industry-generic software products, network information security software products, comprehensive network application platforms, middleware software products, and application tools under one roof, covers all levels of system software, supporting software, intelligent architectural engineering, industry application software, and provides all-round support for large-scale application system engineering.');

Step 2: Create the vector table

Run the following SQL to create the vector table. The /*polar4ai*/ prefix routes the statement to the PolarDB AI engine.

/*polar4ai*/CREATE TABLE vector_table(
  id bigint,
  content text,
  content_vector vector_768,
  primary key(id)
);

The vector_768 data type stores 768-dimensional vectors. This dimension matches the output of the _polar4ai_text2vec model used in the next step — the model always produces 768-dimensional vectors and does not support other dimensions.

Step 3: Vectorize documents

PolarDB AI supports two vectorization modes. Choose based on your scenario:

ModeWhen to use
OfflineBulk vectorization of existing documents. Runs asynchronously in the background, so it does not block your application. Use this for initial data loading or batch updates.
OnlineVectorize a single text string on demand. Use this to convert a user query into a vector before running a search.

Offline vectorization (bulk)

Run the following SQL to vectorize all rows in enterprise_context and write the results to vector_table:

/*polar4ai*/SELECT id, content
FROM predict(
  model _polar4ai_text2vec,
  SELECT id, content FROM enterprise_context
)
WITH (
  x_cols      = 'content',        -- Column containing the source text
  primary_key = 'id',             -- Primary key to use in the output table
  mode        = 'async',          -- Only async mode is supported
  vec_col     = 'content_vector'  -- Column that stores the output vector
)
INTO vector_table;

The WITH() parameters:

ParameterDescriptionExample value
x_colsThe column that contains the source text to vectorizecontent
primary_keyThe primary key column of the output vector tableid
modeThe write mode. Only async (asynchronous) is supportedasync
vec_colThe column in the vector table that stores the embeddingcontent_vector

Online vectorization (single query)

To vectorize a single text string, run:

/*polar4ai*/SELECT * FROM predict(
  model _polar4ai_text2vec,
  SELECT 'What is the stock code of Sinosoft Technology'
) WITH ();

This returns a 768-dimensional vector that you can pass directly to a vector search.

Step 4: Run a vector search

Run the following SQL to find the top 10 most similar documents to a query vector:

/*polar4ai*/SELECT id, distance(content_vector, [1,2,3,4,5……,768])
FROM vector_table
LIMIT 10;

Replace [1,2,3,4,5……,768] with the actual 768-dimensional vector for your query text. To get this vector, first run an online vectorization query (Step 3) against your query string.

Reading the results: The distance() function returns a numeric score for each row indicating the similarity between the document and the query vector.