Data Daz (dasini.net) – Data Systems, AI, and Real-World Insights

Note on model availability: This article was written before September 30, 2026. The models, model IDs, regional availability, and serving modes mentioned below may have been updated, replaced, or retired since then. Before running the examples, check the current OCI documentation and select models supported in your region and serving mode. If you change the embedding model, regenerate the stored embeddings and use the same model for searches.

In my previous series, we built a search engine for this blog using MySQL HeatWave GenAI. We started with title and excerpt embeddings, added article summaries and weighted ranking, then explored searching the full content through chunks.

This time, let’s keep MySQL HeatWave for storing and searching vectors, and call OCI Generative AI directly from Python to generate summaries, keywords, and embeddings.

The goal remains the same: help readers find relevant articles. This alternative gives the application responsibility for the model calls and the indexing workflow. It is another option to consider alongside the integrated approach described in the earlier articles.

For the background, see:

• Part 1 — Embeddings and vector similarity search
• Part 2 — Summaries and weighted ranking
• Part 3 — Searching full articles with chunks

What are we going to build

The Python demo reads published WordPress posts, creates a summary and keywords for each article, and generates separate embeddings for its title, excerpt, and summary. A Flask interface offers semantic, keyword, and hybrid search.

This follows the article-level representation used in part 2. The full article contributes through its generated summary; the demo does not embed every passage as part 3 did. That distinction matters for queries about details that a summary may omit.

It is also a retrieval application. The LLM enriches the index, but it does not write an answer to each user’s question. The interface returns article links and excerpts. A subsequent answer-generation step could turn this into a RAG application, but that step is outside this demo.

Who does what in this architecture

During indexing, Python reads WordPress posts from MySQL, extracts their text, requests summaries and keywords from OCI, requests embeddings, and writes the results back to MySQL.

During semantic search, Flask sends the query text to OCI’s embedding endpoint. Python receives a numerical vector and passes it to a parameterized SQL query. MySQL converts it to a VECTOR and ranks the stored articles. Hybrid search also runs the keyword query, then combines both rankings in Python. Keyword-only searches make no OCI call.

There are no calls to sys.ML_EMBED_ROW, sys.ML_EMBED_TABLE, or the MySQL HeatWave JavaScript GenAI API in this implementation. STRING_TO_VECTOR and DISTANCE remain essential database operations: neither invokes an LLM.

MySQL HeatWave can also integrate with OCI models through its own GenAI interfaces, as part 1 already noted. The distinguishing choice here is to call OCI directly from the application. The comparison concerns these implementations, rather than a limit on what either service can do.

Choose the models and serving mode

The original demo uses cohere.command-a-03-2025 for summaries and keywords, and cohere.embed-multilingual-v3.0 for embeddings. These are separate jobs: a chat model produces text; an embedding model produces the vectors used for retrieval.

For a new deployment, check the current model retirement schedule. Oracle’s schedule listed September 30, 2026 as the on-demand retirement date for Cohere Embed Multilingual 3, with Embed 4 as the suggested replacement. The shared scripts require explicit model IDs in the environment configuration rather than relying on the original default.

The helper already contains an Embed 4 branch that requests 1,024 output dimensions. This matches the demo’s VECTOR(1024) columns. Embed 4 otherwise defaults to 1,536 dimensions; both sizes are supported through the API. See the Embed 4 documentation.

Region and serving mode must be chosen together, and availability may have changed since this article was written. The demo constructs OnDemandServingMode requests, so select a region that currently supports your chosen models on demand. Using a dedicated endpoint requires adapting the client; changing only the model ID is insufficient. Consult the current models-by-region documentation before running the example.

A model change also requires rebuilding the document embeddings and changing the query model to match. Two models can both return 1,024 numbers without producing compatible vectors. Keep the model ID, output dimensions, and preprocessing version with the index configuration, and switch the application only after the new index is ready.

Prepare the database and Python environment

Use a MySQL HeatWave deployment with the vector functions required by the query. Having a VECTOR column alone does not establish that a database supports DISTANCE; consult the vector function documentation for your deployment and version.

