Build an AI-Powered Search Engine with HeatWave GenAI (part 1)

March 13, 2025

This article builds upon the concepts introduced in my previous blog posts:

For a deeper understanding, also consider reading these articles.

Traditional SQL search relies on structured queries (SELECT, WHERE, JOIN, …) and exact or partial matches based on conditions (e.g., WHERE name = 'Olivier' / WHERE name LIKE '%Olivier%').

A typical query may look like:

SELECT title 
FROM articles 
WHERE Category = 'HeatWave' OR tag LIKE "%AI%";

While efficient for structured data, it has limited flexibility for search variations and fails to grasp context or intent.

An alternative is SQL Full-Text Search (FTS), which enables efficient keyword-based searches across large text datasets. For example, MySQL implements FTS using MATCH and AGAINST (e.g. MATCH(name) AGAINST(‘Olivier’)). This feature indexes text content within database columns, allowing for advanced search capabilities such as phrase matching, proximity searches, and relevance scoring.

A typical query may look like:

SELECT * FROM articles
WHERE MATCH (title, body)
AGAINST ('HeatWave' IN NATURAL LANGUAGE MODE);

FTS is usually faster and more relevant than basic SQL searches, efficiently handling large text fields. However, it remains keyword-based rather than semantic, meaning it may overlook context-based variations.

Another option is AI-powered search using large language models (LLMs), also known as semantic search. Unlike keyword-based methods, it leverages embeddings — vector representations of words or sentences — to understand meaning. This enables it to handle synonyms, paraphrasing, and contextual relationships (e.g., searching for ‘AI’ may also return articles on ‘machine learning’). Additionally, it often integrates retrieval-augmented generation (RAG) to enhance responses with external knowledge.

In this article, we’ll dive deeper into AI-powered search using an LLM with the help of HeatWave GenAI…

What we want to do?

The goal is to build an AI-powered search engine for an application, designed to provide users with the most relevant articles based on their queries, using semantic search.
I’ll be using the data from my WordPress-based blog — https://dasini.net/blog/ — with the AI component powered by HeatWave GenAI. This leverages its in-database large language models and vector store capabilities (In-Database Embedding Models, The VECTOR Type, Vector Functions).
To ensure a focused and concise presentation, I’ll simplify this implementation by limiting the search to post titles and excerpts rather than the full article. Although this will significantly reduce the relevance of the results, it provides an opportunity to explore more comprehensive solutions in a future article.

I’am using HeatWave 9.2.1:

SELECT version();
+-------------+
| version()   |
+-------------+
| 9.2.1-cloud |
+-------------+

Create the table which will contain the embeddings

In WordPress the blog posts are stored in the table: wp_posts. The important columns to reach our goal are:

ID bigint unsigned NOT NULL AUTO_INCREMENT,
post_title text NOT NULL
post_excerpt text NOT NULL
guid varchar(255) NOT NULL DEFAULT ”

The columns contains respectively, the unique identifier of the post, its title, a short excerpt (hopefully) of the post and the URL of the article.
Example:

wordpress  SQL> 
SELECT ID, post_title, post_excerpt, guid from wp_posts WHERE post_status = 'publish' AND post_type = 'post' AND ID = 1234\G
*************************** 1. row ***************************
          ID: 1234
  post_title: HeatWave GenAI: Your AI-Powered Content Creation Partner
post_excerpt: Generative artificial intelligence (GenAI) is reshaping the content creation landscape. By training on vast datasets, these "intelligent" systems can produce new, human-quality content across a multitude of domains.

Oracle's HeatWave GenAI (starting with version 9.0.1) is at the forefront of this revolution, offering an integrated platform that combines in-database large language models (LLMs), vector stores, and scale-out vector processing to streamline content generation.
This article explores how HeatWave GenAI is empowering businesses to produce high-quality content rapidly and effectively, making it an indispensable tool for industries demanding speed, accuracy, and security.
        guid: https://dasini.net/blog/?p=1234

