In the world of database management, MySQL HeatWave offers powerful in-memory analytics capabilities that can supercharge your OLTP queries. But what happens when you introduce views into the mix? A common question I get is: “If I create a view in MySQL, does this view run against the data stored in the tables in HeatWave in-memory’s engine?” The short answer is – it depends. Let’s dive deeper into how views interact with HeatWave, based on MySQL’s architecture and HeatWave’s optimization features. This post will break it down step-by-step, with practical tips and examples to help you get the most out of your setup.

What Are MySQL Views and HeatWave?

Before we get into the details, a quick refresher: A MySQL view is essentially a virtual table defined by a SQL query. It doesn’t store data itself but pulls data from underlying tables when queried. HeatWave, on the other hand, is MySQL’s in-memory columnar storage engine (also known as RAPID) designed for high-performance analytics. It offloads complex queries from the standard InnoDB engine to in-memory processing for lightning-fast results.

The key here is that views don’t inherently “know” about HeatWave. Whether a view’s query executes in HeatWave depends on several factors. Let’s explore them.

Factor 1: Loading Underlying Tables into HeatWave

For a view to benefit from HeatWave’s in-memory power, the tables it references must be explicitly loaded into the HeatWave cluster. This isn’t automatic – you need to use specific commands to prepare the tables.

If the tables aren’t loaded, your view will default to running on InnoDB, which is great for transactional workloads but slower for complex queries or analytics.

You will also want to verify the load status for each table with this query (and note, “your_schema_name” is your database name which you will need to edit, and it is not a table name):

USE performance_schema;
SELECT rpd_table_id.TABLE_NAME, rpd_tables.LOAD_STATUS
FROM rpd_table_id, rpd_tables
WHERE rpd_table_id.ID = rpd_tables.ID
AND rpd_table_id.SCHEMA_NAME = 'your_schema_name';

Look for AVAIL_RPDGSTABSTATE in the LOAD_STATUS column – that means the table is ready in HeatWave. I will show you some examples later.

Factor 2: Query Compatibility with HeatWave

Even if your tables are loaded, the view’s underlying SQL query must be something HeatWave can handle. HeatWave excels at analytical operations like aggregations, joins, and GROUP BY clauses, but it has limitations. For instance, certain subqueries, non-deterministic functions – like RAND() – or advanced string manipulations might not offload.

To check compatibility, run an EXPLAIN on your view or query:

EXPLAIN SELECT * FROM your_view_name;

If the plan mentions the RAPID engine (e.g., in the Extra column), you’re good – the query is offloading to HeatWave.

Factor 3: Automatic Offloading by the Query Optimizer

Assuming the tables are loaded and the query is compatible, MySQL’s optimizer decides whether to offload to HeatWave based on cost estimates. When everything aligns, views can seamlessly leverage HeatWave for faster execution.

Remember, views themselves aren’t stored in HeatWave – they’re just queries. The magic happens at runtime.

How to Make Sure Your View Uses HeatWave

Here’s a straightforward checklist:

Load the Tables: As mentioned earlier, using these commands:


ALTER TABLE your_table_name SECONDARY_ENGINE = RAPID;
ALTER TABLE your_table_name SECONDARY_LOAD;

Here is an example. I am only going to load the airline table:

ALTER TABLE airline SECONDARY_ENGINE = RAPID;
Query OK, 0 rows affected (0.0130 sec)

ALTER TABLE airline SECONDARY_LOAD;
Query OK, 0 rows affected, 1 warning (7.0205 sec)

Verify Load Status: Run the performance schema query above and confirm that the airline table was loaded by looking for the LOAD_STATUS of AVAIL_RPDGSTABSTATE.

SELECT rpd_table_id.TABLE_NAME, rpd_tables.LOAD_STATUS
FROM rpd_table_id, rpd_tables
WHERE rpd_table_id.ID = rpd_tables.ID
AND rpd_table_id.SCHEMA_NAME = 'airportdb';
+------------+---------------------+
| TABLE_NAME | LOAD_STATUS         |
+------------+---------------------+
| airline    | AVAIL_RPDGSTABSTATE |
+------------+---------------------+
1 row in set (0.0011 sec)

