MySQL HeatWave is the only fully managed MySQL database service that combines transactions, analytics, machine learning, and GenAI services, without ETL. It also includes the capabilities to query data in object store and provides the best price performance in the industry. MySQL HeatWave is available on Oracle OCI, Amazon AWS, and Microsoft Azure.

Temporary tables are essential tools in modern application development and data workflows, used to manage intermediate results, decompose complex operations, and store session-specific information. However, traditional MySQL temporary tables can introduce performance bottlenecks—particularly in analytics-driven or multi-stage query scenarios.

MySQL HeatWave now offers HeatWave Temporary Tables, which store data entirely in memory and provides SQL access to data. This results in significantly improved query performance and lower latency for applications with multi-step data transformations. Whether you’re powering real-time dashboards, preparing features for machine learning, or executing complex ETL pipelines, HeatWave Temporary Tables make your data operations significantly faster and more efficient.

Why Use Temporary Tables?

Temporary tables are commonly used in:

  • Breaking down complex logic into readable, maintainable steps
  • Holding intermediate results in multi-stage data pipelines
  • Creating user or session-specific scratchpad data
  • Avoiding recomputation in repeated subqueries or joins
  • Feeding material into reports, dashboards, or machine learning models

They are widely used in ETL jobs, reporting systems, stored procedures, and ad hoc analytics.

Challenges with Traditional MySQL Temp Tables

While traditional temporary tables in MySQL are helpful, they come with some drawbacks:

  • Disk-based materialization: Once a temp table exceeds memory thresholds or uses certain data types (like BLOBs), MySQL stores it on disk—leading to slower performance.
  • Limited analytics optimization: Although SELECT queries on traditional temp tables may be offloaded to HeatWave, creating and populating those tables still occurs in the MySQL layer. This process becomes a bottleneck, especially when materializing millions of rows.
  • Latency and overhead: Disk I/O and single-threaded execution slow things down as data complexity or volume increases.

The Solution: HeatWave Temporary Tables (HTTs)

HeatWave Temporary Tables solve these issues by introducing in-memory, read-only temporary tables that are fully processed and stored in HeatWave memory:

  • Always in-memory: No disk fallback—ensuring predictable, high performance
  • Massively parallel: Created and queried using HeatWave’s hybrid-columnar distributed execution engine
  • Designed for analytics: Ideal for complex reporting, feature engineering, and intermediate pipeline data
  • No ETL needed: Operates within the same MySQL HeatWave instance

This makes HeatWave Temp Tables perfect for modern analytics pipelines, where speed, scalability, and simplicity are essential.

How It Works: Creating and Using HeatWave Temp Tables

Creating an HTT uses the familiar CTAS (CREATE TABLE AS SELECT) syntax, with the addition of SECONDARY_ENGINE = RAPID to offload the operation to HeatWave:

CREATE TEMPORARY TABLE my_temp SECONDARY_ENGINE = RAPID
    SELECT c.customer_id, o.order_id, o.amount
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    WHERE o.order_date >= '2025-07-01';

 

Properties and Limitations

HTTs are read-only, in-memory snapshots and are designed specifically for analytical workloads:

  • Query-driven only: HeatWave Temp Tables must be created from a SELECT query; static schema definitions aren’t supported.
  • Fully offloaded: If the creation query cannot run in HeatWave (e.g., base tables not loaded, cluster not active), it will fail.
  • Immutable: No DML (INSERT, UPDATE, DELETE) or ALTER operations allowed after creation.
  • Independent: HTTs don’t track base table updates. If data changes, simply drop and recreate the HTT.
  • Session-local: HeatWave Temp Tables are isolated per session and are automatically dropped when the session ends.
  • Persistent and recoverable: HTTs are stored in HeatWave’s internal object store for durability. If the HeatWave cluster restarts, HTTs are automatically recovered.
  • Monitorable: Metadata and performance statistics are exposed via performance_schema, including the original materialization query.
  • Limitations: HeatWave Temp Tables can’t be created in triggers, and queries returning zero rows aren’t allowed.

At the MySQL server level, HTTs behave like regular temp tables—if mysqld restarts or crashes, session-scoped HTTs are lost.

Querying HeatWave Temp Tables

Once created, HTTs can be used in any analytic query and are also offloaded to HeatWave:

  • Queries referencing HTTs benefit from the same HeatWave acceleration.
  • HTTs remain accessible even if the source tables are unloaded or dropped.
  • Useful in multi-step pipelines, ad hoc explorations, or data transformations that don’t need to recompute every time.

Summary

MySQL HeatWave Temporary Tables bring a much-needed performance boost to a familiar MySQL feature. They are:

  • Fast: Materialized and queried entirely in HeatWave’s in-memory engine
  • Scalable: Built for modern data pipelines and complex workloads
  • Simple: Use familiar SQL syntax—no ETL, no special tools, no data movement
  • Durable and secure: With automatic recovery and metadata observability

Whether you’re building dashboards, transforming data for ML, or breaking complex queries into steps, HTTs help you move faster, scale better, and reduce resource usage—all within the MySQL ecosystem.