The following minimal schema is included as schema.sql with the shared code. Run it in the configured database before ingestion; the scripts assume the destination table already exists:

CREATE TABLE wp_posts_embeddings_cohere (
    ID BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    post_title TEXT NOT NULL,
    post_excerpt TEXT NOT NULL,
    guid VARCHAR(255) NOT NULL,
    post_summary TEXT NOT NULL,
    post_keywords JSON NOT NULL,
    post_title_embedding VECTOR(1024) NOT NULL,
    post_excerpt_embedding VECTOR(1024) NOT NULL,
    post_summary_embedding VECTOR(1024) NOT NULL,
    FULLTEXT KEY title_excerpt_summary
        (post_title, post_excerpt, post_summary)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The keyword query can run without a JSON multi-valued index. Such an index is an optional optimization for JSON_OVERLAPS; verify its definition and the actual execution plan on the target deployment. The FULLTEXT index over the three MATCH columns is required for the demonstrated full-text query.

The Python dependencies are oci, mysql-connector-python, and Flask. Configure OCI SDK authentication and permissions for inference in the chosen compartment, plus connectivity and credentials for MySQL.

The scripts share configuration through settings.py. They read OCI_COMPARTMENT_ID, OCI_GENAI_ENDPOINT, OCI_CONFIG_FILE, OCI_CONFIG_PROFILE, OCI_EMBEDDING_MODEL_ID, and the MYSQL_* settings from environment variables. Ingestion also requires OCI_SUMMARY_MODEL_ID for a compatible Cohere chat model. The scripts do not load a .env file automatically.

Copy .env.example to .env, replace the placeholders, and export the settings as described in README.md. The shared scripts contain no hard-coded credentials. The package includes requirements.txt and .gitignore, and index.html is already placed under Flask’s templates directory. The web application uses the embedding model selected by the server configuration, so the search form cannot override it.

Generate summaries and keywords with OCI

The ingestion script selects published posts, removes HTML markup, ignores script and style content, and normalizes whitespace. Empty excerpts fall back to the title; empty article content falls back to the excerpt or title.

It then calls OCI’s chat API. This condensed extract shows the request structure used in generate_embeddings_gpt.py; imports and response validation are omitted here for readability.

request = CohereChatRequest(
    message=(
        "Summarize the following sanitized blog post. "
        "Return a concise, factual summary and exactly "
        "five distinct, relevant keywords.\n\n"
        f"Post:\n{content}"
    ),
    response_format=CohereResponseJsonFormat(schema={
        "type": "object",
        "properties": {
            "summary": {"type": "string"},
            "keywords": {
                "type": "array",
                "items": {"type": "string"},
            },
        },
        "required": ["summary", "keywords"],
        "additionalProperties": False,
    }),
    max_tokens=400,
    temperature=0,
)
response = helper.client.chat(ChatDetails(
    serving_mode=OnDemandServingMode(
        model_id=SUMMARY_MODEL_ID
    ),
    compartment_id=helper.compartment_id,
    chat_request=request,
))
payload = json.loads(response.data.chat_response.text)

Structured output makes the summary and keywords easier to consume; see CohereResponseJsonFormat. It does not establish that the summary is factually correct. The prompt requests five distinct keywords, but the supplied schema and Python filtering do not enforce that count or uniqueness. Validate these properties explicitly if they are part of the application’s contract.

HTML cleanup also does not make article text safe to treat as instructions. Keep source content separate from trusted task instructions and inspect generated summaries, especially when indexing content from outside your control.

Generate and store document embeddings

OCIEmbeddingHelper wraps the SDK client and serializes the returned vector with json.dumps. The following excerpt preserves its main behavior while making input_type explicit. The helper’s client is configured with OCI’s default retry strategy.

details = {
    "serving_mode": OnDemandServingMode(model_id=model_id),
    "compartment_id": self.compartment_id,
    "inputs": [text],
    "input_type": input_type,
    "truncate": "END",
}
if model_id == "cohere.embed-v4.0":
    details["output_dimensions"] = 1024

response = self.client.embed_text(EmbedTextDetails(**details))
vector = response.data.embeddings[0]
sql_vector = json.dumps(vector)

Use SEARCH_DOCUMENT for stored text and SEARCH_QUERY for user searches. These paired input types tell the model how the text will be used. The OCI SDK parameter names and accepted values are documented in EmbedTextDetails.

The indexing loop creates three embeddings per article:

_, title_vector = helper.embed(
    title, EMBEDDING_MODEL_ID, "SEARCH_DOCUMENT")
_, excerpt_vector = helper.embed(
    excerpt, EMBEDDING_MODEL_ID, "SEARCH_DOCUMENT")
_, summary_vector = helper.embed(
    summary, EMBEDDING_MODEL_ID, "SEARCH_DOCUMENT")

All three vectors go into the same row as the article metadata. Here is the insertion pattern, condensed from the demo:

writer.execute("""
    INSERT INTO wp_posts_embeddings_cohere
      (ID, post_title, post_excerpt, guid,
       post_title_embedding, post_excerpt_embedding,
       post_summary, post_summary_embedding, post_keywords)
    VALUES (%s, %s, %s, %s,
            STRING_TO_VECTOR(%s), STRING_TO_VECTOR(%s),
            %s, STRING_TO_VECTOR(%s), %s)
""", (row["ID"], title, excerpt, row["guid"],
      title_vector, excerpt_vector, summary,
      summary_vector, keywords))

OCI produces the vector values. MySQL converts their JSON string representation into its native vector format. Passing the values as parameters keeps the vector text out of the SQL statement itself.

The helper uses truncate=”END”, so text exceeding the embedding model’s limit can lose its final tokens. Account for that when choosing summary length. For longer documents, the chunking approach from part 3 remains useful; the current ingestion script sends the cleaned article to the summarizer without an explicit length-management step.

Search with an OCI query embedding and SQL distances

The query follows the same boundary between services: Python generates an embedding, then SQL performs retrieval. The following is a shortened equivalent of the weighted semantic query in app.py.

_, query_vector = helper.embed(
    query, EMBEDDING_MODEL_ID, "SEARCH_QUERY")

cursor.execute("""
    WITH q AS (
        SELECT STRING_TO_VECTOR(%s) AS embedding
    )
    SELECT ID, post_title, post_excerpt, guid,
      0.1 * DISTANCE(q.embedding,
                    post_title_embedding, 'COSINE') +
      0.2 * DISTANCE(q.embedding,
                    post_excerpt_embedding, 'COSINE') +
      0.7 * DISTANCE(q.embedding,
                    post_summary_embedding, 'COSINE')
          AS combined_distance
    FROM wp_posts_embeddings_cohere
    CROSS JOIN q
    ORDER BY combined_distance ASC, ID ASC
    LIMIT %s
""", (query_vector, limit))

Lower distance means a closer match. The weights assign 10% to the title, 20% to the excerpt, and 70% to the summary. These are starting values for this demo, not measured optimal settings. ID supplies a stable tie-breaker.

A semantic query returns the nearest available articles even when none is particularly relevant. Returning ten results does not imply ten good matches. Evaluate a relevance threshold or a suitable empty-result policy using representative queries.

The statement expresses a weighted distance ranking. It does not demonstrate approximate nearest-neighbor indexing, a particular offload plan, or a measured latency. Use execution plans and timings to establish how it behaves on your deployment.

Add keyword and hybrid search

Semantic search is useful for paraphrases and related concepts. Literal names, acronyms, and technical identifiers also deserve a keyword route. The demo provides both.

The keyword score combines MySQL full-text relevance over title, excerpt, and summary with an exact overlap against generated keyword strings:

0.7 * MATCH(post_title, post_excerpt, post_summary)
      AGAINST(%s IN NATURAL LANGUAGE MODE)
+ 0.3 * COALESCE(JSON_OVERLAPS(post_keywords, %s), 0)

Python splits the keyword input on commas before building the JSON array. JSON_OVERLAPS tests shared values; it does not provide semantic matching or partial phrase matching. Establish a consistent normalization policy for generated keywords and query terms.

The coefficients do not mean that full-text and keyword overlap contribute calibrated percentages of relevance. Full-text scores are variable, while JSON_OVERLAPS contributes a binary value. The overlap therefore acts as a fixed bonus in this implementation.

Hybrid search combines positions in the two result lists using reciprocal-rank fusion. This compact equivalent preserves the demo’s equal contributions and constant of 60:

merged = {}
for results in (semantic_results, keyword_results):
    for rank, result in enumerate(results, start=1):
        item = merged.setdefault(
            result["ID"], {**result, "fusion_score": 0.0})
        item["fusion_score"] += 1.0 / (60 + rank)

results = sorted(
    merged.values(),
    key=lambda item: (-item["fusion_score"], item["ID"]),
)[:limit]

An article present in both rankings receives both contributions. Higher fusion scores rank first. This avoids adding cosine distances directly to full-text relevance scores, which have different scales and directions.

The demo retrieves ten candidates from each method by default and returns ten fused results. Increasing the candidate pools independently of the final result count is a useful experiment. Fusion cannot recover an article absent from both lists.

This is rank fusion, with no additional LLM or dedicated reranking model involved. The actual Flask implementation also preserves source labels so readers can see which methods found each article. Its displayed distances, keyword scores, and fusion scores are different quantities, not probabilities.

Benefits and trade offs

The direct SDK architecture suits a team that wants to own preprocessing, model requests, structured responses, and retry behavior in Python. These components can be tested and deployed with the application, and model experimentation can proceed independently of stored-routine changes. MySQL HeatWave remains the place where article metadata and vectors are joined and retrieved.

That flexibility carries operational work. The application now coordinates database access and inference access, manages two failure paths, and must keep model configuration consistent across indexing and search. On-demand inference introduces service usage charges and potential throttling, in addition to the database and application costs.

The earlier integrated implementation keeps more of this workflow behind SQL and HeatWave’s GenAI interfaces. That can be attractive for teams who already organize data processing around the database. Using its embedded models also avoids adding a separate OCI inference service dependency for those model calls. The appropriate choice depends on the models, controls, and deployment practices the application needs.

Consideration	Integrated approach from the series	Direct OCI SDK approach
Application code	Model work exposed through database APIs	Application explicitly manages model requests
Change ownership	More behavior maintained with database routines	More behavior maintained with Python services
Data movement	Embedded model work stays within MySQL HeatWave	Article text and queries go to the configured OCI endpoint
Operations	Database and integrated model workflow	Database plus OCI authentication, quotas, and network calls
Cost evaluation	Database and embedded-model resource needs	Database plus inference usage and application operations
Quality evaluation	Depends on representation, models, and ranking	Depends on representation, models, and ranking

These are architectural considerations, not benchmark results. Neither design guarantees better relevance or lower latency. Compare them with the same corpus and judged queries, and distinguish a change of model from a change of orchestration.

Ignoring retries, the current ingestion loop makes one chat request and three embedding requests per article. Each semantic or hybrid search adds one embedding request; keyword-only search adds none. Committing every 100 rows groups database transactions, but does not batch OCI calls. This request pattern is a useful starting point for cost and throughput estimates, rather than a monetary estimate by itself.

Péroraison

MySQL HeatWave and OCI Generative AI can share the work in this way: OCI handles model inference, Python coordinates the application, and SQL handles retrieval.

The integrated solution from the earlier series remains a valid choice. This demo adds an alternative for applications that need direct control of their model calls while keeping search data in MySQL.

Back to top

Resources

• MySQL HeatWave Overview
• HeatWave User Guide
• MySQL
• Oracle Cloud Overview
• OCI Generative AI

Follow me on Linkedin

Watch my videos on my YouTube channel and subscribe.

My Speaker Deck account.

My Slideshare account (archive).

Thanks for using HeatWave & MySQL!

Filed under:
AI, Artificial Intelligence, Howto, MySQL by Olivier DASINI