Test with EXPLAIN: Query your view and check the plan results. I will show you an example later.

EXPLAIN SELECT * FROM your_view_name WHERE some_condition;

Refine the Query: If it’s not offloading, simplify the view definition. Check out the MySQL HeatWave docs for supported operations – it’s worth the read.

Real-World Example

For many of my HeatWave demos, I use the free MySQL airportdb database, which is simply a large data set for testing MySQL. (The database is approximately 2GB in size and consists of 14 tables containing a total of 55,983,205 records.) The airline table I loaded earlier is from this database. Here is a query that I use to demonstrate the speed of HeatWave:

SELECT airline.airlinename, AVG(datediff(departure,birthdate)/365.25) 
AS avg_age, COUNT(*) as nb_people
FROM booking, flight, airline, passengerdetails
WHERE booking.flight_id=flight.flight_id
AND airline.airline_id=flight.airline_id
AND booking.passenger_id=passengerdetails.passenger_id
AND country IN ("SWITZERLAND", "FRANCE", "ITALY")
GROUP BY airline.airlinename
ORDER BY airline.airlinename, avg_age LIMIT 5;

I am going to create a view which includes this query. The CREATE VIEW statement looks like this:

USE airportdb;
CREATE VIEW avg_passenger_age_by_airline_view AS
SELECT airline.airlinename, AVG(datediff(departure,birthdate)/365.25) 
AS avg_age, COUNT(*) as nb_people
FROM booking, flight, airline, passengerdetails
WHERE booking.flight_id=flight.flight_id
AND airline.airline_id=flight.airline_id
AND booking.passenger_id=passengerdetails.passenger_id
AND country IN ("SWITZERLAND", "FRANCE", "ITALY")
GROUP BY airline.airlinename

I can now run the original query (adding the ORDER and LIMIT) with this simple SELECT * statement. The view encapsulates all the joins, filtering, and aggregation so I never have to rewrite the complex query again. If I ever need the full list of airlines (without the LIMIT), I can simply query the view without the LIMIT clause. I can run this statement which will query the view to get my results:

SELECT *
FROM avg_passenger_age_by_airline_view
ORDER BY airlinename, avg_age
LIMIT 5;
+----------------------+-------------+-----------+
| airlinename          | avg_age     | nb_people |
+----------------------+-------------+-----------+
| Afghanistan Airlines | 45.92612137 |     20570 |
| Albania Airlines     | 46.02485633 |     21804 |
| American Samoa Airli | 46.10098466 |     15249 |
| Angola Airlines      | 45.97214161 |     18539 |
| Argentina Airlines   | 46.03115082 |     21298 |
+----------------------+-------------+-----------+
5 rows in set (9.8186 sec)

You can see that this query took 9.8186 seconds, and that’s because I don’t have all of the tables loaded into HeatWave, and the query used the InnoDB storage engine. (Note: I haven’t looked to see if any extra indexes would make this query faster. The airportdb data set already contains indexes, and that’s what I am using.) Remember – I have only loaded the airline table into HeatWave, and again, I can verify which tables have been loaded:

SELECT rpd_table_id.TABLE_NAME, rpd_tables.LOAD_STATUS 
FROM rpd_table_id, rpd_tables 
WHERE rpd_table_id.ID = rpd_tables.ID 
AND rpd_table_id.SCHEMA_NAME = 'airportdb';
+------------+-----------------------+
| TABLE_NAME |           LOAD_STATUS |
+------------+-----------------------+
| airline    |   AVAIL_RPDGSTABSTATE |
+------------+-----------------------+

I can also check to see that the query with the view is running against InnoDB. Here is the EXPLAIN plan for the query using the view:

