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

April 8, 2025

Tags: AI, Cloud, GenAI, Heatwave, JavaScript, LLM

In Build an AI-Powered Search Engine with HeatWave GenAI (part 1), we explored how to build an AI-powered search engine using HeatWave GenAI. We highlighted the advantages of AI-driven semantic search over traditional SQL-based methods and provided a detailed guide on generating embeddings and conducting similarity searches. These techniques enhance the retrieval of relevant articles, improving the user’s ability to find information efficiently.

In this second part, we will explore how to enhance the relevance of our answers using reranking techniques. Next, we will further refine our results by instructing the model to generate embeddings based on article summaries. All these steps will be performed within HeatWave, leveraging its capability to write Stored Programs in JavaScript.

I’am using HeatWave 9.2.1:

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

In the previous episode

if you remember, in example 3.1, we saw how to run a similarity search on title & excerpt (post_title_embedding & post_excerpt_embedding), using an elegant CTE:

SQL >
-- 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
...

This result can be further improved using reranking techniques.
Reranking involves reordering or refining an initial set of retrieved documents to enhance their relevance to a user’s query. This step is essential for optimizing search quality, often leading to a significant boost in the relevance of the retrieved information.

Reranking

Here we setting up the same context (ie embedding model, natural language query encoding and vector similarity operations) than in part 1:

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;

The retrieved results is now sorted using weights on title and excerpt distances:

-- Ex 3.2 Similarity search on title & excerpt (post_title_embedding & post_excerpt_embedding) with weights

WITH initial_results AS (
    SELECT
        ID, 
        post_title,
        post_excerpt,
        DISTANCE(post_title_embedding, @searchItemEmbedding, 'COSINE') AS title_distance, 
        DISTANCE(post_excerpt_embedding, @searchItemEmbedding, 'COSINE') AS excerpt_distance, 
    guid 
    FROM WP_embeddings.wp_posts_embeddings_minilm 
    ORDER BY title_distance + excerpt_distance  -- Simple combination
    LIMIT 15 -- Retrieve a larger initial set
),
reranked_results AS (
    SELECT
        ID,
        post_title,
        post_excerpt,
        (0.3 * title_distance + 0.7 * excerpt_distance) AS combined_distance,   -- Weighted combination
    guid 
    FROM initial_results
)
SELECT post_title, post_excerpt, combined_distance, guid
FROM reranked_results
ORDER BY combined_distance ASC
LIMIT 5\G

*************************** 1. row ***************************
       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.
combined_distance: 0.49683985114097595
             guid: https://dasini.net/blog/?p=3456
*************************** 2. row ***************************
       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.
combined_distance: 0.4994780898094177
             guid: https://dasini.net/blog/?p=1234
*************************** 3. row ***************************
       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.
combined_distance: 0.6582363367080688
             guid: https://dasini.net/blog/?p=2345
*************************** 4. row ***************************
       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...
combined_distance: 0.694593733549118
             guid: https://dasini.net/blog/?p=6789
*************************** 5. row ***************************
       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.
combined_distance: 0.7135995388031006
             guid: https://dasini.net/blog/?p=5678

In the example above, the excerpt is given more than twice the weight of the title (0.7 vs 0.3), based on the assumption that the excerpt holds more relevant information in this context.
Depending on your use case, you may want to fine-tune these weights, as adjusting them can significantly improve the quality of search results.

Now, let’s explore how we can further refine our results by leveraging article summaries along with JavaScript-based stored procedures and functions.

A Javascript, stored procedure & AI story

When searching through articles, relying solely on the title and excerpt may not yield the most relevant results.
What if we used a summary instead?
This approach can strike an excellent balance between relevance and implementation simplicity. With HeatWave GenAI, the entire workflow can be handled directly within the database using SQL and JavaScript-based stored procedures.

In WordPress, article content is stored as HTML in the post_content column. To make it suitable for processing by a large language model (LLM), this content must first be sanitized — that is, all HTML tags need to be removed, as they are not meaningful to the LLM.

Table preparation

As a preparation step, I added a new column (post_content_text longtext NOT NULL) to the wp_posts_embeddings_minilm table that we have used in part 1.

ALTER TABLE wp_posts_embeddings_minilm ADD COLUMN post_content_text longtext NOT NULL;