Based on these information, I created a table, wp_posts_embeddings_minilm, that contains the 4 columns:

wordpress  SQL> 
CREATE TABLE `wp_posts_embeddings_minilm` (
  `ID` bigint unsigned NOT NULL AUTO_INCREMENT,
  `post_title` text NOT NULL,
  `post_excerpt` text NOT NULL,
  `guid` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  KEY `post_title` (`post_title`(255)),
  KEY `post_excerpt` (`post_excerpt`(255)),
  KEY `guid` (`guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Then I populate this new table with my published posts:

wordpress  SQL> 
INSERT INTO wp_posts_embeddings_minilm SELECT ID, post_title, post_excerpt, guid FROM wp_posts WHERE post_status = 'publish' AND post_type = 'post';

End of the first step. Now we need to create the embeddings for each published posts.

Create the embeddings

We can query HeatWave’s sys.ML_SUPPORTED_LLMS table to know what embedding model we can use:

wordpress  SQL> 
SELECT * FROM sys.ML_SUPPORTED_LLMS WHERE model_type = "embedding";
+--------------------------------------+------------+
| model_name                           | model_type |
+--------------------------------------+------------+
| minilm                               | embedding  |
| all_minilm_l12_v2                    | embedding  |
| multilingual-e5-small                | embedding  |
| cohere.embed-english-light-v3.0      | embedding  |
| cohere.embed-multilingual-v3.0       | embedding  |
| cohere.embed-multilingual-light-v3.0 | embedding  |
| cohere.embed-english-v3.0            | embedding  |
+--------------------------------------+------------+

The 3 firsts (minilm, all_minilm_l12_v2 & multilingual-e5-small) are HeatWave’s in-database embedding models, there is no extra cost to use them (minilm and all_minilm_l12_v2 are 2 different names for the same model). Both are for encoding text or files in any supported language. HeatWave GenAI uses minilm, by default, for encoding English documents. While my blog also contains French article I’ll use this LLM.

The other models are from OCI Generative AI Service. They can also be used in the HeatWave workflow however their use will incur additional costs.

The comprehensive list of the languages, embedding models, and large language models (LLMs) that HeatWave GenAI supports is available here.

We are going to use the minilm model. The idea is to use this embedding model to encode the rows into a vector embedding. Embeddings creation is very easy with HeatWave, we only need to use 1 routine: sys.ML_EMBED_TABLE. This stored procedure runs multiple embedding generations in a batch, in parallel.

As a reminder this is what the wp_posts_embeddings_minilm table looks like:

DESC wp_posts_embeddings_minilm;
+--------------+-----------------+------+-----+---------+----------------+
| Field        | Type            | Null | Key | Default | Extra          |
+--------------+-----------------+------+-----+---------+----------------+
| ID           | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| post_title   | text            | NO   | MUL | NULL    |                |
| post_excerpt | text            | NO   | MUL | NULL    |                |
| guid         | varchar(255)    | NO   | MUL |         |                |
+--------------+-----------------+------+-----+---------+----------------+

Let’s encode the title, with sys.ML_EMBED_TABLE:

wordpress  SQL> 
CALL sys.ML_EMBED_TABLE("wordpress.wp_posts_embeddings_minilm.post_title", "wordpress.wp_posts_embeddings_minilm.post_title_embedding", JSON_OBJECT("model_id", "minilm"));

A new column – post_title_embedding – was added. It used the vector type:

DESC wp_posts_embeddings_minilm;
+----------------------+-----------------+------+-----+---------+----------------+
| Field                | Type            | Null | Key | Default | Extra          |
+----------------------+-----------------+------+-----+---------+----------------+
| ID                   | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| post_title           | text            | NO   | MUL | NULL    |                |
| post_excerpt         | text            | NO   | MUL | NULL    |                |
| guid                 | varchar(255)    | NO   | MUL |         |                |
| post_title_embedding | vector(2048)    | NO   |     | NULL    |                |
+----------------------+-----------------+------+-----+---------+----------------+

If you want to check the content, use the FROM_VECTOR (alias of VECTOR_TO_STRING) function:

SELECT post_title, FROM_VECTOR(post_title_embedding) FROM wp_posts_embeddings_minilm WHERE ID = 1234\G
*************************** 1. row ***************************
                       post_title: HeatWave GenAI: Your AI-Powered Content Creation Partner
FROM_VECTOR(post_title_embedding): [-6.91916e-02,-8.97512e-02,4.70568e-02,2.00090e-04,2.08057e-03,-3.68097e-02, ...

Now let’s create the embeddings for the excerpt:

CALL sys.ML_EMBED_TABLE("wordpress.wp_posts_embeddings_minilm.post_excerpt", "wordpress.wp_posts_embeddings_minilm.post_excerpt_embedding", JSON_OBJECT("model_id", "minilm"));

Now the new table structure is:

DESC wp_posts_embeddings_minilm;
+------------------------+-----------------+------+-----+---------+----------------+
| Field                  | Type            | Null | Key | Default | Extra          |
+------------------------+-----------------+------+-----+---------+----------------+
| ID                     | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| post_title             | text            | NO   | MUL | NULL    |                |
| post_excerpt           | text            | NO   | MUL | NULL    |                |
| guid                   | varchar(255)    | NO   | MUL |         |                |
| post_title_embedding   | vector(2048)    | NO   |     | NULL    |                |
| post_excerpt_embedding | vector(2048)    | NO   |     | NULL    |                |
+------------------------+-----------------+------+-----+---------+----------------+

Title and excerpt embeddings have been created!

SHOW CREATE TABLE wp_posts_embeddings_minilm\G
*************************** 1. row ***************************
       Table: wp_posts_embeddings_minilm
Create Table: CREATE TABLE `wp_posts_embeddings_minilm` (
  `ID` bigint unsigned NOT NULL AUTO_INCREMENT,
  `post_title` text NOT NULL,
  `post_excerpt` text NOT NULL,
  `guid` varchar(255) NOT NULL DEFAULT '',
  `post_title_embedding` vector(2048) NOT NULL COMMENT 'GENAI_OPTIONS=EMBED_MODEL_ID=minilm',
  `post_excerpt_embedding` vector(2048) NOT NULL COMMENT 'GENAI_OPTIONS=EMBED_MODEL_ID=minilm',
  PRIMARY KEY (`ID`),
  KEY `post_title` (`post_title`(255)),
  KEY `post_excerpt` (`post_excerpt`(255)),
  KEY `guid` (`guid`)
) ENGINE=InnoDB AUTO_INCREMENT=7059 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Query Encoding and Vector Similarity Operations

To retrieve the most relevant articles, we first need to convert the user’s query into a vector representation that captures its semantic meaning. This process, known as query encoding, transforms text into numerical embeddings. Once encoded, we can perform a similarity search to find the most relevant results by comparing the query’s embedding with precomputed vectors from our database.

Encode the query into a vector embedding

To generate a vector embedding for the query, we use the ML_EMBED_ROW routine. This function applies the specified embedding model to encode the given text into a vector representation. The routine returns a VECTOR containing the numerical embedding of the text.

Using it is straightforward. I define two variables: @searchItem (the text to encode) and @embeddOptions (the embedding model used for encoding):

wordpress  SQL> 
-- Set variables
SET @embeddOptions = '{"model_id": "minilm"}';
SET @searchItem = "Generative artificial intelligence";

-- Encode the query using the embedding model
SELECT sys.ML_EMBED_ROW(@searchItem, @embeddOptions) into @searchItemEmbedding;

If you want to see the content of the variable (not sure it is a good idea):

SELECT @searchItemEmbedding;
0x94927EBDB244843CB36A4E3CC747A2BC7EBFC6BDB5F4B7B... (truncated)

You can print the vector using the following query;

SELECT FROM_VECTOR(vect) 
FROM (
      SELECT sys.ML_EMBED_ROW(@searchItem, @embeddOptions) AS vect
     ) AS dt;
[-6.21515e-02,1.61460e-02,1.25987e-02,-1.98096e-02,... (truncated)

Similarity search

To retrieve relevant blog content, we perform vector similarity calculations using the DISTANCE function. This function computes the distance between two vectors using COSINE, DOT, or EUCLIDEAN distance metrics.
In our case, the two vectors being compared are the encoded query (@searchItemEmbedding) and the precomputed embeddings stored in the wp_posts_embeddings_minilm table (post_title_embedding and post_excerpt_embedding)

A cosine similarity search for article titles can be conducted using:

-- Ex 1.0. Similarity search only on titles (post_title_embedding)

SELECT ID, post_title, post_excerpt, DISTANCE(@searchItemEmbedding, post_title_embedding, 'COSINE') AS min_distance 
FROM WP_embeddings.wp_posts_embeddings_minilm 
ORDER BY min_distance ASC 
LIMIT 3\G
*************************** 1. row ***************************
          ID: 2345
  post_title: Simplifying AI Development: A Practical Guide to HeatWave GenAI’s RAG &amp; Vector Store Features
post_excerpt: This tutorial explores HeatWave GenAI, a cloud service that simplifies interacting with unstructured data using natural language. It combines large language models, vector stores, and SQL queries to enable tasks like content generation, chatbot, and retrieval-augmented generation (RAG). The focus is on RAG and how HeatWave GenAI’s architecture helps users gain insights from their data.
min_distance: 0.1232912540435791
*************************** 2. row ***************************
          ID: 1234
  post_title: HeatWave GenAI: Your AI-Powered Content Creation Partner
post_excerpt: Generative artificial intelligence (GenAI) is reshaping the content creation landscape. By training on vast datasets, these "intelligent" systems can produce new, human-quality content across a multitude of domains.

Oracle's HeatWave GenAI (starting with version 9.0.1) is at the forefront of this revolution, offering an integrated platform that combines in-database large language models (LLMs), vector stores, and scale-out vector processing to streamline content generation.
This article explores how HeatWave GenAI is empowering businesses to produce high-quality content rapidly and effectively, making it an indispensable tool for industries demanding speed, accuracy, and security.
min_distance: 0.1264844536781311
*************************** 3. row ***************************
          ID: 3456
  post_title: HeatWave GenAI: Sentiment Analysis Made Easy-Peasy
post_excerpt: This new AI tech, called generative AI (or GenAI), can dive deep into what people are saying and tell us if they’re feeling positive, negative, or neutral.
Let’s see how HeatWave GenAI, can help you to enhance your understanding of customer sentiment, improve decision-making, and drive business success.
min_distance: 0.12810611724853516

A probably more elegant query, using Common Table Expression (CTE), is:

-- Ex 1.1. Similarity search only on titles (post_title_embedding) w/ CTE
WITH distances AS (
    SELECT
        ID, 
        post_title,
        post_excerpt,
        DISTANCE(@searchItemEmbedding, post_title_embedding, 'COSINE') AS min_distance
    FROM WP_embeddings.wp_posts_embeddings_minilm
)
SELECT *
FROM distances
ORDER BY min_distance
LIMIT 3\G
*************************** 1. row ***************************
          ID: 2345
  post_title: Simplifying AI Development: A Practical Guide to HeatWave GenAI’s RAG &amp; Vector Store Features
post_excerpt: This tutorial explores HeatWave GenAI, a cloud service that simplifies interacting with unstructured data using natural language. It combines large language models, vector stores, and SQL queries to enable tasks like content generation, chatbot, and retrieval-augmented generation (RAG). The focus is on RAG and how HeatWave GenAI’s architecture helps users gain insights from their data.
min_distance: 0.1232912540435791
*************************** 2. row ***************************
          ID: 1234
  post_title: HeatWave GenAI: Your AI-Powered Content Creation Partner
post_excerpt: Generative artificial intelligence (GenAI) is reshaping the content creation landscape. By training on vast datasets, these "intelligent" systems can produce new, human-quality content across a multitude of domains.

Oracle's HeatWave GenAI (starting with version 9.0.1) is at the forefront of this revolution, offering an integrated platform that combines in-database large language models (LLMs), vector stores, and scale-out vector processing to streamline content generation.
This article explores how HeatWave GenAI is empowering businesses to produce high-quality content rapidly and effectively, making it an indispensable tool for industries demanding speed, accuracy, and security.
min_distance: 0.1264844536781311
*************************** 3. row ***************************
          ID: 3456
  post_title: HeatWave GenAI: Sentiment Analysis Made Easy-Peasy
post_excerpt: This new AI tech, called generative AI (or GenAI), can dive deep into what people are saying and tell us if they’re feeling positive, negative, or neutral.
Let’s see how HeatWave GenAI, can help you to enhance your understanding of customer sentiment, improve decision-making, and drive business success.
min_distance: 0.12810611724853516

Of course, you can perform the same search on the excerpt alone.
Alternatively, you can run a similarity search across both columns for more comprehensive results:

-- Ex 3.1 Similarity search on title & excerpt (post_title_embedding & post_excerpt_embedding)

WITH distances AS (
    SELECT
        ID, 
        post_title,
        post_excerpt,
        (
          DISTANCE(post_title_embedding, @searchItemEmbedding, 'COSINE') + 
          DISTANCE(post_excerpt_embedding, @searchItemEmbedding, 'COSINE')
        ) / 2 AS avg_distance
    FROM WP_embeddings.wp_posts_embeddings_minilm
)
SELECT *
FROM distances
ORDER BY avg_distance
LIMIT 5\G
*************************** 1. row ***************************
          ID: 1234
  post_title: HeatWave GenAI: Your AI-Powered Content Creation Partner
post_excerpt: Generative artificial intelligence (GenAI) is reshaping the content creation landscape. By training on vast datasets, these "intelligent" systems can produce new, human-quality content across a multitude of domains.

Oracle's HeatWave GenAI (starting with version 9.0.1) is at the forefront of this revolution, offering an integrated platform that combines in-database large language models (LLMs), vector stores, and scale-out vector processing to streamline content generation.
This article explores how HeatWave GenAI is empowering businesses to produce high-quality content rapidly and effectively, making it an indispensable tool for industries demanding speed, accuracy, and security.
avg_distance: 0.5131600499153137
*************************** 2. row ***************************
          ID: 3456
  post_title: HeatWave GenAI: Sentiment Analysis Made Easy-Peasy
post_excerpt: This new AI tech, called generative AI (or GenAI), can dive deep into what people are saying and tell us if they’re feeling positive, negative, or neutral.
Let’s see how HeatWave GenAI, can help you to enhance your understanding of customer sentiment, improve decision-making, and drive business success.
avg_distance: 0.5587222874164581
*************************** 3. row ***************************
          ID: 2345
  post_title: Simplifying AI Development: A Practical Guide to HeatWave GenAI’s RAG &amp; Vector Store Features
post_excerpt: This tutorial explores HeatWave GenAI, a cloud service that simplifies interacting with unstructured data using natural language. It combines large language models, vector stores, and SQL queries to enable tasks like content generation, chatbot, and retrieval-augmented generation (RAG). The focus is on RAG and how HeatWave GenAI’s architecture helps users gain insights from their data.
avg_distance: 0.6403274536132812
*************************** 4. row ***************************
          ID: 6789
  post_title: Webinar - Apprentissage automatique avec MySQL HeatWave
post_excerpt: HeatWave Machine Learning (ML) inclut tout ce dont les utilisateurs ont besoin pour créer, former, déployer et expliquer des modèles d’apprentissage automatique dans MySQL HeatWave, sans coût supplémentaire.

Dans ce webinaire vous apprendrez...
avg_distance: 0.7226708233356476
*************************** 72525. row ***************************
          ID: 5678
  post_title: Building an Interactive LLM Chatbot with  HeatWave Using Python
post_excerpt: AI-powered applications require robust and scalable database solutions to manage and process large amounts of data efficiently. HeatWave is an excellent choice for such applications, providing high-performance OLTP, analytics, machine learning and generative artificial intelligence capabilities.

In this article, we will explore a Python 3 script that connects to an HeatWave instance and enables users to interact with different large language models (LLMs) dynamically.
avg_distance: 0.736954927444458

For this example, the average distance was selected to combine title and excerpt similarity. However, alternative aggregation techniques can be employed.

Manage new articles

My last article (before this one) is named: Building an Interactive LLM Chatbot with HeatWave Using Python.

wordpress  SQL> 
SELECT ID, post_title, post_excerpt, guid FROM wp_posts WHERE ID = 4567\G
*************************** 1. row ***************************
          ID: 4567
  post_title: Building an Interactive LLM Chatbot with  HeatWave Using Python
post_excerpt: AI-powered applications require robust and scalable database solutions to manage and process large amounts of data efficiently. HeatWave is an excellent choice for such applications, providing high-performance OLTP, analytics, machine learning and generative artificial intelligence capabilities.

In this article, we will explore a Python 3 script that connects to an HeatWave instance and enables users to interact with different large language models (LLMs) dynamically.
        guid: https://dasini.net/blog/?p=4567

To enable this article in the AI-powered search engine, I need to generate embeddings for both the title (post_title) and excerpt (post_excerpt).
This is accomplished using sys.ML_EMBED_ROW, which encodes text into a vector embedding based on a specified model, returning a VECTOR data type.

These embeddings will then be inserted into the wp_posts_embeddings_minilm table, utilizing the same minilm embedding model:

wordpress  SQL> 
INSERT INTO wp_posts_embeddings_minilm (ID, post_title, post_excerpt, guid, post_title_embedding, post_excerpt_embedding) 
     SELECT ID, post_title, post_excerpt, guid, sys.ML_EMBED_ROW(post_title, '{"model_id": "minilm"}'), sys.ML_EMBED_ROW(post_excerpt,'{"model_id": "minilm"}')      
     FROM wordpress.wp_posts 
     WHERE ID = 4567;

Et voilà!
This implementation focused on post titles and excerpts for brevity, it lays a solid foundation for more comprehensive searches across entire article content, a topic to be explored in a future article.

Peroration

In this article, we explored how HeatWave GenAI enables AI-powered semantic search by leveraging in-database embeddings and vector similarity operations. Unlike traditional SQL search and full-text search, which rely on keyword matching, HeatWave GenAI provides deeper contextual understanding by transforming text into vector representations and performing similarity searches using the DISTANCE function.

The power of HeatWave GenAI’s in-database LLMs and vector store features was highlighted, showcasing its efficiency in handling both embedding generation and similarity calculations. Furthermore, the process of integrating new articles into the search engine by generating and inserting their embeddings was outlined, ensuring the search remains up-to-date. This approach not only enhances content discovery but also lays the groundwork for more advanced applications, such as personalized recommendations and intelligent query responses.

By adopting AI-powered search, we empower users with a more intuitive and effective way to discover relevant information, ultimately improving the overall user experience. Thanks to HeatWave GenAI which provides a robust and scalable solution for integrating advanced AI capabilities directly within the database.

Stay tuned for more insights!

Follow me on Linkedin

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using HeatWave & MySQL!

Olivier DASINI

Cloud Solutions Architect at Oracle
MySQL Geek, author, blogger and speaker
I’m an insatiable hunger of learning.
—–

Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–

dasini.net – Diary of a MySQL expert