explain SELECT * 
FROM avg_passenger_age_by_airline_view 
ORDER BY airlinename, avg_age 
LIMIT 5\G
*************************** 1. row ***************************
EXPLAIN: -> Sort: avg_passenger_age_by_airline_view.airlinename, 
avg_passenger_age_by_airline_view.avg_age, limit input to 5 row(s) per chunk (cost=11.7e+6..11.7e+6 rows=5)
-> Table scan on avg_passenger_age_by_airline_view (cost=11.7e+6..11.7e+6 rows=113)
-> Materialize (cost=11.7e+6..11.7e+6 rows=113)
-> Table scan on (cost=11.7e+6..11.7e+6 rows=113)
-> Aggregate using temporary table (cost=11.7e+6..11.7e+6 rows=113)
-> Inner hash join (booking.flight_id = flight.flight_id) (cost=499651..9.94e+6 rows=2.33e+6)
-> Nested loop inner join (cost=3.73..8.71e+6 rows=2.33e+6)
-> Filter: (passengerdetails.country in ('SWITZERLAND','FRANCE','ITALY')) (cost=108..166108 rows=1545)
-> Table scan on passengerdetails (cost=4.54..164041 rows=36095)
-> Index lookup on booking using passenger_idx (passenger_id = passengerdetails.passenger_id) (cost=3.66..5532 rows=1510)
-> Hash
-> Inner hash join (airline.airline_id = flight.airline_id) (cost=116..313681 rows=462553)
-> Table scan on flight (cost=0.515..238083 rows=462553)
-> Hash
-> Table scan on airline (cost=0.636..71.9 rows=113)

If I want this query to run in HeatWave, I will need to alter and load the other three tables into HeatWave – booking, flight, passengerdetails.

ALTER TABLE booking SECONDARY_ENGINE = RAPID;
ALTER TABLE flight SECONDARY_ENGINE = RAPID;
ALTER TABLE passengerdetails SECONDARY_ENGINE = RAPID;

ALTER TABLE booking SECONDARY_LOAD;
ALTER TABLE flight SECONDARY_LOAD;
ALTER TABLE passengerdetails SECONDARY_LOAD;

And, as these tables are being loaded, I can check on their status using the query from above:

SELECT rpd_table_id.TABLE_NAME, rpd_tables.LOAD_STATUS 
FROM rpd_table_id, rpd_tables 
WHERE rpd_table_id.ID = rpd_tables.ID 
AND rpd_table_id.SCHEMA_NAME = 'airportdb';
+------------+-----------------------+
| TABLE_NAME |           LOAD_STATUS |
+------------+-----------------------+
| booking    | LOADING_RPDGSTABSTATE |
| airline    |   AVAIL_RPDGSTABSTATE |
+------------+-----------------------+

After all four tables have been loaded into HeatWave, when I run the EXPLAIN plan again, I will see that the MySQL optimizer will offload this query to HeatWave. To verify this, in the EXPLAIN plan, look for lines that mentions the RAPID storage engine and the NOTE at the end which states the query is being executed in the secondary engine: (Note: the full EXPLAIN output has been edited and truncated for clarity)

explain SELECT * 
FROM avg_passenger_age_by_airline_view 
ORDER BY airlinename, avg_age LIMIT 5\G->
...
Table scan on airline in secondary engine RAPID (cost=0..0 rows=113)
Note (code 1003): Query is executed in secondary engine; 
    the actual query plan may diverge from the printed one
...

Now, after all of the tables have been loaded, when I run the query again, the query will run in HeatWave and the query execution time will be significantly faster:

SELECT *  FROM avg_passenger_age_by_airline_view
ORDER BY airlinename, avg_age  LIMIT 5;
+----------------------+-------------+-----------+
| airlinename          | avg_age     | nb_people |
+----------------------+-------------+-----------+
| Afghanistan Airlines | 45.92607116 |     20570 |
| Albania Airlines     | 46.02480595 |     21804 |
| American Samoa Airli | 46.10093492 |     15249 |
| Angola Airlines      | 45.97209191 |     18539 |
| Argentina Airlines   | 46.03110048 |     21298 |
+----------------------+-------------+-----------+
5 rows in set (0.2588 sec)

The original query took 9.8186 seconds to execute, but after we loaded the tables into HeatWave, the query only took .2588 seconds – or almost 38 times as fast.

select 9.8186/.2588;
+--------------+
| 9.8186/.2588 |
+--------------+
|  37.93894900 |
+--------------+
1 row in set (0.0013 sec)

