Modern applications are expected to deliver instant responses while processing increasingly large volumes of data. Achieving this level of performance isn’t simply a matter of making the database faster. It requires placing the right workload on the right layer of the architecture.
Some operations require ultra-fast repeated reads, others demand transactional consistency, while analytical queries benefit from massively parallel in-memory processing.
This is precisely where OCI Cache and MySQL HeatWave complement each other.
OCI Cache provides a fully managed, high-performance caching layer compatible with Valkey and Redis environments, while MySQL HeatWave delivers a fully managed MySQL database with an integrated in-memory query accelerator capable of running transactional, analytical, machine learning, and AI workloads from the same database.
The real value isn’t choosing one technology over the other. It’s understanding that they solve different problems and that, when combined, they create a simple yet extremely powerful architecture.
OCI Cache accelerates data access. MySQL HeatWave accelerates data processing.
Together they provide exceptional performance while keeping the architecture straightforward, scalable, and easy to operate.
Far from being competitors, these two services are highly complementary. In this article, we’ll explore how they work together and why combining them can dramatically improve application responsiveness, database efficiency, and overall user experience.
What is OCI Cache?
OCI Cache is Oracle Cloud Infrastructure‘s fully managed in-memory caching service, powered by Valkey while maintaining compatibility with the Redis protocol.
Following Redis licensing changes, the open source community created Valkey under the Linux Foundation. Oracle became one of its major contributors and integrated Valkey directly into Oracle Cloud Infrastructure (OCI), providing developers with an enterprise-grade managed service while preserving compatibility with existing Redis applications.
For teams already familiar with Redis, migrating to OCI Cache typically requires little or no application changes.
Being a fully managed service, OCI Cache removes the operational burden of deploying clusters, configuring replication, handling failover, performing upgrades, and scaling infrastructure. Applications benefit from response times measured in microseconds to sub-millisecond latency, making it ideal for frequently accessed data.
Typical use cases include:
User sessions
Shopping carts
User profiles and preferences
Product catalogs
Feature flags
API response caching
Leaderboards
Rate limiting
Frequently accessed configuration data
…
Its mission is simple:
Keep the hottest application data in memory so it can be returned almost instantly while reducing the workload on the database.
What is MySQL HeatWave?
MySQL HeatWave is Oracle’s fully managed MySQL Database Service that natively integrates a massively parallel, in-memory query accelerator (the RAPID engine) directly with a standard MySQL database (via the reliable InnoDBengine).
Core Capabilities:
Dual Acceleration Architecture: Unlike traditional database systems that handle transactional and analytical processes completely separately, HeatWave uses an innovative in-memory engine to dramatically accelerate both standard transactional (OLTP) queries and complex reporting.
True Mixed Workloads (HTAP): It runs real-time transactional operations using InnoDB storage while concurrently routing heavy queries and analytics (OLAP) workloads to the integrated HeatWave Cluster.
Zero Code Changes: The query optimizer automatically decides whether to execute a query on standard InnoDB or route it to the massively parallel HeatWave cluster nodes. It accelerates standard MySQL queries by orders of magnitude, frequently turning long-running SQL operations into near-instantaneous responses, without altering a single line of your application code.
Moreover, MySQL HeatWave also includes integrated capabilities such as:
AutoML
Vector Store
GenAI
Lakehouse
JSON analytics
…
allowing developers to build modern AI-powered applications directly inside MySQL.
Complementary Services, Not Competing Ones
Because both OCI Cache and HeatWave rely heavily on memory, it’s easy to assume they solve the same problem. They don’t!
Their objectives are fundamentally different.
OCI Cache is an in-memory key/value store optimized for retrieving already-known data with extremely low latency.
MySQL HeatWave is an in-memory relational query engine optimized for executing SQL statements, joins, aggregations, and analytical queries over very large datasets.
Think of them as two different optimization layers.
OCI Cache & MySQL HeatWave: Head-to-Head Comparison
OCI Cache and HeatWave solve different problems, so it helps to compare them directly.
Aspect
OCI Cache
MySQL HeatWave
Primary role
Ultra-fast data access for repeated reads
High-speed transactional and analytical processing
Data model
NoSQL Key-Value
Relational tables with SQL
Latency profile
Microseconds to milliseconds
Milliseconds to seconds
Best suited for
User sessions, configuration flags, hot lookup results, …
OLTP, OLAP, AI, reporting
Consistency
Depends on cache strategy and invalidation
ACID transactions, durable
Query engine
Simple key-based access patterns
Full SQL + accelerated analytics
Operational goal
Reduce latency and offload MySQL
Accelerate computation
Scale Mechanism
Shards/replicas and vertical scaling
Scale the HeatWave cluster (nodes) independently of MySQL (read replicas)
As you’ve understood, both are in-memory technologies… but optimized for completely different access patterns.
A simple way to remember the difference:
OCI Cache answers
"Give me product #123."
MySQL HeatWave answers
"Show me the top twenty products purchased by customers living in France during the last two years."
Those are completely different workloads.
Choosing the Right Architecture
Depending on your workload, several architectures are possible.
MySQL HeatWave Only
Ideal when your application requires:
transactional consistency
query acceleration
operational reporting
real-time analytics
AI capabilities
The HeatWave Cluster accelerates SQL execution directly inside MySQL, making it an excellent solution for mixed OLTP/OLAP workloads.
However, HeatWave is not a cache.
Repeated requests for the exact same object still require SQL execution.
OCI Cache in Front of MySQL
This architecture shines for applications with many repeated reads.
Frequently requested objects are stored in OCI Cache, allowing subsequent requests to bypass MySQL entirely.
Benefits include:
dramatically lower latency
reduced database load
improved scalability
better user experience
The trade-off is that applications must manage cache expiration and cache invalidation.
OCI Cache + MySQL HeatWave
For many modern applications, this is the sweet spot:
HeatWave accelerates reporting, dashboards, AI, and complex SQL queries.
Each service focuses on what it does best.
A useful mental model is:
OCI Cache is the fastest path to already-known information.
while
MySQL HeatWave is the fastest path to discovering new information through SQL, analytics, and AI.
Why use both?
Using both services together creates a very balanced architecture. OCI Cache absorbs the repetitive reads that applications perform thousands of times every second.
Meanwhile, MySQL HeatWave focuses on:
transactional integrity
fresh data
analytical queries
dashboards
AI workloads
reporting
The combination is especially attractive when:
your application has a small set of extremely hot data;
you need operational dashboards;
you want to reduce database load;
you want analytics without introducing another database technology;
you prefer a simple, cloud-native architecture.
One way to visualize the relationship is:
OCI Cache reduces how often your application needs to ask the database the same question.
MySQL HeatWave dramatically improves how quickly the database answers every remaining question.
That’s not overlap, that’s architectural layering!
Advantages and Considerations
OCI Cache
Advantages
Extremely low latency
Redis/Valkey protocol compatibility
Fully managed
Reduces read pressure on database instances
Excellent horizontal scalability
Considerations
Adds cache invalidation complexity to application code
No persistence guarantee (ephemeral in nature)
Does not replace a persistent database
MySQL HeatWave
Advantages
SQL support
ACID compliance
Mixed OLTP, OLAP and AI workloads
High-performance analytics
Integrated Machine Learning and GenAI
Persistent storage
Considerations
Not intended for key/value caching
Slightly higher baseline network and query parsing overhead compared to pure memory lookups
Solves a different class of performance problems
Demo: A Python Glimpse at Latency
To show how simple it is to programmatically bridge these two layers, let’s look at a practical Python script.
Instead of mock data, this script connects to MySQL HeatWave using the MySQL Connector/Python to find a specific user, populates (seeds) the OCI Cache with that record as a JSON object using the valkey client, and then runs a back-to-back benchmark to demonstrate the latency difference between the two layers.
The goal isn’t to show MySQL is slow, its own read latency with a primary key is excellent, but to highlight the difference in speed a dedicated cache layer can provide for hot data.
import valkey
import mysql.connector
import time
import json
# ==========================================
# 1. CONNECTION SETUP
# ==========================================
# OCI Cache (Valkey) Connection
cache = valkey.Valkey(
host='abcdefghijklmno.redis.eu-frankfurt-1.oci.oraclecloud.com',
port=6379,
ssl=True,
decode_responses=True
)
# MySQL HeatWave Connection
def get_mysql_conn():
return pymysql.connect(
host='10.0.1.42',
user='dev',
password='My5U4€s3kret',
database='oci-cache_mysql-heatwave',
autocommit=True
)
# ==========================================
# 2. SETUP: SYNC DATA FROM MYSQL TO OCI CACHE
# ==========================================
USER_ID = 42
cache_key = f"user:{USER_ID}"
print(f"[*] Connecting to MySQL HeatWave to fetch data for user {USER_ID}...")
setup_conn = get_mysql_conn()
cursor = setup_conn.cursor(dictionary=True)
cursor.execute("SELECT id, name, role FROM users WHERE id = %s", (USER_ID,))
user_data = cursor.fetchone()
cursor.close()
setup_conn.close()
if not user_data:
raise ValueError(f"User with ID {USER_ID} not found in MySQL. Please verify your seed data.")
# Set the cache with the actual database payload
print(f"[*] Seeding OCI Cache (Valkey) with data from MySQL: {user_data}")
cache.set(cache_key, json.dumps(user_data))
print("[+] Cache primed successfully!\n")
# ==========================================
# 3. LATENCY BENCHMARK
# ==========================================
# --- SCENARIO A: Reading from OCI Cache (Valkey) ---
start_time = time.perf_counter()
cached_data = cache.get(cache_key)
cache_latency = (time.perf_counter() - start_time) * 1000 # Convert to ms
# --- SCENARIO B: Reading from MySQL (InnoDB) ---
conn = get_mysql_conn()
start_time = time.perf_counter()
cursor = conn.cursor(dictionary=True)
cursor.execute("SELECT id, name, role FROM users WHERE id = %s", (USER_ID,))
db_data = cursor.fetchone()
cursor.close()
db_latency = (time.perf_counter() - start_time) * 1000 # Convert to ms
conn.close()
# ==========================================
# 4. RESULTS
# ==========================================
print("--- Latency Benchmark Results ---")
print(f"OCI Cache (Valkey) Read: {cache_latency:.3f} ms")
print(f"MySQL (InnoDB) Read: {db_latency:.3f} ms")
print(f"Speedup Factor: {db_latency / cache_latency:.1f}x faster via Cache")
(venv) daz@sandbox:~/valkey$ python3 demo1_local.py
[*] Connecting to MySQL HeatWave to fetch data for user 42...
[*] Seeding OCI Cache (Valkey) with data from MySQL: {'id': 42, 'name': 'Olivier', 'role': 'admin'}
[+] Cache primed successfully!
--- Latency Benchmark Results ---
OCI Cache (Valkey) Read: 0.078 ms
MySQL (InnoDB) Read: 0.248 ms
Speedup Factor: 3.2x faster via Cache
What this tells us:
When you run this script, you will typically see OCI Cache returning data in fractions of a millisecond, while MySQL takes a few milliseconds.
For a CTO: That millisecond difference, multiplied by millions of daily active users, translates to a scalable architecture, massive infrastructure cost savings and a snappier user experience.
For a Developer: You get access to ultra-fast, microsecond APIs for fast user experiences alongside a fully robust, standard SQL database that seamlessly handles backend transactional logic and reporting.
For a DBA: You successfully offloaded millions of trivial SELECT statements away from your precious MySQL connection pool, keeping the database healthy for complex transactions and HeatWave analytics.
By running both services natively on OCI, you eliminate integration and management overhead, allowing you to focus purely on building fast, scalable applications.
Péroraison
OCI Cache and MySQL HeatWave are not competing services, they operate at different layers of the application stack.
OCI Cache minimizes application latency by serving frequently accessed data directly from memory, while MySQL HeatWave accelerates transactional processing, analytics, machine learning, AI, and SQL execution on persistent data.
Together they enable architects to design applications that are responsive, scalable, analytically powerful, and remarkably simple.
Perhaps the biggest advantage is that both are fully managed Oracle Cloud services. You don’t have to spend your time provisioning servers, configuring clusters, or managing failover. Instead, you can focus on what really matters: building applications that deliver value to your users.
In the end, it’s not about choosing between caching and database acceleration. It’s about using each technology where it delivers the greatest benefit.
MySQL 9.7.0 LTS, released in April 2026, is a major milestone. It establishes the new 9.7.x Long-Term Support line, giving organizations a stable, eight-year supported branch to standardize on, while consolidating the innovations delivered during the 9.x innovation cycle. This release significantly expands MySQL Community Edition with capabilities previously restricted to Enterprise Edition.
This report is designed to be a convenient, go-to document for staying aware of the most important features of MySQL 9.7 and MySQL HeatWave 9.7. It’s structured for quick consumption, with concise summaries and links to deeper resources.
Here’s a sneak peek at what’s inside:
The Shift to MySQL 9.7.0 LTS: What this new Long-Term Support branch means for your stability and upgrade strategy.
Community Edition Upgrades: Some major capabilities moved to the Community Edition, including OpenTelemetry support, DML for JSON Duality Views, and the Hypergraph Optimizer.
Enterprise Enhancements: Deep dives into the new GA Dynamic Data Masking (protect sensitive data without app changes!) and Audit Log improvements.
MySQL HeatWave Exclusives: Features you won’t find in standard editions, such as the newly simplified “Create Replica DB System” for Disaster Recovery on OCI.
Community Contributions
As always, a special thank you to the MySQL community. This release includes contributions from individuals at Alibaba, Amazon, Tencent, Percona, and others, helping to improve the optimizer, parser, prepared statements, and more
Whether you are planning an upgrade, evaluating new features, or simply want to stay informed about the MySQL ecosystem, I hope this report will save you some time :).
As always, feedback is very welcome. If you notice something I missed or have suggestions for future editions, feel free to leave a comment or reach out on social media.
Modern AI systems increasingly rely on multimodal data: text, images, documents, audio, and video. Among these modalities, image understanding has become one of the most important capabilities for AI-powered applications.
Organizations now expect systems to:
Search images using natural language
Compare visually similar assets
Classify and enrich image catalogs
Validate AI-generated descriptions
Build semantic retrieval pipelines
Traditionally, implementing these capabilities required specialized computer vision infrastructure, external vector databases, custom ML pipelines, and multiple frameworks.
With MySQL HeatWave GenAI, many of these capabilities can now be implemented directly inside SQL workflows using built-in AI routines such as:
{ "description": "A red motorcycle parked on a city street near buildings during daytime.",
"keywords": ["motorcycle", "street", "urban", "vehicle", "city"]
}
Step 2: Text -> Embedding
The generated text is then converted into a vector embedding using a text embedding model.
This creates semantic vectors representing the meaning of the image.
Strengths
This approach offers several advantages:
Human-readable and explainable
Unlike direct image embeddings, the generated metadata is understandable by humans.
If a search fails, developers can inspect:
The generated description
The extracted keywords
The semantic interpretation
This creates a powerful audit trail.
SQL-native workflow
Because the representation becomes textual:
Standard embedding models can be used
VECTOR columns integrate naturally
SQL workflows remain simple
This aligns perfectly with MySQL HeatWave GenAI capabilities.
Trade-offs
The quality of retrieval depends heavily on:
Caption quality
Prompt engineering
Vision model performance
Low-level visual details may also be lost:
Texture
Exact shape
Pixel-specific patterns
To summarize
Pros
Human-readable
Easy to debug
Works well with SQL systems
Easier operationalization
Cons
Less precise for purely visual similarity
Depends on generated metadata quality
3. Multimodal Fusion Models
A third category consists of multimodal architectures capable of jointly processing:
Images
Text
Audio
Video
Structured data
These systems use:
Early fusion
Intermediate fusion
Late fusion
Hybrid fusion architectures
Their objective is to create unified multimodal embeddings.
Strengths
Very powerful semantic understanding
Strong cross-modal reasoning
Rich contextual interpretation
Challenges
These architectures are often:
More complex
Harder to operationalize
Less transparent
Difficult to integrate into SQL-centric architectures
Why We Chose the Image -> Text -> Embedding Approach?
In this article, we intentionally choose the Semantic Metadata Embeddings pipeline because it aligns perfectly with MySQL HeatWave GenAI capabilities.
By consolidating these diverse requirements into a single platform, MySQL HeatWave significantly lowers the Total Cost of Ownership (TCO). It eliminates the expensive licensing fees associated with multiple third-party services and external vector databases.
Furthermore, by making AI logic SQL-native and reducing operational complexity, organizations can leverage their existing database expertise rather than hiring specialized ML engineers to manage separate orchestration layers, drastically cutting both infrastructure and personnel overhead.
Summary of TCO Drivers
Skill Set Leverage: AI tasks become accessible to anyone with SQL skills, reducing the need for niche, high-cost specialists.
Reduced Licensing: Consolidating multiple services into one platform removes the need for separate vendor contracts.
Lower Infrastructure Costs: Eliminating dedicated ML infrastructure and external databases reduces the physical or cloud hardware footprint.
Operational Efficiency: Keeping data inside MySQL HeatWave simplifies governance and reduces the labor hours required for complex data movement and synchronization.
Generating Image Understanding with sys.ML_GENERATE
The foundation of our evaluation assistant is the ability to convert images into semantic understanding.
One of the most interesting aspects of ML_GENERATE is that it bridges the gap between:
Structured SQL systems
Unstructured image content
The output becomes structured semantic metadata that can:
Be stored
Indexed
Embedded
Queried
Compared
using standard SQL operations.
Supported Vision Models
The supported vision models can be queried directly from MySQL HeatWave:
mysql>
SELECT *
FROM sys.ML_SUPPORTED_LLMS
WHERE model_id LIKE 'google.gemini%'\G
*************************** 1. row ***************************
provider: OCI Generative AI Service
model_id: google.gemini-2.5-flash
availability_date: 2026-01-22
capabilities: ["GENERATION"]
default_model: 0
*************************** 2. row ***************************
provider: OCI Generative AI Service
model_id: google.gemini-2.5-pro
availability_date: 2026-01-22
capabilities: ["GENERATION"]
default_model: 0
*************************** 3. row ***************************
provider: OCI Generative AI Service
model_id: google.gemini-2.5-flash-lite
availability_date: 2026-01-22
capabilities: ["GENERATION"]
default_model: 0
The model used in this article is:
google.gemini-2.5-pro
The workflow is straightforward:
This transforms unstructured visual data into structured semantic data.
Examples
Object Detection Prompt
mysql>
SELECT image_base64
FROM image_details
WHERE id_image = 1
INTO @image_base64;
SET @prompt_desc_key_json = 'What objects are present?';
SELECT JSON_UNQUOTE(
JSON_EXTRACT(
sys.ML_GENERATE(@prompt_desc_key_json, JSON_OBJECT("model_id", "google.gemini-2.5-pro", "image", @image_base64)),
'$.text'
)
) AS description
\G
*************************** 1. row ***************************
description: Based on the image provided, here are the objects that are present:
**Main Subject:**
* A black motorcycle, which appears to be a Honda Hornet, is the central object. Its visible parts include:
* Wheels and tires
* Engine
* Exhaust pipe and muffler
* Frame
* Seat
* Fuel tank (with a black tank cover on it)
* Handlebars with mirrors and levers
* Front and rear disc brakes
* Gold-colored front forks
**Background and Surroundings:**
* A white or light gray garage door with vertical panels.
* A red brick wall on the left.
* A white drainpipe between the wall and the garage door.
* Pavement or concrete on the ground, with some patches of frost or thin snow.
* Dry, brown leaves scattered on the ground.
Scene Context Prompt
mysql>
SET @prompt_desc_key_json = 'What is the context of this scene?';
SELECT JSON_UNQUOTE(
JSON_EXTRACT(
sys.ML_GENERATE(@prompt_desc_key_json, JSON_OBJECT("model_id", "google.gemini-2.5-pro", "image", @image_base64)),
'$.text'
)
) AS description
\G
*************************** 1. row ***************************
description: Based on the image, the context of the scene is an outdoor, residential setting, likely during a cold season like late autumn or winter.
Here are the key details that establish this context:
* **Subject:** The central focus is a modern, black and silver Honda Hornet motorcycle. It is parked and appears to be the subject of the photo, possibly taken by its owner.
* **Location:** The motorcycle is parked on a paved surface, such as a driveway or a courtyard, in front of white, vertical-paneled garage doors. To the left, there is a red brick wall. This combination suggests a residential area.
* **Season/Weather:** The ground is littered with dead, brown leaves and patches of what looks like frost or old, melting snow. This indicates cold weather, pointing to late autumn or winter. The lighting appears flat and overcast, which is common for that time of year.
This capability becomes the semantic foundation of the evaluation assistant. We are not merely generating captions.
We are enabling:
Queryable visual understanding
Semantic enrichment
Explainable AI retrieval
Text-to-Image Search (Semantic Retrieval)
One of the most powerful applications of semantic embeddings is natural language image search.
The objective is simple:
Input: TextOutput: Relevant Images
But internally, the logic is fundamentally different from traditional image retrieval systems.
mysql>
SET @prompt_desc_key_json = '
Analyze the image and return a minified JSON object.
The JSON must contain two fields:
"description" (a concise, single-paragraph description of visible content with no speculation)
and "keywords" (a JSON array of relevant lowercase visual keywords).
Output ONLY the raw JSON on a single line without any markdown formatting, backticks, or preamble
';
Generate Description and Keywords
mysql>
SELECT JSON_UNQUOTE(
JSON_EXTRACT(
sys.ML_GENERATE(@prompt_desc_key_json, JSON_OBJECT("model_id", "google.gemini-2.5-pro", "image", @image_base64)),
'$.text'
)
) AS description
\G
*************************** 1. row ***************************
description: {"description":"A side profile view of a black Honda Hornet naked motorcycle parked on a paved surface with scattered leaves. The bike has a black frame, engine, and fuel tank, with a silver tail section and exhaust pipe. The front forks are gold-colored. In the background, there is a red brick wall on the left and a light-colored, vertically-paneled garage door on the right.","keywords":["motorcycle","honda","honda hornet","naked bike","street bike","black","parked","outdoors","garage door","brick wall","engine","exhaust","two-wheeler","vehicle","wheel","tire","disc brake"]}
mysql>
SET @embeddOptions = '{"model_id": "cohere.embed-english-v3.0"}';
SET @searchImage = 'street motorcycle';
SELECT sys.ML_EMBED_ROW(
@searchImage,
@embeddOptions
) INTO @searchImageEmbedding;
If you want to see what a vector embedding looks like under the hood, you can use the FROM_VECTOR() function:
mysql>
SELECT from_vector(@searchImageEmbedding)\G
*************************** 1. row ***************************
from_vector(@searchImageEmbedding): [2.09045e-02,1.06277e-02,2.59590e-03,-6.01807e-02,-4.44336e-02,8.44574e-03,-4.88281e-02,8.72803e-03,-1.45645e-02,2.78168e-02,-4.73328e-02,2.53296e-03,-5.47791e-03,-1.36337e-02,4.00696e-02,-1.90735e-02,6.69556e-02,-3.72314e-02,3.95203e-02,2.24457e-02,-1.06964e-02,7.75757e-02,-1
... even more numbers ...
,-3.04871e-02,5.33295e-03,3.96729e-03]
Similarity Search Using Descriptions
mysql>
SELECT
image_name,
DISTANCE(
image_description_embedding,
@searchImageEmbedding,
'COSINE'
) AS min_distance
FROM image_details
ORDER BY min_distance
LIMIT 3;
+-----------------------+---------------------+
| image_name | min_distance |
+-----------------------+---------------------+
| vintage_sidecar.JPG | 0.46460431814193726 |
| Le_Mans_24_moto.png | 0.49795448780059814 |
| hornet_black_gold.jpg | 0.5088052749633789 |
+-----------------------+---------------------+
Similarity Search Using Description + Keywords
mysql>
WITH distances AS (
SELECT
image_name,
(
DISTANCE(
image_keywords_embedding,
@searchImageEmbedding,
'COSINE'
)
+
DISTANCE(
image_description_embedding,
@searchImageEmbedding,
'COSINE'
)
) / 2 AS avg_distance
FROM image_details
)
SELECT *
FROM distances
ORDER BY avg_distance ASC
LIMIT 3;
+-----------------------+---------------------+
| image_name | avg_distance |
+-----------------------+---------------------+
| hornet_black_gold.jpg | 0.48873063921928406 |
| vintage_sidecar.JPG | 0.4904197156429291 |
| Yamaha_DTX.jpg | 0.5230678915977478 |
+-----------------------+---------------------+
Simple Re-ranking Strategy
mysql>
WITH initial_results AS (
SELECT
image_name,
DISTANCE(
image_keywords_embedding,
@searchImageEmbedding,
'COSINE'
) AS keywords_distance,
DISTANCE(
image_description_embedding,
@searchImageEmbedding,
'COSINE'
) AS description_distance
FROM image_details
ORDER BY keywords_distance + description_distance
LIMIT 15
),
reranked_results AS (
SELECT
image_name,
(
0.3 * keywords_distance +
0.7 * description_distance
) AS combined_distance
FROM initial_results
)
SELECT *
FROM reranked_results
ORDER BY combined_distance ASC
LIMIT 3;
+-----------------------+--------------------+
| image_name | combined_distance |
+-----------------------+--------------------+
| vintage_sidecar.JPG | 0.4800935566425323 |
| hornet_black_gold.jpg | 0.496760493516922 |
| Le_Mans_24_moto.png | 0.5196295261383057 |
+-----------------------+--------------------+
Key Insight
The most important concept here is this:
We are not comparing images.
We are comparing semantic representations of images.
This distinction fundamentally changes how image retrieval systems can be designed using SQL-native AI capabilities.
Reverse Image Search (Image-to-Image via Semantics)
Traditional reverse image search systems rely heavily on:
Pixel similarity
Feature extraction
Specialized computer vision pipelines
Our approach is different.
We perform Semantic Image-to-Image Search.
This is:
Similarity of meaning
Not similarity of pixels
Workflow
Why This Is Interesting
Two images may be visually different while still being semantically related.
For example:
Different motorcycles
Different lighting conditions
Different camera angles
Yet both images may describe: « A motorcycle parked on an urban street ».
Semantic retrieval captures this meaning.
Advantages of the Semantic Approach
Simpler Architecture
No need for:
Complex Computer Vision frameworks
GPU-heavy image feature pipelines
Specialized image vector databases
Explainability
Because the retrieval is based on generated semantic metadata:
Results can be audited
Descriptions can be refined
Prompts can be improved
SQL-native Workflow
Everything remains inside MySQL HeatWave:
Metadata
Embeddings
Similarity search
Ranking logic
Architectural Trade-offs & Design Considerations
Building AI-powered semantic image systems involves several important architectural decisions.
Description vs Keywords
Both representations serve different purposes.
Descriptions
Descriptions provide:
Rich contextual understanding
Better semantic reasoning
Natural language flexibility
However:
They may introduce noise
Longer text may dilute embeddings
Keywords
Keywords provide:
Focused semantic signals
Better precision
Faster matching
However:
They lose contextual richness
Strategy implemented in this article
A hybrid approach:
Used descriptions for semantic context
Used keywords for precision
Combined both during re-ranking
However
I’m storing two embeddings doubles vector storage (2 × 2048 dimensions per image). For very large libraries, consider using only one – either concatenated or choose the one that performs better on your validation set.
Storing Images in the Database vs Object Storage
Another important design consideration concerns image storage.
Storing Images in MySQL
Advantages:
Simpler architecture
Centralized governance
Easier transactional consistency
Disadvantages:
Larger database size increases buffer pool pressure
Store thumbnails (small, e.g., 512×512) as base64 in MySQL (should be sufficient for vision model analysis)
Store full-resolution images in object storage for later retrieval or user download
This enables:
Fast previews
Efficient semantic retrieval
Scalable storage architecture
Prompt Engineering Matters
Semantic quality heavily depends on prompts.
Poor prompts generate:
Vague descriptions
Weak keywords
Low-quality embeddings
Well-designed prompts improve:
Retrieval quality
Explainability
Evaluation consistency
Prompt engineering becomes a critical part of semantic architecture design.
Péroraison
MySQL HeatWave GenAI significantly simplifies the implementation of semantic image understanding systems while substantially lowering the Total Cost of Ownership through the elimination of external infrastructure and fragmented AI pipelines.
By combining:
Vision-language models
Text embeddings
Vector search
SQL-native AI routines
organizations can build powerful multimodal systems directly inside MySQL HeatWave.
The « image -> text -> embedding » approach presented in this article provides:
Explainability
Operational simplicity
SQL-native integration
Semantic search capabilities
Most importantly, it enables architects and developers to build AI systems that remain understandable, debuggable, and governable — which is often one of the biggest challenges in enterprise AI adoption.
In Ask Your Database Anything: Natural Language to SQL (NL2SQL) in MySQL HeatWave, we have explored the innovative MySQL HeatWave GenAI technology that converts Natural Language into SQL, making it easier for you to interact with databases. This feature collects information on the schemas, tables, and columns that you have access to, and then uses a Large Language Model (LLM) to generate an SQL query for the question pertaining to your data. It also lets you run the generated query and view the result set.
Following our last article, Let Your AI DBA Assistant Write Your MySQL Queries, showcased an interesting use case: leveraging an AI DBA Assistant to generate monitoring and tuning queries for the Performance, Information, and Sys Schemas using plain English.
In this article, we will talk about how to handle JSON documents with MySQL HeatWave GenAI NL2SQL feature.
SQL>
-- HeatWave MySQL server version
SHOW VARIABLES WHERE Variable_name IN ('version_comment', 'version');
+-----------------+--------------------------+
| Variable_name | Value |
+-----------------+--------------------------+
| version | 9.4.2-cloud |
| version_comment | MySQL Enterprise - Cloud |
+-----------------+--------------------------+
CREATE TABLE `country_json_nested` (
`ID` int NOT NULL AUTO_INCREMENT COMMENT 'Primary key: integer that uniquely identifies country_json JSON documents.',
`country_description` json NOT NULL COMMENT 'Country information. Including country names, continent, region, surface area, identifiers (country code), demographics, economy (GNP), and government structure',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Stores detailed information about countries, including identifiers, demographics, economy, and government structure.'
;
CREATE TABLE `country_language_json_nested` (
`ID` int NOT NULL AUTO_INCREMENT COMMENT 'Primary key: integer that uniquely identifies country_language_json JSON documents.',
`country_language_description` json NOT NULL COMMENT 'Country languages information. Including a three-letter country code referencing the country table, the name of the language spoken in the country, whether they are official and the share of the population using them.',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Stores information about the languages spoken in each country, including whether they are official and the share of the population using them.'
;
CREATE TABLE `city_json_nested` (
`ID` int NOT NULL AUTO_INCREMENT COMMENT 'Primary key: integer that uniquely identifies city_json JSON documents.',
`city_description` json NOT NULL COMMENT 'Cities information. including their name, country, district, and population.',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Stores information about cities, including their name, country, district, and population.'
;
Let’s run 3 queries on the data set:
MySQL>
-- Find the name, country code and the district of the city with ID 2989
SELECT
city_description->>"$.city_name" AS "City Name",
city_description->>"$.location.country_code" AS "Country Code",
city_description->>"$.location.district" AS District
FROM city_json_nested
WHERE city_description->>"$.ID_city" = 2989;
+-----------+--------------+-------------+
| city_name | country_code | district |
+-----------+--------------+-------------+
| Grenoble | FRA | Rhône-Alpes |
+-----------+--------------+-------------+
-- Find the government form of Germany
SELECT
country_description->>"$.government.government_form" AS "Government Form"
FROM country_json_nested
WHERE country_description->>"$.identifiers.code" = 'FRA';
+-----------------+
| government_form |
+-----------------+
| Republic |
+-----------------+
-- List all official languages spoken in Canada
SELECT
country_language_description->>"$.language_info.language" AS Language
FROM country_language_json_nested
WHERE country_language_description->>"$.country.country_code" = 'CAN'
AND country_language_description->>"$.language_info.is_official" = 'T';
+----------+
| language |
+----------+
| English |
| French |
+----------+
I’m employing the inline path operator (->>), which serves as a convenient shorthand for extracting and unquoting values: JSON_UNQUOTE(JSON_EXTRACT(...)).
The execution of these queries, while simple, still relies on existing SQL and JSON expertise. MySQL HeatWave GenAI can eliminates this dependency by enabling users to interact with the database via Natural Language to SQL. As highlighted in “Ask Your Database Anything: Natural Language to SQL in MySQL HeatWave” the new NL_SQL routine allows non-technical users to write requests in plain English, and have the system automatically generate the necessary SQL.
Introducing JSON
JSON (JavaScript Object Notation) is a lightweight, human-readable format for structuring and exchanging data. Built around key–value pairs and ordered arrays, JSON enables developers to model complex, hierarchical information without the rigidity of a fixed schema. Its simplicity, flexibility, and near-universal support across programming languages have made it the standard for transmitting structured data between servers, web applications, and APIs in modern software systems.
JSON in the MySQL Context
In MySQL, the native JSON data type — introduced in version 5.7 — brings NoSQL-style flexibility into the realm of relational databases. It allows developers to store, validate, and optimize JSON documents directly within columns, combining the ACID compliance and transactional integrity of MySQL with the dynamic nature of semi-structured data. Specialized JSON functions such as JSON_EXTRACT(), JSON_CONTAINS(), JSON_OBJECT() or JSON_TABLE() enable precise querying and manipulation, while indexing options like generated columns ensure high performance. This hybrid approach lets MySQL handle everything from traditional records to complex API payloads within a unified, scalable system.
Some MySQL & JSON content that you may find useful:
NL2SQL in MySQL HeatWave GenAI is easy to use and highly effective when configured with the right context. To help the LLM generate accurate queries, it’s important to narrow its focus by specifying the relevant schemas and tables through the schemas and tables parameters. Additionally, using clear, descriptive names for tables, columns, and views ensures the model can better interpret their purpose and produce more precise SQL statements.
The core difficulty stems from JSON’s structure: the document simultaneously holds both data and metadata (keys), yet MySQL treats the entire document as undifferentiated raw data within a single column. From the database’s perspective, the column contains a blob of JSON, meaning there’s little or no external schema metadata — such as explicit column names, data types, or constraints — to help the LLM interpret the semantic meaning or internal structure of the fields. This inherent lack of external context makes intelligent processing more challenging.
MySQL nl2sql_world_json_nested SQL> SET @nlq = "Number of cities";
Query OK, 0 rows affected (0.0003 sec)
MySQL nl2sql_world_json_nested SQL> CALL sys.NL_SQL(@nlq, @output, '{"model_id": "meta.llama-3.3-70b-instruct", "schemas":["nl2sql_world_json_nested"]}');
+-----------------------------------------------------------------------+
| Executing generated SQL statement... |
+-----------------------------------------------------------------------+
| SELECT COUNT(`ID`) FROM `nl2sql_world_json_nested`.`city_json_nested` |
+-----------------------------------------------------------------------+
1 row in set (1.6583 sec)
+-------------+
| COUNT(`ID`) |
+-------------+
| 4079 |
+-------------+
1 row in set (1.6583 sec)
That was straightforward!
Now, let’s raise the bar with a slightly more complex query:
MySQL nl2sql_world_json_nested SQL> SET @nlq = "Population of Monaco";
Query OK, 0 rows affected (0.0003 sec)
MySQL nl2sql_world_json_nested SQL> CALL sys.NL_SQL(@nlq, @output, '{"model_id": "meta.llama-3.3-70b-instruct", "schemas":["nl2sql_world_json_nested"]}');
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Executing generated SQL statement... |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT JSON_EXTRACT(`city_description`, '$.population') FROM `nl2sql_world_json_nested`.`city_json_nested` WHERE JSON_EXTRACT(`city_description`, '$.name') = 'Monaco' |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (2.1982 sec)
Empty set (2.1982 sec)
Query OK, 0 rows affected (2.1982 sec)
MySQL nl2sql_world_json_nested SQL> SET @nlq = "List all official languages spoken in Canada";
Query OK, 0 rows affected (0.0004 sec)
MySQL nl2sql_world_json_nested SQL> CALL sys.NL_SQL(@nlq, @output, '{"model_id": "meta.llama-3.3-70b-instruct", "schemas":["nl2sql_world_json_nested"]}');
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Executing generated SQL statement... |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT JSON_EXTRACT(`country_language_description`, '$[*].language') FROM `nl2sql_world_json_nested`.`country_language_json_nested` WHERE JSON_EXTRACT(`country_language_description`, '$[*].country_code') = '"CAN"' AND JSON_EXTRACT(`country_language_description`, '$[*].is_official') = 'true' |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (2.3889 sec)
Empty set (2.3889 sec)
Query OK, 0 rows affected (2.3889 sec)
In both of these recent cases, the generated queries were syntactically correct, but the system failed to produce a semantically relevant query.
But all is not lost…
Workaround: Defining Views for Better Context
A highly effective workaround is to introduce well-defined views over the existing table. The main idea is to expose the JSON’s internal structure by effectively translating the semi-structured data into a fully relational schema. By using descriptive, explicit column names in these views, you furnish the NL2SQL model with the necessary semantic metadata. This strategy significantly improves accuracy for complex JOIN operations and accurate filtering by providing clear, accessible relational keys.
CREATE OR REPLACE VIEW country_nested_flat AS
SELECT
ID AS country_id,
-- Identifiers
JSON_UNQUOTE(JSON_EXTRACT(country_description, '$.identifiers.code')) AS country_code,
JSON_UNQUOTE(JSON_EXTRACT(country_description, '$.identifiers.code2')) AS country_code2,
JSON_UNQUOTE(JSON_EXTRACT(country_description, '$.identifiers.country_name')) AS country_name,
JSON_UNQUOTE(JSON_EXTRACT(country_description, '$.identifiers.local_name')) AS local_name,
-- Geography
JSON_UNQUOTE(JSON_EXTRACT(country_description, '$.geography.continent')) AS continent,
JSON_UNQUOTE(JSON_EXTRACT(country_description, '$.geography.region')) AS region,
CAST(JSON_EXTRACT(country_description, '$.geography.surface_area') AS DECIMAL(12,2)) AS surface_area,
CAST(JSON_EXTRACT(country_description, '$.geography.ID_capital') AS SIGNED) AS capital_id,
-- Demographics
CAST(JSON_EXTRACT(country_description, '$.demographics.country_population') AS SIGNED) AS population,
CAST(JSON_EXTRACT(country_description, '$.demographics.life_expectancy') AS DECIMAL(5,2)) AS life_expectancy,
CAST(JSON_EXTRACT(country_description, '$.demographics.independance_year') AS SIGNED) AS independance_year,
-- Economy
CAST(JSON_EXTRACT(country_description, '$.economy.GNP') AS DECIMAL(15,2)) AS gnp,
CAST(JSON_EXTRACT(country_description, '$.economy.GNPOld') AS DECIMAL(15,2)) AS gnp_old,
-- Government
JSON_UNQUOTE(JSON_EXTRACT(country_description, '$.government.head_of_state')) AS head_of_state,
JSON_UNQUOTE(JSON_EXTRACT(country_description, '$.government.government_form')) AS government_form
FROM country_json_nested;
CREATE OR REPLACE VIEW country_language_nested_flat AS
SELECT
ID AS language_id,
-- Country reference
JSON_UNQUOTE(JSON_EXTRACT(country_language_description, '$.country.country_code')) AS country_code,
-- Language details
JSON_UNQUOTE(JSON_EXTRACT(country_language_description, '$.language_info.language')) AS language,
JSON_UNQUOTE(JSON_EXTRACT(country_language_description, '$.language_info.is_official')) AS is_official,
CAST(JSON_EXTRACT(country_language_description, '$.language_info.percentage') AS DECIMAL(5,2)) AS percentage
FROM country_language_json_nested;
CREATE OR REPLACE VIEW city_nested_flat AS
SELECT
ID AS json_id,
-- City identifiers
CAST(JSON_EXTRACT(city_description, '$.ID_city') AS SIGNED) AS city_id,
JSON_UNQUOTE(JSON_EXTRACT(city_description, '$.city_name')) AS city_name,
-- Location details
JSON_UNQUOTE(JSON_EXTRACT(city_description, '$.location.district')) AS district,
JSON_UNQUOTE(JSON_EXTRACT(city_description, '$.location.country_code')) AS country_code,
-- Demographics
CAST(JSON_EXTRACT(city_description, '$.city_population') AS SIGNED) AS population
FROM city_json_nested;
Let’s now re-run our three previous queries, applying this new view-based approach:
MySQL nl2sql_world_json_nested SQL> SET @nlq = "Number of cities";
Query OK, 0 rows affected (0.0004 sec)
MySQL nl2sql_world_json_nested SQL> CALL sys.NL_SQL(@nlq, @output, '{"model_id": "meta.llama-3.3-70b-instruct", "schemas":["nl2sql_world_json_nested"]}');
+----------------------------------------------------------------------------+
| Executing generated SQL statement... |
+----------------------------------------------------------------------------+
| SELECT COUNT(`city_id`) FROM `nl2sql_world_json_nested`.`city_nested_flat` |
+----------------------------------------------------------------------------+
1 row in set (1.8282 sec)
+------------------+
| COUNT(`city_id`) |
+------------------+
| 4079 |
+------------------+
1 row in set (1.8282 sec)
Query OK, 0 rows affected (1.8282 sec)
MySQL nl2sql_world_json_nested SQL> SET @nlq = "Population of Monaco";
Query OK, 0 rows affected (0.0004 sec)
MySQL nl2sql_world_json_nested SQL> CALL sys.NL_SQL(@nlq, @output, '{"model_id": "meta.llama-3.3-70b-instruct", "schemas":["nl2sql_world_json_nested"]}');
+-----------------------------------------------------------------------------------------------------------+
| Executing generated SQL statement... |
+-----------------------------------------------------------------------------------------------------------+
| SELECT `population` FROM `nl2sql_world_json_nested`.`country_nested_flat` WHERE `country_name` = 'Monaco' |
+-----------------------------------------------------------------------------------------------------------+
1 row in set (2.0889 sec)
+------------+
| population |
+------------+
| 34000 |
+------------+
1 row in set (2.0889 sec)
Query OK, 0 rows affected (2.0889 sec)
MySQL nl2sql_world_json_nested SQL> SET @nlq = "List all official languages spoken in Canada";
Query OK, 0 rows affected (0.0004 sec)
MySQL nl2sql_world_json_nested SQL> CALL sys.NL_SQL(@nlq, @output, '{"model_id": "meta.llama-3.3-70b-instruct", "schemas":["nl2sql_world_json_nested"]}');
+---------------------------------------------------------------------------------------------------------------------------------------+
| Executing generated SQL statement... |
+---------------------------------------------------------------------------------------------------------------------------------------+
| SELECT `language` FROM `nl2sql_world_json_nested`.`country_language_nested_flat` WHERE `country_code` = 'CAN' AND `is_official` = 'T' |
+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (2.0249 sec)
+----------+
| language |
+----------+
| English |
| French |
+----------+
2 rows in set (2.0249 sec)
Query OK, 0 rows affected (2.0249 sec)
Q.E.D.
Peroraison
Bridging natural language processing and semi-structured data presents both exciting opportunities and unique challenges. MySQL HeatWave GenAI’s NL2SQL capability demonstrates how natural language can simplify interaction with complex data systems, even when working with intricate JSON documents. However, because JSON stores both data and metadata together, the absence of explicit schema information can limit how effectively an LLM interprets and formulates queries. Creating well-structured views that expose JSON’s internal organization offers a practical solution — transforming unstructured data into meaningful relational context. Ultimately, this approach not only enhances NL2SQL’s accuracy but also showcases how MySQL HeatWave continues to evolve as a powerful engine for intelligent, natural language–driven analytics.
Having explored the innovative MySQL HeatWave technology that converts Natural Language into SQL (Ask Your Database Anything: Natural Language to SQL in MySQL HeatWave), our next article in this series, dives into a practical use case demonstrating how an AI DBA Assistant can significantly simplify your query generation workflow.
In MySQL, there are 3 specialized system schemas designed to give DBAs and developers deeper visibility and control over the server. Together, they provide the tools needed to monitor performance, inspect metadata, and simplify management tasks:
The Performance Schema is a powerful instrumentation framework designed for low-level monitoring of server execution, enabling administrators and developers to gain deep insights into how the database is running. Unlike general status metrics, it collects highly detailed statistics about server events and resource usage directly from the server internals in real time, with the data stored in memory. It is particularly useful for for live diagnostics and performance tuning. The primary purpose of the Performance Schema is to expose what is happening inside the MySQL server and, more importantly, why certain operations may be slow. This makes it an invaluable tool for tasks such as identifying poorly performing queries, diagnosing I/O wait bottlenecks, or analyzing mutex contention in multithreaded workloads. By surfacing these low-level insights, the Performance Schema empowers users to move beyond surface-level monitoring and perform precise root-cause analysis of performance issues.
The Information Schema is the SQL-standard-compliant interface for accessing metadata about the objects managed by the server. Acting as a central directory, it provides a structured view of databases, tables, columns, indexes, privileges, and overall server characteristics, making it the go-to source for understanding the logical organization of a MySQL instance. Its primary purpose is to expose database metadata—answering questions about what objects exist and how they are structured, rather than how they perform. While the metadata itself is stored on disk, MySQL presents it through in-memory tables that can be queried like regular tables. Typical use cases include retrieving a list of all tables in a specific database, checking column data types, or examining indexes and privileges. By adhering to the SQL standard, the Information Schema ensures portability and consistency, allowing users to interact with MySQL metadata in a way that aligns with other relational database systems.
The Sys Schema is a set of user-friendly views, functions, and procedures that sits on top of the Performance Schema and Information Schema, transforming their often complex and technical data into a more readable, actionable format. Its main purpose is to simplify the process of interpreting server metadata and performance statistics, making it much easier for DBAs and developers to diagnose issues and optimize workloads without having to manually parse through raw instrumentation data. By aggregating and presenting information from both underlying schemas, the Sys Schema provides clear insights into common administrative tasks, such as identifying the most time-consuming queries, monitoring active sessions, or detecting unused indexes. In essence, it acts as a usability layer, bridging the gap between MySQL’s powerful but intricate internal schemas and the practical needs of day-to-day database operations.
Together, these schemas form the foundation for effective MySQL performance tuning, troubleshooting, and administration.
However, fully leveraging the Performance Schema, Information Schema, and Sys Schema requires a solid command of SQL to query the data they expose. This is where AI can bridge the gap. As explored in my article Ask Your Database Anything: Natural Language to SQL in MySQL HeatWave, MySQL HeatWave’s Natural Language to SQL (NL2SQL) capabilities make it possible to interact with your database by simply asking questions in plain English, without writing complex queries.
Let’s walk through a concrete example (a special thanks to my colleague Ivan for inspiring this idea):
MySQL’s Performance, Information, and Sys Schemas give DBAs powerful tools to monitor, tune, and troubleshoot servers, while HeatWave NL2SQL makes those insights accessible in simple English.
The ability to query data efficiently has always been central to unlocking insights, but writing SQL can be a barrier for many users who aren’t fluent in the language of databases. Analysts, product managers, and business users often know the questions they want to ask—just not how to express them in SQL. With the rise of large language models (LLMs) and advancements in database technology, that gap is closing quickly.
MySQL HeatWave now brings Natural Language to SQL (NL2SQL) capabilities directly into the database engine, allowing users to generate SQL queries from plain English statements. Instead of wrestling with complex joins, filters, or aggregate functions, users can simply type a natural-language request—such as “Show me the top 10 products by revenue this quarter”—and HeatWave automatically translates it into an SQL query.
In this article, we’ll explore how to leverage LLM-powered NL2SQL in MySQL HeatWave, walk through practical examples, and show how this feature empowers both technical and non-technical users to interact with data more intuitively.
SQL>
-- HeatWave MySQL server version
SHOW VARIABLES WHERE Variable_name IN ('version_comment', 'version');
+-----------------+--------------------------+
| Variable_name | Value |
+-----------------+--------------------------+
| version | 9.4.1-cloud |
| version_comment | MySQL Enterprise - Cloud |
+-----------------+--------------------------+
Furthermore, I’m using a modified version (available on my GitHub account) of the well-known World database. Although the data hasn’t been updated in some time, it still serves as a useful tool for visualizing the results of SQL queries.
Here the schema:
CREATE TABLE `country` (
`code` char(3) NOT NULL DEFAULT '' COMMENT 'Primary key: three-letter country code (ISO standard).',
`country_name` char(52) NOT NULL DEFAULT '' COMMENT 'Official name of the country.',
`continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia' COMMENT 'Continent where the country is located.',
`region` char(26) NOT NULL DEFAULT '' COMMENT 'Geographical region within the continent.',
`surface_area` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Total surface area of the country in square kilometers.',
`independance_year` smallint DEFAULT NULL COMMENT 'Year the country achieved independence (NULL if unknown).',
`country_population` int NOT NULL DEFAULT '0' COMMENT 'Total population of the country.',
`life_expectancy` decimal(3,1) DEFAULT NULL COMMENT 'Average life expectancy of the population in years.',
`GNP` decimal(10,2) DEFAULT NULL COMMENT 'Gross National Product of the country in millions of USD.',
`GNPOld` decimal(10,2) DEFAULT NULL COMMENT 'Gross National Product in an earlier year for comparison.',
`local_name` char(45) NOT NULL DEFAULT '' COMMENT 'The country’s name in its local language.',
`government_form` char(45) NOT NULL DEFAULT '' COMMENT 'Description of the form of government (e.g., Republic, Monarchy).',
`head_of_state` char(60) DEFAULT NULL COMMENT 'Name of the current head of state (e.g., President, Monarch).',
`ID_capital` int DEFAULT NULL COMMENT 'ID of the capital city (foreign key reference to city table).',
`code2` char(2) NOT NULL DEFAULT '' COMMENT 'Two-letter country code (ISO standard).',
PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Stores detailed information about countries, including identifiers, demographics, economy, and government structure.'
;
CREATE TABLE `country_language` (
`country_code` char(3) NOT NULL DEFAULT '' COMMENT 'Three-letter country code referencing the country table. First part of the composite primary key.',
`language` char(30) NOT NULL DEFAULT '' COMMENT 'Name of the language spoken in the country. Second part of the composite primary key.',
`is_official` enum('T','F') NOT NULL DEFAULT 'F' COMMENT 'Indicates whether the language is an official language of the country (T = true, F = false).',
`percentage` decimal(4,1) NOT NULL DEFAULT '0.0' COMMENT 'Percentage of the country’s population that speaks this language.',
PRIMARY KEY (`country_code`,`language`),
KEY `country_code` (`country_code`),
CONSTRAINT `country_language_ibfk_1` FOREIGN KEY (`country_code`) REFERENCES `country` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Stores information about the languages spoken in each country, including whether they are official and the share of the population using them.'
;
CREATE TABLE `city` (
`ID_city` int NOT NULL AUTO_INCREMENT COMMENT 'Primary key: integer that uniquely identifies each city.',
`city_name` char(35) NOT NULL DEFAULT '' COMMENT 'Name of the city.',
`country_code` char(3) NOT NULL DEFAULT '' COMMENT 'Three-letter country code referencing the country table.',
`district` char(20) NOT NULL DEFAULT '' COMMENT 'District or administrative region where the city is located.',
`city_population` int NOT NULL DEFAULT '0' COMMENT 'Population count of the city.',
PRIMARY KEY (`ID_city`),
KEY `country_code` (`country_code`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_code`) REFERENCES `country` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Stores information about cities, including their name, country, district, and population.'
;
As you can see, the main difference from the original version is that I embedded the documentation directly into the tables using COMMENT clauses. In addition, some column names have been renamed. The goal is to capture as much relevant information as possible to provide business context. This contextual information will then be used to augment the LLM.
Let’s run 3 queries on the data set:
MySQL>
-- Find the name, country code and the district of the city with ID 2989
SELECT city_name, country_code, district
FROM city
WHERE ID_city = 2989;
+-----------+--------------+-------------+
| city_name | country_code | district |
+-----------+--------------+-------------+
| Grenoble | FRA | Rhône-Alpes |
+-----------+--------------+-------------+
-- Find the government form of Germany
SELECT government_form
FROM country
WHERE code = 'FRA';
+-----------------+
| government_form |
+-----------------+
| Republic |
+-----------------+
-- List all official languages spoken in Canada
SELECT language
FROM country_language
WHERE country_code = 'CAN' AND is_official = 'T';
+----------+
| language |
+----------+
| English |
| French |
+----------+
These queries are fairly simple, but they still require some SQL skills. What if you could write in plain English and have MySQL HeatWave understand you? This is now possible with the NL_SQL routine, which generates and executes SQL queries directly from natural language statements.
Generate SQL Queries From Natural-Language Statements
Starting with MySQL HeatWave 9.4.1, MySQL HeatWave GenAI (on OCI, AWS, and Azure) allows you with the NL_SQL stored procedure, to generate SQL queries directly from natural language, making it easier to interact with your databases.
The feature gathers information about the schemas, tables, and columns you have access to, then leverages a Large Language Model to generate an appropriate SQL query based on your request. You can also execute the generated query and view the results instantly.
Also as of MySQL HeatWave 9.3.2, you can view the list of available Large Language Models and embedding models directly from the database using the sys.ML_SUPPORTED_LLMS view. This enhancement, part of the MySQL HeatWave GenAI feature, allows you to stay current with the available models, including in-database models and those from external services likeOracle Cloud Infrastructure (OCI) Generative AI.
To display the list of supported content generation LLMs and their provider, you can use the following simple query:
MySQL>
SELECT model_id, provider
FROM sys.ML_SUPPORTED_LLMS
WHERE capabilities LIKE '["GENERATION"]';
+-------------------------------+---------------------------+
| model_id | provider |
+-------------------------------+---------------------------+
| llama2-7b-v1 | HeatWave |
| llama3-8b-instruct-v1 | HeatWave |
| llama3.1-8b-instruct-v1 | HeatWave |
| llama3.2-1b-instruct-v1 | HeatWave |
| llama3.2-3b-instruct-v1 | HeatWave |
| mistral-7b-instruct-v1 | HeatWave |
| mistral-7b-instruct-v3 | HeatWave |
| cohere.command-latest | OCI Generative AI Service |
| cohere.command-plus-latest | OCI Generative AI Service |
| cohere.command-a-03-2025 | OCI Generative AI Service |
| meta.llama-3.3-70b-instruct | OCI Generative AI Service |
| cohere.command-r-08-2024 | OCI Generative AI Service |
| cohere.command-r-plus-08-2024 | OCI Generative AI Service |
+-------------------------------+---------------------------+
We can see here, the in-MySQL HeatWave models (provider HeatWave) as well as Oracle Cloud Infrastructure Generative AI service models (provider OCI Generative AI Service).
To view or use the OCI Generative AI Service models in this list, you need to enable the database system to access OCI services. For more information, see Authenticate OCI Generative AI Service.
When using the sys.NL_SQL routine in MySQL HeatWave, you have several options for the underlying (LLM) that translates your natural language queries into SQL. The available models are part of the MySQL HeatWave GenAI feature. You can select from in-database models or, like we have seen above, if your environment is configured for it, models from the Oracle Cloud Infrastructure (OCI) Generative AI Service.
In MySQL HeatWave 9.4.1, some of the available models for natural language to SQL tasks include:
meta.llama-3.3-70b-instruct (OCI Generative AI Service)
llama3.1-8b-instruct-v1 (In-database HeatWave)
llama3.2-3b-instruct-v1 (In-database HeatWave)
The specific models available may vary by MySQL HeatWave version and the cloud service region you are using. To get the most current list, don’t forget that you can always query the sys.ML_SUPPORTED_LLMS view.
What Can I Ask My Database? Example Queries
Now, let’s see how it works in action. We’ll start with our earlier queries and then try out a few new ones.
Find the name, country code and the district of the city with ID 2989:
MySQL> SET @nlq = "Find the name, country code and the district of the city with ID 2989";
Query OK, 0 rows affected (0.0004 sec)
MySQL> CALL sys.NL_SQL(@nlq, @output, '{"model_id": "llama3.2-3b-instruct-v1", "schemas":["nl2sql_world"]}');
+--------------------------------------------------------------------------------------------------+
| Executing generated SQL statement... |
+--------------------------------------------------------------------------------------------------+
| SELECT `city_name`, `country_code`, `district` FROM `nl2sql_world`.`city` WHERE `ID_city` = 2989 |
+--------------------------------------------------------------------------------------------------+
1 row in set (3.7089 sec)
+-----------+--------------+-------------+
| city_name | country_code | district |
+-----------+--------------+-------------+
| Grenoble | FRA | Rhône-Alpes |
+-----------+--------------+-------------+
1 row in set (3.7089 sec)
Find the government form of Germany:
MySQL> SET @nlq = "Find the government form of Germany";
Query OK, 0 rows affected (0.0003 sec)
MySQL> CALL sys.NL_SQL(@nlq, @output, '{"model_id": "llama3.1-8b-instruct-v1", "schemas":["nl2sql_world"]}');
+-----------------------------------------------------------------------------+
| Executing generated SQL statement... |
+-----------------------------------------------------------------------------+
| SELECT `government_form` FROM `nl2sql_world`.`country` WHERE `code` = 'DEU' |
+-----------------------------------------------------------------------------+
1 row in set (4.0998 sec)
+------------------+
| government_form |
+------------------+
| Federal Republic |
+------------------+
1 row in set (4.0998 sec)
List all official languages spoken in Canada:
MySQL> SET @nlq = "Find the top 5 most populated cities in the world";
Query OK, 0 rows affected (0.0002 sec)
MySQL> CALL sys.NL_SQL(@nlq, @output, '{"model_id": "meta.llama-3.3-70b-instruct", "schemas":["nl2sql_world"]}');
+----------------------------------------------------------------------------------------------------------+
| Executing generated SQL statement... |
+----------------------------------------------------------------------------------------------------------+
| SELECT `city_name`, `city_population` FROM `nl2sql_world`.`city` ORDER BY `city_population` DESC LIMIT 5 |
+----------------------------------------------------------------------------------------------------------+
1 row in set (2.6276 sec)
+-----------------+-----------------+
| city_name | city_population |
+-----------------+-----------------+
| Mumbai (Bombay) | 10500000 |
| Seoul | 9981619 |
| São Paulo | 9968485 |
| Shanghai | 9696300 |
| Jakarta | 9604900 |
+-----------------+-----------------+
5 rows in set (2.6276 sec)
Find the top 10 most multilingual countries (by number of languages spoken):
MySQL> SET @nlq = "Find the top 10 most multilingual countries (by number of languages spoken)";
Query OK, 0 rows affected (0.0004 sec)
MySQL> CALL sys.NL_SQL(@nlq, @output, '{"model_id": "meta.llama-3.3-70b-instruct", "schemas":["nl2sql_world"]}');
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Executing generated SQL statement... |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT `T1`.`country_name`, COUNT(`T2`.`language`) AS `num_languages` FROM `nl2sql_world`.`country` AS `T1` JOIN `nl2sql_world`.`country_language` AS `T2` ON `T1`.`code` = `T2`.`country_code` GROUP BY `T1`.`code` ORDER BY `num_languages` DESC LIMIT 10 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (5.8831 sec)
+--------------------+---------------+
| country_name | num_languages |
+--------------------+---------------+
| United States | 12 |
| China | 12 |
| India | 12 |
| Canada | 12 |
| Russian Federation | 12 |
| Tanzania | 11 |
| South Africa | 11 |
| Philippines | 10 |
| Iran | 10 |
| Kenya | 10 |
+--------------------+---------------+
10 rows in set (5.8831 sec)
List all official languages spoken in Canada:
MySQL> SET @nlq = "List all official languages spoken in Canada";
Query OK, 0 rows affected (0.0003 sec)
MySQL> CALL sys.NL_SQL(@nlq, @output, '{"model_id": "llama3.2-3b-instruct-v1", "schemas":["nl2sql_world"]}');
+------------------------------------------------------------------------------------------------------------------+
| Executing generated SQL statement... |
+------------------------------------------------------------------------------------------------------------------+
| SELECT `language` FROM `nl2sql_world`.`country_language` WHERE `is_official` = 'T' AND `country_code` IN ('CAN') |
+------------------------------------------------------------------------------------------------------------------+
1 row in set (3.0730 sec)
+----------+
| language |
+----------+
| English |
| French |
+----------+
2 rows in set (3.0730 sec)
Pretty impressive, isn’t it? 🙂
Click the picture to enlarge
A word of caution: Natural Language to SQL is not foolproof. The LLM can make mistakes and may generate an incorrect query. Additionally, while there are no guarantees, a larger number of model parameters (the b in 70b) typically leads to better results.
The next section will discuss key considerations for ensuring optimal performance and accuracy when using the sys.NL_SQL routine.
Getting the Best Results from Natural Language to SQL
Best Practices for Optimal Results
To get the most out of the NL_SQL feature, follow these best practices for generating accurate and efficient queries:
Provide Specific Context: Help the LLM by restricting its focus. Use the schemas and tables parameters to specify only the relevant schemas or tables for your query.
Use Descriptive Names: Ensure your tables, columns, and views have clear, semantically meaningful names. Using descriptive views is especially helpful for improving the accuracy of complex JOIN operations.
Be Specific with Values: To avoid errors, provide exact values in your natural language input, rather than vague descriptions.
Ready to move from concept to code? See the practical use case where an AI DBA Assistant significantly simplifies your query generation workflow in the article, Let Your AI DBA Assistant Write Your MySQL Queries
Important Limitations and Considerations
While powerful, the NL_SQL feature does have a few limitations to keep in mind:
Query Accuracy: The generated SQL may not always be perfectly valid or optimal. Always review the output before executing it.
Performance: Generated queries can sometimes be complex, leading to unpredictable execution times and potentially large result sets that consume excessive resources.
Stateless Operations: The feature does not maintain the state of previous calls. Each invocation is independent and does not learn from prior interactions.
Metadata Volume: Accuracy can decrease if the database contains a large amount of metadata, as this can confuse the LLM.
Unsupported Features: The NL_SQL routine does not support temporary tables.
Peroraison
With the Natural Language to SQL feature in MySQL HeatWave, we’re witnessing a major shift in how we interact with databases. This technology empowers a broader range of users—from business analysts to product managers—to access and analyze data without needing deep SQL expertise. By simply using plain English, anyone can now translate a business question into an executable SQL query. This not only democratizes data access but also significantly accelerates the time from question to insight.
The sys.NL_SQL routine, backed by powerful LLMs, is a testament to the seamless integration of generative AI into core database services. While it’s not a silver bullet—and requires careful consideration of best practices like providing context and using descriptive names—it marks a fundamental step toward making data more accessible and intuitive.
By providing a bridge between human language and database queries, MySQL HeatWave is not just helping us write code; it’s enabling us to ask our databases anything, paving the way for a more natural and productive relationship with our data.
With the sys.NL_SQL routine, asking questions of your database has never been more natural.
Managing large volumes of data is a challenge every organization faces as applications grow. For MySQL users, this often means deciding what to do with historical or less frequently accessed data: keep it in production tables at the cost of performance, or archive it and lose the ability to query it efficiently. Traditionally, archiving has been a trade-off — helpful for keeping databases lean, but limiting when developers or analysts need to run queries across years of historical records.
By combining a high-performance in-memory query accelerator with a fully managed MySQL database service, HeatWave makes it possible to archive vast amounts of data while still enabling fast, interactive analytics. Instead of moving archived data into a separate system or relying on slow queries, developers and DBAs can continue to use familiar SQL while taking advantage of HeatWave’s speed and scale.
In this article, we’ll explore how to use HeatWave to archive MySQL data effectively and then run accelerated queries on that archived data — without compromising performance. Whether you’re managing billions of rows or just planning for future growth, this approach can help simplify your architecture while delivering the best of both worlds: efficient data storage and lightning-fast analytics.
Context
The database has been collecting and collecting daily time-stamped data for years.To optimize performance, we want to keep only the current year’s data plus the full year before it. All older data should be archived, yet still available for querying with very low latency. Let’s see how HeatWave can help us archive efficiently while still running lightning-fast queries on historical data.
SQL>
-- HeatWave MySQL server version
SHOW VARIABLES WHERE Variable_name IN ('version_comment', 'version');
+-----------------+--------------------------+
| Variable_name | Value |
+-----------------+--------------------------+
| version | 8.4.6-cloud |
| version_comment | MySQL Enterprise - Cloud |
+-----------------+--------------------------+
with a 1 node HeatWave Cluster enable.
SQL>
-- HeatWave cluster node number / HeatWave Cluster status
SHOW STATUS WHERE Variable_name IN ('rapid_cluster_ready_number', 'rapid_cluster_status');
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| rapid_cluster_ready_number | 1 |
| rapid_cluster_status | ON |
+----------------------------+-------+
We’ll illustrate this article’s examples using a dataset from Kaggle.
Exchanging Partitions and Subpartitions with Tables
In our case, a practical approach to archiving is to convert the partitions containing old data into regular tables. With {HeatWave} MySQL, this process is straightforward. Let’s take a look at how it works.
In MySQL, it is possible to exchange a table partition or subpartition with a table usingALTER TABLE. There are some requirements, so you may want to read the documentation, but in short we first need to create a similar table than the one that contains the partitions. But this table must not be partitioned. Then we’ll be able to transfer the data from the partition to this new table.
In MySQL, you can use the ALTER TABLE command to exchange a partition or subpartition with a regular table. There are a few requirements to meet — so it’s worth reviewing the documentation — but in short, the process starts by creating a table with the same structure as the partitioned one, except without partitions. Once that table is ready, you can transfer the data from the partition into it.
To create the table (min_temp_1981) that will store the data from partition p1981, you can do, a CREATE… SELECT:
SQL >
CREATE TABLE min_temp_1981 SELECT * FROM min_temp WHERE NULL;
Query OK, 0 rows affected (0.0126 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- No data was copied
SELECT * FROM min_temp_1981 LIMIT 3;
Empty set (0.0005 sec)
SHOW CREATE TABLE min_temp_1981\G
*************************** 1. row ***************************
Table: min_temp_1981
Create Table: CREATE TABLE `min_temp_1981` (
`d` date DEFAULT NULL,
`temp` decimal(3,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
In order to have lightning-fast queries, the archived data must be loaded into the HeatWave cluster.
HeatWave cluster provides a distributed, scalable, shared-nothing, in-memory, hybrid columnar, query processing engine. It can accelerate analytic queries, query external data stored in object storage, and perform machine learning.
To successfully load a table into a HeatWave cluster, you must first ensure it has a primary key. If the table you are trying to archive lacks a primary key, the service will return an “Unable to load table without primary key“ error:
The workflow can be easily simplified using stored procedures (since we’re using HeatWave MySQL 8.4, we can’t take advantage of JavaScript stored procedures, which are only available starting from MySQL 9.2.).
Let’s review the 4 steps:
1. Clone table (Copy structure without partitions,… or data) CALL clone_table(<schema_name>, <partitioned_table>, <archived_table>);
3. Add a primary key CALL add_primary_key_column(<schema_name>, <archived_table>, <primary_key>);
4. Load the data into the HeatWave Cluster CALL sys.heatwave_load(JSON_ARRAY(<schema_name>), JSON_OBJECT(‘include_list’, JSON_ARRAY(<schema_name>.<archived_table>)));
While a robust production solution would be more complex, we can illustrate the core concepts with a basic implementation (these examples should only be used for testing and learning) :
Clone table
DELIMITER $$
CREATE PROCEDURE `clone_table`(
IN _source_schema VARCHAR(64),
IN _source_table VARCHAR(64),
IN _destination_table VARCHAR(64)
)
BEGIN
-- Build the ALTER TABLE statement in order to create the table with the data
SET @create_table_stmt = CONCAT(
' CREATE TABLE `',_source_schema, '`.`', _destination_table,
'` SELECT * FROM `',_source_schema, '`.`',_source_table, '`',
' WHERE NULL '
);
-- Execute
PREPARE stmt FROM @create_table_stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE exchange_partition(
IN _source_schema VARCHAR(64),
IN _partitioned_table VARCHAR(64),
IN _partition_to_exchange VARCHAR(64),
IN _new_table_name VARCHAR(64)
)
BEGIN
-- Build the ALTER TABLE statement in order to exchange the partition with the newly created table
SET @exchange_stmt = CONCAT(
' ALTER TABLE `',_source_schema, '`.`', _partitioned_table, '` ',
' EXCHANGE PARTITION `', _partition_to_exchange, '` ',
' WITH TABLE `',_source_schema, '`.`', _new_table_name, '`'
);
-- Execute
PREPARE stmt FROM @exchange_stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE add_primary_key_column(
IN _source_schema VARCHAR(64),
IN _table_name VARCHAR(64),
IN _column_with_PK VARCHAR(64)
)
BEGIN
-- Build the ALTER TABLE statement in order to add the PK
SET @alter_table_stmt = CONCAT(
' ALTER TABLE `',_source_schema, '`.`', _table_name, '` ',
' ADD COLUMN `', _column_with_PK, '` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST'
);
-- Execute
PREPARE stmt FROM @alter_table_stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
SQL>
SELECT name, load_progress, load_status, query_count
FROM performance_schema.rpd_tables JOIN performance_schema.rpd_table_id USING(id)
WHERE name LIKE 'temp_archiving%'
ORDER BY name;
+------------------------------+---------------+---------------------+-------------+
| name | load_progress | load_status | query_count |
+------------------------------+---------------+---------------------+-------------+
| temp_archiving.min_temp_1981 | 100 | AVAIL_RPDGSTABSTATE | 0 |
| temp_archiving.min_temp_1982 | 100 | AVAIL_RPDGSTABSTATE | 0 |
+------------------------------+---------------+---------------------+-------------+
Data has been offloaded to the HeatWave Cluster.
In our case, this workload would only run once a year after the catch-up phase. But in practice, you could schedule this kind of workflow to run more regularly — whether with cron (or the event scheduler if you are a player 🙂 , your favorite open source data orchestration tool like Airflow, Prefect, Dagster,… or a cloud native managed service like OCI Data Integration.
Peroraison
Archiving data no longer has to mean sacrificing accessibility or performance. With HeatWave MySQL, DBAs and developers can seamlessly move older partitions into regular tables, load them into the HeatWave cluster, and continue running queries at scale — all with the familiar MySQL syntax they already know.
By combining efficient data archiving with in-memory acceleration, HeatWave allows organizations to strike the balance between keeping production databases lean and still being able to analyze years of historical data instantly. What was once a trade-off between storage efficiency and query performance is now a streamlined workflow that can be automated and adapted to your needs.
With HeatWave, your archived data is no longer just stored; it’s ready to deliver actionable insights at the speed of thought.
A few years ago (in 2018), I wrote a MySQL Security series and one of the episode was about the MySQL Enterprise Audit feature — MySQL Security – MySQL Enterprise Audit — a powerful auditing capability that enables you to track and monitor database activity to ensure data integrity, strengthen security, and maintain compliance with regulatory requirements. This robust feature has also been available in HeatWave MySQL for the past few years (since 2023), bringing the same enterprise-grade auditing capabilities to the cloud.
HeatWave MySQL Database Audit is builds upon the established technology of MySQL Enterprise Audit, offering a comprehensive solution for tracking and analyzing database activities.
Key Benefits of HeatWave MySQLDatabase Audit
Rigorous Compliance & Forensics: Helps organizations meet stringent industry regulations (like FedRAMP, DISA STIG, PCI-DSS, HIPAA, SOX, GDPR, FERPA, and Center for Internet Security Benchmarks) by creating a detailed record of database events, essential for investigations and demonstrating adherence to policies.
Security Operations (SecOps): Enables real-time monitoring of user behaviors to detect and respond to potential security threats proactively.
Holistic Server Activity Tracking: Provides comprehensive auditing, from basic client connections and disconnections to more granular activities like interactions with specific schemas and tables, security changes, and errors.
Insights into Query and Statement Performance: Tracks query execution statistics, allowing for the identification of slow queries and performance bottlenecks, leading to database optimization.
Utilization & Optimization: Offers data-driven insights to pinpoint and streamline database operations and resource utilization.
“Trust but Verify” Security Principle: Allows for the monitoring of high-privilege users to prevent misuse of access.
Business Audit: Creates detailed records to prove data validity, accuracy, and integrity, demonstrating that no tampering has occurred.
Security Analysis: Serves as a vital component in a defense-in-depth strategy, facilitating both proactive (machine learning-based anomaly detection) and reactive (post-mortem analysis of attacks) security measures.
Feature Highlights:
Ready to Use: No installation steps are required, simplifying the process of securing and monitoring database activities.
Customizable Auditing: Allows Database Administrators to define filters to monitor specific operations, users, or broad activity categories, reducing audit noise and optimizing log size.
Real-time & Minimal Overhead: Provides instantaneous access to database activity with minimal impact on performance, supporting diverse use cases.
Automatic Log Rotation & Management: Includes built-in log rotation and automatic purging, ensuring optimal DB system performance without manual intervention. Audit logs are structured in JSON format, encrypted, and compressed for efficiency and security.
Multi-Instance Support: Works seamlessly with standalone, multi-instance, and High-Availability (HA) configurations, replicating audit filters and configurations to ensure no event is lost.
Access via SQL Interface: Enables querying of logs directly from the SQL interface using any MySQL client. Logs can also be channeled to OCI Logging Analytics, third-party monitoring tools, or SIEM systems for broader analysis.
Effortless Migration of Rules: Existing MySQL Enterprise Auditing rules (on-premise or other systems) can be easily migrated to HeatWave MySQL instances in the cloud, ensuring consistency.
Optional Query Execution Metrics: Can include details about query execution, such as slow queries, for performance analysis.
Option to Remove Sensitive Data: Allows for the omission of sensitive data from statements before logging.
Server Activity Tracking
The goal here is to track all DML (Data Manipulation Language) statements executed on a HeatWave MySQL instance, by an user account (assuming this user account it used by an application that requires audited). You can easily achieve this by creating a dedicated HeatWave MySQL Audit Log Filter that specifically logs:
INSERT
UPDATE
DELETE
TRUNCATE TABLE
REPLACE
LOAD DATA
LOAD XML
We’ll assign this filter to the dedicated user called auditee@%.
Workflow overview:
Verify that the HeatWave MySQL Database Audit is enabled
Create the DML specific audit filter
Register the filter
Assign the filter to the appropriate user account
Important: To utilize any filtering capabilities, the user performing these actions must possess the AUDIT_ADMIN privilege.
HeatWave MySQL Database Audit is enable by default. You can check using the following queries:
SQL>
-- Checks at the server plugins level
SELECT
PLUGIN_NAME,
PLUGIN_STATUS
FROM
INFORMATION_SCHEMA.PLUGINS
WHERE
PLUGIN_NAME LIKE 'audit%';
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| audit_log | ACTIVE |
+-------------+---------------+
-- Check at the component-based infrastructure level
SELECT
*
FROM
mysql.component
WHERE
component_urn LIKE '%audit%'\G
*************************** 1. row ***************************
component_id: 6
component_group_id: 6
component_urn: file://component_audit_api_message_emit
The user_defined_functions table contains a row for each loadable function registered automatically by a component or plugin, or manually by a CREATE FUNCTION statement.
Create the DML Audit Filter
The filer to log all DMLs running on HeatWave MySQL is quite simple. We are using a JSON syntax:
To access audit data, users can simply query it using standard SQL. The primary method for retrieving this information is audit_log_read(), which returns the audit records in JSON format.
For a basic example of how to extract audit log entries, use the following command:
SELECT audit_log_read(audit_log_read_bookmark());
To display the audit data in a more readable format, use theJSON_PRETTY() and CONVERT() functions:
SELECT JSON_PRETTY(CONVERT(audit_log_read(audit_log_read_bookmark()) USING UTF8MB4))\G
You can refine your audit data extraction by passing additional parameters to the audit_log_read() function. For example, to retrieve, 10 entries of audit logs starting from a specific timestamp, you can use:
Note. You can also use the MySQL’s JSON function JSON_TABLE, to transform audit data into a tabular format. For example to extract a subset of JSON name-value pairs and convert them into a structured table, making the data easier to work with and analyze.
Playground
My application uses the auditee user account, which has the necessary privileges on the s1 schema as well as the AUDIT_ADMIN privilege:
CREATE USER auditee@'%' IDENTIFIED BY 'My5up4rP@sS';
GRANT ALL ON s1.* TO auditee@'%';
GRANT AUDIT_ADMIN ON *.* TO auditee@'%';
and for this demo I’ll use the table s1.t1:
CREATE SCHEMA s1;
USE s1;
CREATE TABLE `t1` (
`id` int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
);
Inside a session using the auditee@% user account:
HeatWave MySQL Database Audit offers a powerful, enterprise-grade auditing framework that seamlessly extends MySQL Enterprise Audit capabilities to the cloud. By enabling fine-grained filtering, real-time monitoring, and flexible log access via SQL, it empowers database administrators and security teams to ensure compliance, enhance visibility, and strengthen operational security.
In this article, we demonstrated how to track all DML operations executed by a specific application user using a dedicated audit filter. From enabling the audit plugin to querying structured audit logs, HeatWave makes it straightforward to implement robust auditing practices with minimal overhead.
Whether you’re working toward regulatory compliance, safeguarding sensitive data, or optimizing database performance, HeatWave MySQL Database Audit equips you with the tools needed to meet modern data governance and security demands—efficiently and effectively.
By leveraging HeatWave MySQL Database Audit, you’re not just logging data; you’re building a foundation of trust and accountability for your critical database operations.
When it comes to loading data from CSV files into your MySQL environment, there’s no shortage of options. In this post, I’ll walk you through two efficient, developer-friendly and MySQL-ish approaches:
SQL>
SELECT COUNT(*) FROM homestays.reviews_from_mysqlsh;
+----------+
| COUNT(*) |
+----------+
| 2068800 |
+----------+
1 row in set (0.5208 sec)
SQL>
EXPLAIN SELECT COUNT(*) FROM homestays.reviews_from_mysqlsh\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0) (cost=816457..816457 rows=1)
-> Table scan on reviews_from_mysqlsh (cost=0.267..551720 rows=2.06e+6)
1 row in set (0.0934 sec)
As a side note, you can even do some basic transformations on the fly before loading the data. See decodeColumns and columns options.
Import data from a local disk
If the file is on a “local” disk, the syntax is the following:
JS>
util.importTable("/Data/project/source/reviews.csv", {schema: "homestays", table: "reviews_from_mysqlsh", dialect: "csv-unix", skipRows: 1, showProgress: true})
Importing from file '/Data/project/source/reviews.csv' to table `homestays`.`reviews_from_mysqlsh` in MySQL Server at 10.0.1.2:3306 using 1 thread
[Worker000]: reviews.csv: Records: 2068800 Deleted: 0 Skipped: 0 Warnings: 0
99% (47.29 MB / 47.29 MB), 7.27 MB/s
File '/Data/project/source/reviews.csv' (47.29 MB) was imported in 6.9141 sec at 6.84 MB/s
Total rows affected in homestays.reviews_from_mysqlsh: Records: 2068800 Deleted: 0 Skipped: 0 Warnings: 0
Import data to a MySQL server
Obviously, util.importTable also works on a classic (I mean non HeatWave) MySQL instance. In this context, you will most likely need to set local_infile variable to 1. Its default value is OFF:
mysql>
SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+
It allows you to avoid the following error:
ERROR: The 'local_infile' global system variable must be set to ON in the target server, after the server is verified to be trusted.
Util.importTable: Invalid preconditions (RuntimeError)
So basically, you’ll need to do something like this:
JS>
\sql SET GLOBAL local_infile = 1;
util.importTable("/Data/project/source/reviews.csv", {schema: "homestays", table: "reviews_from_mysqlsh", dialect: "csv-unix", skipRows: 1, showProgress: true})
\sql SET GLOBAL local_infile = 0;
Turning our attention back to HeatWave — as you may already know, a HeatWave cluster can dramatically accelerate your queries, enabling you to use the familiar MySQL API in analytics scenarios such as data warehousing and lakehousing. To unlock these performance superpowers, you first need to load your data into the HeatWave cluster. Once that’s done, you can fully enjoy the incredible speed and efficiency it brings to your workloads!
Load data into the HeatWave Cluster
To load your data into your HeatWave Cluster from your MySQL table use thesys.heatwave_load stored procedure.
SQL>
CALL sys.heatwave_load(JSON_ARRAY("homestays"), JSON_OBJECT('include_list', JSON_ARRAY('homestays.reviews_from_mysqlsh')));
+------------------------------------------+
| INITIALIZING HEATWAVE AUTO PARALLEL LOAD |
+------------------------------------------+
| Version: 4.31 |
| |
| Load Mode: normal |
| Load Policy: disable_unsupported_columns |
| Output Mode: normal |
| |
+------------------------------------------+
6 rows in set (0.0158 sec)
+-----------------------------------------------------------------------------------------+
| OFFLOAD ANALYSIS |
+-----------------------------------------------------------------------------------------+
| Verifying input schemas: 1 |
| User excluded items: 0 |
| |
| SCHEMA OFFLOADABLE OFFLOADABLE SUMMARY OF |
| NAME TABLES COLUMNS ISSUES |
| ------ ----------- ----------- ---------- |
| `homestays` 0 0 1 table(s) are not loadable |
| |
| No offloadable schema found, HeatWave Auto Load terminating |
| |
| Total errors encountered: 1 |
| Total warnings encountered: 3 |
| Retrieve the associated logs from the report table using the query below: |
| SELECT log FROM sys.heatwave_autopilot_report WHERE type IN ('error', 'warn'); |
| |
+-----------------------------------------------------------------------------------------+
15 rows in set (0.0158 sec)
Oops!! It failed! There is an error. Let’s check it:
SQL>
SELECT log FROM sys.heatwave_autopilot_report WHERE type IN ('error', 'warn');
+-------------------------------------------------------------------------------------------------------------------------+
| log |
+-------------------------------------------------------------------------------------------------------------------------+
| {"error": "Unable to load table without primary key", "table_name": "reviews_from_mysqlsh", "schema_name": "homestays"} |
| {"warn": "1 table(s) are not loadable", "schema_name": "homestays"} |
| {"warn": "No offloadable tables found", "schema_name": "homestays"} |
| {"warn": "No offloadable tables found for given input target"} |
+-------------------------------------------------------------------------------------------------------------------------+
{“error”: “Unable to load table without primary key”, “table_name”: “reviews_from_mysqlsh”, “schema_name”: “homestays”}
Well, I conveniently forgot to mention one important requirement: the table must have a primary key. 😉
If your table doesn’t have a natural or meaningful primary key, no worries — one option is to use Generated Invisible Primary Keys (GIPKs). This allows MySQL to automatically add an invisible primary key behind the scenes.
Please note the SECONDARY_ENGINE=RAPID new clause.
And you can still query your table according to your needs:
SQL>
SELECT COUNT(*) FROM homestays.reviews_from_mysqlsh;
+----------+
| COUNT(*) |
+----------+
| 2068800 |
+----------+
1 row in set (0.1160 sec)
SQL>
EXPLAIN SELECT COUNT(*) FROM homestays.reviews_from_mysqlsh\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0) (cost=16.6e+6..16.6e+6 rows=1)
-> Table scan on reviews_from_mysqlsh in secondary engine RAPID (cost=0..0 rows=2.07e+6)
1 row in set, 1 warning (0.0935 sec)
Note (code 1003): Query is executed in secondary engine; the actual query plan may diverge from the printed one
HeatWave’s Auto Parallel Load
HeatWave’s Auto Parallel Load is a key feature within HeatWave that automatically loads data into the HeatWave cluster, without requiring manual intervention or tuning. Data loading is performed using multiple threads across nodes in the HeatWave cluster, significantly speeding up the operation.
And guess what? you already know the command, it is sys.heatwave_load.
To use it, first we need to define the command using JSON syntax. We recommend assigning this JSON structure to a variable, such as @input_list:
The dialect is CSV, so the only information HeatWave’s Auto Parallel Load requires from us is the presence of a header in the file.
And like we have seen previously, run the stored procedure using the CALL statement:
SQL>
CALL sys.heatwave_load(CAST(@input_list AS JSON), NULL);
+------------------------------------------+
| INITIALIZING HEATWAVE AUTO PARALLEL LOAD |
+------------------------------------------+
| Version: 4.31 |
| |
| Load Mode: normal |
| Load Policy: disable_unsupported_columns |
| Output Mode: normal |
| |
+------------------------------------------+
6 rows in set (0.0128 sec)
+--------------------------------------------------------------------------------------------------------------------+
| LAKEHOUSE AUTO SCHEMA INFERENCE |
+--------------------------------------------------------------------------------------------------------------------+
| Verifying external lakehouse tables: 1 |
| |
| SCHEMA TABLE TABLE IS RAW NUM. OF ESTIMATED SUMMARY OF |
| NAME NAME CREATED FILE SIZE COLUMNS ROW COUNT ISSUES |
| ------ ----- -------- --------- ------- --------- ---------- |
| `homestays` `reviews_from_HW_load` NO 45.10 MiB 2 2.07 M |
| |
| New schemas to be created: 0 |
| External lakehouse tables to be created: 1 |
| |
+--------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.0128 sec)
+------------------------------------------------------------------------+
| OFFLOAD ANALYSIS |
+------------------------------------------------------------------------+
| Verifying input schemas: 1 |
| User excluded items: 0 |
| |
| SCHEMA OFFLOADABLE OFFLOADABLE SUMMARY OF |
| NAME TABLES COLUMNS ISSUES |
| ------ ----------- ----------- ---------- |
| `homestays` 1 2 |
| |
| Total offloadable schemas: 1 |
| |
+------------------------------------------------------------------------+
10 rows in set (0.0128 sec)
+-----------------------------------------------------------------------------------------------------------------------------+
| CAPACITY ESTIMATION |
+-----------------------------------------------------------------------------------------------------------------------------+
| Default encoding for string columns: VARLEN (unless specified in the schema) |
| Estimating memory footprint for 1 schema(s) |
| |
| TOTAL ESTIMATED ESTIMATED TOTAL DICTIONARY VARLEN ESTIMATED |
| SCHEMA OFFLOADABLE HEATWAVE NODE MYSQL NODE STRING ENCODED ENCODED LOAD |
| NAME TABLES FOOTPRINT FOOTPRINT COLUMNS COLUMNS COLUMNS TIME |
| ------ ----------- --------- --------- ------- ---------- ------- --------- |
| `homestays` 1 57.57 MiB 192.00 KiB 0 0 0 7.00 s |
| |
| Sufficient MySQL host memory available to load all tables. |
| Sufficient HeatWave cluster memory available to load all tables. |
| |
+-----------------------------------------------------------------------------------------------------------------------------+
12 rows in set (0.0128 sec)
+---------------------------------------------------------------------------------------------------------------------------------------+
| EXECUTING LOAD SCRIPT |
+---------------------------------------------------------------------------------------------------------------------------------------+
| HeatWave Load script generated |
| Retrieve load script containing 2 generated DDL command(s) using the query below: |
| Deprecation Notice: "heatwave_load_report" will be deprecated, please switch to "heatwave_autopilot_report" |
| SELECT log->>"$.sql" AS "Load Script" FROM sys.heatwave_autopilot_report WHERE type = "sql" ORDER BY id; |
| |
| Adjusting load parallelism dynamically per internal/external table. |
| Using current parallelism of 4 thread(s) as maximum for internal tables. |
| |
| Warning: Executing the generated script may alter column definitions and secondary engine flags in the schema |
| |
| Using SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
| |
| Proceeding to load 1 table(s) into HeatWave. |
| |
| Applying changes will take approximately 7.01 s |
| |
+---------------------------------------------------------------------------------------------------------------------------------------+
16 rows in set (0.0128 sec)
+----------------------------------------------------+
| TABLE LOAD |
+----------------------------------------------------+
| TABLE (1 of 1): `homestays`.`reviews_from_HW_load` |
| Commands executed successfully: 2 of 2 |
| Warnings encountered: 0 |
| Table load succeeded! |
| Total columns loaded: 2 |
| Elapsed time: 30.95 s |
| |
+----------------------------------------------------+
7 rows in set (0.0128 sec)
+----------------------------------------------------------------------------------+
| LOAD SUMMARY |
+----------------------------------------------------------------------------------+
| |
| SCHEMA TABLES TABLES COLUMNS LOAD |
| NAME LOADED FAILED LOADED DURATION |
| ------ ------ ------ ------- -------- |
| `homestays` 1 0 2 30.95 s |
| |
| Total errors encountered: 0 |
| Total warnings encountered: 2 |
| Retrieve the associated logs from the report table using the query below: |
| SELECT log FROM sys.heatwave_autopilot_report WHERE type IN ('error', 'warn'); |
| |
+----------------------------------------------------------------------------------+
11 rows in set (0.0128 sec)
The operation completed successfully; however, two warnings were generated. Details regarding these warnings are available in the sys.heatwave_autopilot_report table:
SQL >
SELECT log FROM sys.heatwave_autopilot_report WHERE type IN ('error', 'warn')\G
*************************** 1. row ***************************
log: {"message": "[WARNINGS SUMMARY] Lakehouse Schema Inference had 1 warning(s) out of which 1 were not recorded (due to max_error_count limit or filtering rules)", "table_name": "reviews_from_HW_load", "schema_name": "homestays", "condition_no": 1}
*************************** 2. row ***************************
log: {"message": "[WARNINGS SUMMARY] 1 warning(s) with code: 6095(ER_LH_WARN_INFER_SKIPPED_LINES)", "table_name": "reviews_from_HW_load", "schema_name": "homestays", "condition_no": 2}
Fortunately, nothing critical.
And now you can query your table according to your needs:
SQL>
SELECT COUNT(*) FROM reviews_from_HW_load;
+----------+
| COUNT(*) |
+----------+
| 2068800 |
+----------+
1 row in set (0.1147 sec)
SQL>
EXPLAIN SELECT COUNT(*) FROM homestays.reviews_from_HW_load\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0) (cost=16.6e+6..16.6e+6 rows=1)
-> Table scan on reviews_from_HW_load in secondary engine RAPID (cost=0..0 rows=2.07e+6)
1 row in set, 1 warning (0.0933 sec)
Note (code 1003): Query is executed in secondary engine; the actual query plan may diverge from the printed on
Peroration
Whether you’re working with traditional MySQL or taking advantage of the blazing-fast analytics capabilities of HeatWave, importing CSV data doesn’t have to be a bottleneck. With tools like MySQL Shell’s parallel import utility and HeatWave’s Auto Parallel Load, you have flexible, scalable options that fit a variety of use cases — from local file loading to seamless integration with object storage.
By combining these tools with features like Generated Invisible Primary Keys, you can streamline the ingestion process and get your data ready for powerful, real-time analytics with minimal overhead.
So next time you’re staring at a CSV file and a big dataset to analyze, you’re fully equipped to handle it — the MySQL way.
Join the Oracle Dev Days – French Edition, from May 20 to 22, 2025! This must-attend event (in French) offers a rich program exploring the latest advancements in AI, databases, cloud, and Java.
Join me on May 21 at 2:00 PM for the day dedicated to “Database & AI.” I’ll be presenting “Building an AI-Powered Search Engine with HeatWave GenAI.” I’ll show you how to go beyond the limits of traditional SQL to harness the power of LLM-driven semantic search.
This approach significantly enhances the relevance of search results by understanding context, interpreting user intent, and handling synonyms.
During this session, we’ll cover:
The technology stack used: SQL, Python, and JavaScript-based stored procedures
The architecture of a complete RAG (retrieval-augmented generation) pipeline, including data extraction, vectorization, storage, and querying within the database
The process of building a chatbot for natural language interaction with the AI
Discover how to implement a powerful, AI-enhanced semantic search engine directly within Oracle HeatWave GenAI.