The structure of the table is now:

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',
  `post_content_text` longtext NOT NULL,
  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

This new column — post_content_text — will be populated with the sanitized content of post_content.

For example, original HTML in post_content:

<!-- wp:paragraph -->
<p>HeatWave GenAI brings LLMs directly into your database, enabling powerful AI capabilities and natural language processing.</p>
<!-- /wp:paragraph -->

Should be stored in post_content_text as:

HeatWave GenAI brings LLMs directly into your database, enabling powerful AI capabilities and natural language processing

Strip HTML tags with a javascript stored routine

This could be done in Javascript, inside HeatWave, as a stored program. Isn’t it magnificent?
HeatWave supports stored routines written in JavaScript, since version 9.0.

A simple implementation could be the following:

CREATE FUNCTION sp.stripHtmlTags(htmlString LONGTEXT) RETURNS LONGTEXT NO SQL LANGUAGE JAVASCRIPT AS
$$
    if (!htmlString) {
        return "";
    }

    // Replace HTML tags with a space
    return htmlString
        .replace(/<[^>]+>/g, " ")  // Replace all tags with a space
        .replace(/\s+/g, " ")      // Replace multiple spaces with a single space
        .trim(); 
$$
;

Please bear in mind that I’m not a developer, so this code is provided for illustrative purposes only. It may contain errors or limitations. Please use it at your own risk and adapt it to your specific needs (also feel free to share back).

Let’s see if it works:

SELECT sp.stripHtmlTags('<!-- wp:paragraph --><p>HeatWave GenAI brings LLMs directly into your database, enabling powerful AI capabilities and natural language processing.</p><!-- /wp:paragraph -->') ;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sp.stripHtmlTags('<!-- wp:paragraph --><p>HeatWave GenAI brings LLMs directly into your database, enabling powerful AI capabilities and natural language processing.</p><!-- /wp:paragraph -->') |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HeatWave GenAI brings LLMs directly into your database, enabling powerful AI capabilities and natural language processing.                                                                       |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Looks like this Javascript stored function is doing the job.

We can now uses it to sanitized all the articles.

UPDATE wp_posts_embeddings_minilm 
    INNER JOIN wp_posts USING (ID) 
    SET post_content_text = sp.stripHtmlTags(post_content) 
    WHERE post_status = 'publish' 
       AND post_type = 'post' ;

Now all the published posts have a sanitized text only version in the database. We can now uses it to generate article summaries.

Generating summaries with the JavaScript HeatWave GenAI API

HeatWave offers a JavaScript API that enables seamless integration with HeatWave GenAI, allowing you to perform natural language searches powered by LLMs. You can find more details in the official documentation.

To generate summaries for all my articles, I’ll create a stored procedure using the ml.generate method. This method supports two modes: single invocation and batch processing. While single invocation is ideal for handling new articles individually, we’ll focus on the batch mode here to process all existing articles efficiently:

SQL > 
CREATE PROCEDURE sp.summarizePostBatch() LANGUAGE JAVASCRIPT AS
$$
    let schema = session.getSchema("wordpress");
    let table = schema.getTable("wp_posts_embeddings_minilm");

    ml.generate(table, "post_content_text", "post_summary_json", {model_id: "mistral-7b-instruct-v1", task: "summarization"});
$$
;

ml.generate loads the model (mistral-7b-instruct-v1), generates a response (article summary) inside the post_summary_json column (automatically created), based on the prompt (article) from the post_content_text column and returns the response (a summary).

Then call the stored procedure:

CALL sp.summarizePostBatch();

 Query OK, 0 rows affected (1 hour 2 min 16.3605 sec)

The process successfully created a new JSON column named post_summary_json:

SQL > SHOW CREATE TABLE wp_posts_embeddings_minilm;

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',
  `post_content_text` longtext NOT NULL,
  `post_summary_json` json DEFAULT NULL,
  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

Using the HeatWave MySQL JSON functions, we can check the content of this new column:

SELECT LEFT(post_summary_json->>"$.text", 100) AS json FROM wp_posts_embeddings_minilm WHERE ID = 1234\G
*************************** 1. row ***************************
json: Generative artificial intelligence (GenAI) is reshaping the content creation landscape. By training

Now let’s see how to create embeddings for all the summaries

Create the embeddings