But, what happens when there are data changes in the InnoDB base tables? You don’t have to do anything. The view and the data in HeatWave updates automatically, and HeatWave uses the fresh in-memory data.

If you want to do some testing, and you want to tell the MySQL optimizer to not use the HeatWave RAPID engine, you may toggle it on or off, or you may force the optimizer to use RAPID by issuing these commands:

-- Turn the RAPID engine OFF
SET SESSION use_secondary_engine = 'OFF';

-- Turn the RAPID engine ON
SET SESSION use_secondary_engine = 'ON';

-- Force it to use the RAPID engine
SET SESSION use_secondary_engine = 'FORCED';

Finally, to identify tables in the DB System that are defined with a secondary engine (RAPID), query the CREATE_OPTIONS column in the INFORMATION_SCHEMA.TABLES table. If the CREATE_OPTIONS column contains both SECONDARY_ENGINE=RAPID and SECONDARY_LOAD=1, the table definition has been modified to use RAPID and the table has been loaded into the MySQL HeatWave Cluster.

SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS 
FROM INFORMATION_SCHEMA.TABLES 
WHERE CREATE_OPTIONS 
LIKE '%SECONDARY_ENGINE%' 
AND TABLE_SCHEMA LIKE 'airportdb';
+--------------+-------------------+---------------------------------------------+
| TABLE_SCHEMA | TABLE_NAME    | CREATE_OPTIONS               |
+--------------+-------------------+---------------------------------------------+
| airportdb  | airline      | SECONDARY_ENGINE="RAPID" SECONDARY_LOAD="1" |
| airportdb  | airplane     | SECONDARY_ENGINE="RAPID" SECONDARY_LOAD="1" |
| airportdb  | airplane_type   | SECONDARY_ENGINE="RAPID" SECONDARY_LOAD="1" |
| airportdb  | airport      | SECONDARY_ENGINE="RAPID" SECONDARY_LOAD="1" |
| airportdb  | airport_geo    | SECONDARY_ENGINE="RAPID" SECONDARY_LOAD="1" |
| airportdb  | airport_reachable | SECONDARY_ENGINE="RAPID" SECONDARY_LOAD="1" |
| airportdb  | booking      | SECONDARY_ENGINE="RAPID" SECONDARY_LOAD="1" |
| airportdb  | employee     | SECONDARY_ENGINE="RAPID" SECONDARY_LOAD="1" |
| airportdb  | flight      | SECONDARY_ENGINE="RAPID" SECONDARY_LOAD="1" |
| airportdb  | flight_log    | SECONDARY_ENGINE="RAPID" SECONDARY_LOAD="1" |
| airportdb  | flightschedule  | SECONDARY_ENGINE="RAPID" SECONDARY_LOAD="1" |
| airportdb  | passenger     | SECONDARY_ENGINE="RAPID" SECONDARY_LOAD="1" |
| airportdb  | passengerdetails | SECONDARY_ENGINE="RAPID" SECONDARY_LOAD="1" |
| airportdb  | weatherdata    | SECONDARY_ENGINE="RAPID" SECONDARY_LOAD="1" |
+--------------+-------------------+---------------------------------------------+

Remember – creating a view in MySQL doesn’t automatically make it run “in-memory,” but when the underlying tables are in HeatWave, queries on the view can indeed run against that in-memory data. This integration makes HeatWave a game-changer for analytics on MySQL, blending OLTP and OLAP seamlessly. If you’re working with large datasets, experimenting with HeatWave could unlock significant performance gains. Views and HeatWave can be a powerful combo for analytical workloads, but it does require some setup. For more information on HeatWave, check the official MySQL documentation.

Key Benefits and Final Thoughts

  • Complex joins or aggregations in the view’s query can run blazingly fast in HeatWave’s parallel environment.
  • No changes to your application code – just load the tables and let HeatWave handle the offload.
  • If tables aren’t loaded into HeatWave or the query isn’t compatible, your view falls back to InnoDB – potentially slower for big analytics.
  • No extra config for views beyond table loading and query tweaks.
  • Always test with EXPLAIN for precision.