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:
Create a document table and insert your source text.
Create a vector table to store the embeddings.
Vectorize the documents (offline for bulk data, online for single queries).
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 enabledDatabase 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:
| Mode | When to use |
|---|---|
| Offline | Bulk 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. |
| Online | Vectorize 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:
| Parameter | Description | Example value |
|---|---|---|
x_cols | The column that contains the source text to vectorize | content |
primary_key | The primary key column of the output vector table | id |
mode | The write mode. Only async (asynchronous) is supported | async |
vec_col | The column in the vector table that stores the embedding | content_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.