We have seen in Build an AI-Powered Search Engine with HeatWave GenAI (part 1) how to create embeddings with the minilm model, using sys.ML_EMBED_TABLE routine.
We’ll follow the same process, but this time using JavaScript stored routines. Specifically, we’ll use the ml.embed method.

Before implementing the routine, we need to do some modification on the table. The reason is that in HeatWave 9.2.1, the table columns must be in one of the following format: varchar, tinytext, text, mediumtext and longtext. If it is not the case you’ll trigger the ERROR: 1644 (45000): ML006093: Type of 'post_summary_json' must be in ["varchar", "tinytext", "text", "mediumtext", "longtext"]
Quite explicit though!

We’ll transfer the content of post_summary_json into a text column named post_summary:

SQL >
ALTER TABLE wp_posts_embeddings_minilm ADD COLUMN post_summary text;

UPDATE wp_posts_embeddings_minilm SET post_summary =  post_summary_json->>"$.text";

And finally to avoid error ERROR: 1644 (45000): ML006093, we must drop post_summary_json:

ALTER TABLE wp_posts_embeddings_minilm DROP COLUMN post_summary_json;

ml.embed also supports two variants, one for a single invocation, and one for batch processing.

Single invocation of ml.embed

The goal is to create a stored procedure that encodes a summarized article into a vector embedding using the minilm embedding model:

SQL >
CREATE PROCEDURE sp.createEmbeddings(IN text2embed LONGTEXT, OUT vect VECTOR) LANGUAGE JAVASCRIPT AS
$$
    let embedding = ml.embed(text2embed, {model_id: "minilm"});
    vect = embedding;
$$
;

The input parameter is the summarized article:

SQL >
CALL sp.createEmbeddings(" This article explores the integration of HeatWave GenAI with Python for AI-driven applications. It provides a step-by-step guide to building a simple chatbot system that interacts with HeatWave using its in-database LLMs and external APIs from OCI Generative AI Service. The script demonstrates how to establish a connection to the HeatWave MySQL database, load and manage multiple LLMs within HeatWave, allow users to select their preferred model dynamically, facilitate chatbot interactions using HeatWave Chat, and retrieve and manage chat options. The article also discusses the benefits of using HeatWave GenAI for AI-driven applications, including its modular and scalable design, dynamic LLM selection, and powerful capabilities in AI.", @myVect);

The output parameter — @myVect — contains the embedding:

SQL >
SELECT @myVect\G
*************************** 1. row ***************************
@myVect: ?RK?p?????!<Q?c?
?????h?[???;??qV@=?mW???F?_?M=??<??$=EN&??Xv?6?`=E????P?V^v????=Dw?<Q?f?.?x?
                                                                            ?N?????<?1=
                                                                                        P-???=??O=
                                                                                                  ?A=21?=Ez????@??1j??*]?T??K????o?V?Ž?l
??+??=W??=3??????                                                                                                                       >?<iyx<?????<j????<???;z?_?H<??,=?'@?j?<N??=???M?C?`Wb?Qr?U?c???V<DmT<{N6=
                ????T??$?<?u
                            ?v$?=g?=?k???D?"??6?\??

A more human representation (or not) is possible using FROM_VECTOR:

SQL >
SELECT FROM_VECTOR(@myVect)\G
*************************** 1. row ***************************
FROM_VECTOR(@myVect): [-4.96396e-02,-8.32623e-02,9.86042e-03,-1.38911e-02,-1.98374e-02,-5.67944e-02,-3.55247e-02,2.43442e-03,-7.37070e-03,4.69574e-02,-5.25947e-02,-4.85626e-02,5.02704e-02,9.01929e-03,4.01000e-02,-2.53762e-03,-1.50358e-02,5.48794e-02,-3.82407e-02,-5.09363e-02,-6.01486e-02,6.62680e-02,1.97102e-02,-3.52313e-03,-6.07311e-02,-5.04761e-02,-2.28147e-02,2.70393e-02,3.71569e-02,-2.64454e-03,1.19642e-01,5.07343e-02,4.72823e-02,6.94298e-02,-1.87351e-02,-1.17510e-02,-5.71763e-02,-5.39956e-02,-1.96540e-02,-2.69486e-02,-3.46894e-02,-3.81926e-02,-9.65525e-02,3.40393e-02,2.23684e-02,1.51657e-02,-1.00843e-01,2.82697e-02,-2.81484e-02,2.06150e-02,-8.44855e-02,-9.88659e-02,1.22386e-02,4.21836e-02,-2.93204e-03,7.91035e-03,7.20659e-02,-8.70026e-03,-4.77326e-02,-1.38148e-02,-2.58113e-02,-4.66768e-03,-9.27910e-03,1.30998e-02,1.29655e-02,4.45084e-02,-2.69566e-02,7.25501e-02,7.15968e-02,-1.46195e-01,-6.30012e-02,-7.91235e-02,-5.19264e-02,2.40655e-02,-3.42919e-02,7.72180e-02,7.22950e-02,-7.19829e-02,-7.17984e-03,-2.89341e-02,-1.34624e-02,-4.61435e-02,1.58105e-02,7.25958e-02,-2.99175e-02,-7.80187e-02,-5.74389e-03,-2.57604e-02,7.18643e-02,4.47061e-02,2.45664e-02,-1.13831e-02,4.58998e-02,-1.19727e-02,5.77563e-02,9.52516e-02,3.84456e-02,-3.92485e-02,-3.90266e-02,2.87119e-02,-7.21781e-02,4.85492e-02,-8.24129e-02,-2.01809e-02,-1.77014e-02,6.55753e-02,-4.22585e-02,-6.67007e-02,6.90129e-02,-2.04837e-02,-5.49840e-02,-8.76547e-03,4.42857e-02,5.63733e-02,7.53812e-02,-4.63425e-02,1.82254e-02,1.18100e-02,-7.03449e-02,7.29235e-02,2.41146e-02,-3.35154e-02,-3.56129e-02,7.28611e-02,6.86842e-02,-5.54628e-02,-1.93234e-02,-1.89223e-03,9.01134e-02,6.44596e-02,2.64851e-02,9.06968e-02,4.06748e-02,5.16320e-02,3.33822e-02,5.54104e-02,-3.21358e-02,-6.66219e-02,2.00648e-02,1.09276e-01,-6.25206e-02,4.89027e-02,-4.17487e-02,-5.67389e-02,7.44691e-03,-4.42884e-02,3.18385e-02,-1.98049e-02,-7.42827e-02,-6.14671e-02,7.44849e-02,3.02982e-02,2.98403e-02,7.55829e-02,1.20768e-01,1.26906e-02,1.04550e-01,6.59261e-02,1.30538e-02,-1.39226e-02,-3.02682e-02,7.50245e-02,1.57195e-02,-2.63368e-03,-9.18113e-02,-6.67840e-02,-2.06254e-02,7.86020e-02,-2.10694e-02,1.30604e-03,5.31497e-02,5.42747e-02,-1.07493e-01,-4.50561e-02,-1.25854e-01,-2.63633e-02,2.99242e-02,-2.35737e-02,-1.81825e-02,2.76637e-02,-4.12063e-02,4.46310e-02,-1.92102e-02,-8.92773e-02,4.45359e-02,-1.11110e-02,-5.22624e-02,-2.45076e-02,-1.96262e-02,-1.98535e-02,8.15337e-02,7.87625e-02,5.73686e-02,1.47477e-02,6.91806e-03,1.53566e-02,7.33980e-03,1.07936e-02,1.51880e-03,2.81434e-02,-6.74229e-02,2.64910e-02,-1.99371e-02,5.59245e-02,2.73276e-02,-1.19287e-01,8.55642e-03,6.91925e-02,1.21589e-02,-4.05408e-02,-7.55252e-02,-3.61071e-02,2.72453e-02,-8.27803e-02,3.18795e-02,-5.05173e-03,-1.99760e-02,3.63341e-02,-6.50054e-02,-8.56357e-03,5.15133e-02,6.26782e-02,-4.57907e-02,-1.35400e-33,-3.12649e-02,1.42193e-02,-6.76464e-02,2.92021e-02,-5.87203e-02,-1.18268e-01,6.15763e-02,4.44978e-02,4.98419e-02,-1.72100e-02,-1.13092e-01,6.02123e-02,1.55891e-02,-5.68059e-02,-1.35132e-02,-4.19842e-02,-2.60558e-02,2.80308e-03,1.32598e-01,1.07273e-01,-3.47401e-02,4.75040e-02,-7.63112e-02,-2.59081e-02,2.11842e-02,2.25822e-02,-1.98663e-02,-1.04105e-02,-6.49599e-02,1.09372e-03,-5.17167e-02,1.38617e-02,-6.26832e-02,1.26345e-02,-1.71078e-02,5.77133e-02,-5.38819e-03,-6.03902e-02,1.65896e-02,-8.53037e-02,6.79781e-02,-2.80634e-02,-8.47060e-02,5.93425e-02,-1.46708e-02,4.85509e-02,-1.12832e-01,6.27912e-03,-6.16615e-03,-2.94036e-02,3.11167e-02,2.30855e-03,6.65979e-02,-6.83186e-02,4.35274e-02,-1.24276e-01,1.87970e-02,-3.84284e-02,-1.53174e-02,-3.97068e-02,1.07243e-02,-3.07626e-02,2.31247e-02,-3.29633e-02,-6.20930e-02,1.08394e-01,7.25451e-02,7.60207e-02,-4.54095e-02,-8.78456e-02,4.84518e-02,-4.07884e-02,7.50114e-02,1.14496e-02,2.41463e-02,5.81300e-02,-1.30148e-02,1.39014e-02,-1.40898e-02,-1.72861e-02,-3.93898e-02,-2.61493e-02,2.51394e-02,-2.53317e-02,-9.82916e-04,-2.21086e-02,5.22690e-02,3.53555e-02,6.22829e-03,3.66027e-02,2.17727e-03,6.67700e-02,-2.32061e-02,-1.69419e-03,-9.06145e-03,6.34304e-32,-1.78320e-02,-2.15657e-02,-6.16218e-03,5.44959e-02,1.88435e-02,-1.29032e-02,-4.80581e-02,4.21709e-02,1.50341e-02,4.37988e-02,8.35770e-02,-8.84255e-02,7.72715e-02,5.24237e-02,-3.56883e-02,-8.12593e-03,-3.41413e-02,-1.69388e-02,3.47573e-02,-1.21286e-01,3.22201e-02,3.63997e-02,-2.33719e-02,-2.07887e-02,-8.05931e-02,-7.69887e-02,-4.39181e-02,-9.76368e-03,-1.48867e-03,3.44279e-02,-3.62778e-02,-7.50277e-03,4.86021e-02,5.18756e-02,1.85040e-02,-2.77495e-03,-3.53111e-02,5.16714e-02,-2.28214e-02,-6.10510e-02,2.86529e-02,1.37693e-01,8.80472e-03,-5.79396e-02,3.57790e-02,1.58254e-03,3.59281e-02,-9.29721e-02,1.09798e-02,1.83488e-02,-1.42538e-03,-3.40717e-02,3.98733e-02,-1.05952e-02,3.72981e-02,-5.85627e-02,3.13814e-02,-5.80935e-02,-3.15143e-03,6.94674e-02,-6.51819e-03,6.39965e-02,3.89966e-02,-7.56700e-02]

Batch processing of ml.embed

To generate embeddings for all current posts at once, batch processing is the most efficient approach. Let’s create a new stored procedure in JavaScript:

SQL >
CREATE PROCEDURE sp.createEmbeddingsBatch() LANGUAGE JAVASCRIPT AS
$$
    let schema = session.getSchema("wordpress");
    let table = schema.getTable("wp_posts_embeddings_minilm");

    ml.embed(table, "post_summary", "post_summary_embedding", {model_id: "minilm"});
$$
;

ml.embed loads the model (minilm), generates an embedding inside the post_summary_embedding column (automatically created), based on the post summaries from the post_summary column.

Then run the stored procedure:

SQL > 
CALL sp.createEmbeddingsBatch();
Query OK, 0 rows affected (32.9131 sec)

The process successfully created a new VECTOR column named post_summary_embedding:

SQL > SHOW CREATE TABLE wp_posts_embeddings_minilm;

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',
  `post_content_text` longtext NOT NULL,
  `post_summary` text,
  `post_summary_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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Query Encoding and Vector Similarity Operations

The penultimate step is 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 will be able to perform a similarity search to find the most relevant results by comparing the query’s embedding with precomputed vectors from HeatWave.

Encode the query into a vector embedding

Like we have seen in part1, 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. Let’s define two variables: @searchItem (the text to encode) and @embeddOptions (the embedding model used for encoding):

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;

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.
Here, the two vectors being compared are the encoded query (@searchItemEmbedding) and the precomputed embeddings stored in the wp_posts_embeddings_minilm table (post_summary_embedding)

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

-- Ex 4.1. Similarity search only on the post summaries (post_summary_embedding)

WITH distances AS (
    SELECT
        ID, 
        post_title,
        post_excerpt,
        DISTANCE(@searchItemEmbedding, post_summary_embedding, 'COSINE') AS min_distance
    FROM WP_embeddings.wp_posts_embeddings_minilm
)
SELECT *
FROM distances
ORDER BY min_distance
LIMIT 5\G

A cosine similarity search for article titles, excerpts and summaries can be conducted using:

-- Ex 5.1 Similarity search on title, excerpt & post summary (post_title_embedding, post_excerpt_embedding & post_summary_embedding)

WITH distances AS (
    SELECT
        post_title,
        post_excerpt,
        (
          DISTANCE(post_title_embedding, @searchItemEmbedding, 'COSINE') + 
          DISTANCE(post_excerpt_embedding, @searchItemEmbedding, 'COSINE') + 
          DISTANCE(post_summary_embedding, @searchItemEmbedding, 'COSINE')
        ) / 3 AS avg_distance,
        guid
    FROM WP_embeddings.wp_posts_embeddings_minilm
)
SELECT *
FROM distances
ORDER BY avg_distance
LIMIT 5\G

Finally, you can (try to) improve the results using a reranking technique:

-- Ex 5.2 Weighted Similarity search on title, excerpt & post summary (post_title_embedding, post_excerpt_embedding & post_summary_embedding)

WITH initial_results AS (
    SELECT
        post_title,
        post_excerpt,
        DISTANCE(post_title_embedding, @searchItemEmbedding, 'COSINE') AS title_distance, 
        DISTANCE(post_excerpt_embedding, @searchItemEmbedding, 'COSINE') AS excerpt_distance,
        DISTANCE(post_summary_embedding,  @searchItemEmbedding, 'COSINE') AS summary_distance, 
        guid
    FROM WP_embeddings.wp_posts_embeddings_minilm 
    ORDER BY title_distance + excerpt_distance + summary_distance ASC -- Simple combination
    LIMIT 15 -- Retrieve a larger initial set
),
reranked_results AS (
    SELECT
        ID,
        post_title,
        post_excerpt,
        (0.2 * title_distance + 0.3 * excerpt_distance + 0.5 * summary_distance) AS combined_distance,  -- Weighted combination
        guid
    FROM initial_results
)
SELECT post_title, post_excerpt, combined_distance, guid
FROM reranked_results
ORDER BY combined_distance ASC
LIMIT 5\G

*************************** 1. row ***************************
       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.
combined_distance: 0.5093500733375549
             guid: https://dasini.net/blog/?p=1234
*************************** 2. row ***************************
       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.
combined_distance: 0.637738311290741
             guid: https://dasini.net/blog/?p=2345
*************************** 3. row ***************************
       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.
combined_distance: 0.6417026937007904
             guid: https://dasini.net/blog/?p=3456
*************************** 4. row ***************************
       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.
combined_distance: 0.6545232772827148
             guid: https://dasini.net/blog/?p=5678
*************************** 5. row ***************************
       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...
combined_distance: 0.7031511843204499
             guid: https://dasini.net/blog/?p=6789

Peroration

In this second part of our journey into building an AI-powered search engine with HeatWave GenAI, we explored advanced techniques to refine search relevance. By incorporating reranking strategies and leveraging article summaries for embedding generation, we significantly improved the quality of retrieved results.

Furthermore, we demonstrated how to harness HeatWave’s support for JavaScript-based stored programs to sanitize content, generate summaries, and compute embeddings — all within the database. This seamless integration of AI-powered search within HeatWave showcases its potential for efficient, scalable, and intelligent information retrieval.

With these enhancements, our search engine is now more capable of understanding and delivering relevant results, although we still can further optimize performance by experimenting with different embedding models, or even integrating additional AI-driven ranking techniques.

Stay tuned for more insights!

To be continued…

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