mysql temporary table vs memory table

Temporary tables are tables with limited scope, and memory tables are tables with different storage methods. Temporary tables are visible in the current session and are automatically deleted after the connection is disconnected. Various storage engines can be used, which are suitable for saving intermediate results and avoiding repeated calculations; 1. Temporary tables support indexing, and multiple sessions can create tables with the same name without affecting each other; 2. The memory table uses the MEMORY engine, and the data is stored in memory, and the restart is lost, which is suitable for cache small data sets with high frequency access; 3. The memory table supports hash indexing, and does not support BLOB and TEXT types, so you need to pay attention to memory usage; 4. The life cycle of the temporary table is limited to the current session, and the memory table is shared by all connections. When choosing, it should be decided based on whether the data is private, whether high-speed access is required and whether it can tolerate loss.

There are temporary tables and memory tables in MySQL, which are easy to be confused when many people use them. In fact, they are two different things and the problems they solve are also different. Simply put: a temporary table is a table with a limited scope of function, while a memory table is a table with different storage methods .

The following is a few common usage scenarios to talk about their differences and application.

What is a temporary table?

A temporary table is a table structure that is only visible in the current session. Once the connection is disconnected, the table will be automatically deleted. It can be used with any storage engine, such as InnoDB, MyISAM, or Memory.

Common uses:

• Save temporary data in the middle of complex query
• Avoid repeated calculations and improve performance
• Used for intermediate results in stored procedures or functions

Recommended usage:

• If you only need a "intermediate table" for the current operation and do not want to affect other users, use temporary tables
• You can create indexes or operate like normal tables
• Multiple sessions can create temporary tables with the same name without affecting each other

Example statement:

 CREATE TEMPORARY TABLE temp_users (
    id INT,
    name VARCHAR(100)
);

What is a memory table?

Memory Table, also known as HEAP table, is a table that stores data in memory and uses the MEMORY storage engine. It reads and writes quickly, but data is lost after restarting.

Common uses:

• Caches small datasets with high frequency access
• Speed up certain operations as temporary cache
• Used for fast read and write scenarios such as statistics and counting

Recommended usage:

• Used when the data is not large and you are not afraid of losing it
• Supports hash index, suitable for point-checking
• BLOB and TEXT type fields are not supported
• Pay attention to memory usage and don't crash the server

Example statement:

 CREATE TABLE cache_data (
    key_name VARCHAR(50) PRIMARY KEY,
    value TEXT
) ENGINE=MEMORY;

Temporary table vs memory table: key differences

How to choose in practical applications?

If you just want to save some intermediate results during SQL execution and don't want others to see it, then use temporary tables ;
If there is a set of data that you want to keep it alive and access it quickly and you are not afraid of reboot loss, you can consider using memory tables .

Of course, sometimes you can also use it in combination, such as using temporary tables for intermediate processing, and then put the results in the memory table for subsequent calls.

Basically, these are all. Both types of tables have their own positioning. Understand their essence so that they will not make mistakes when used.

The above is the detailed content of mysql temporary table vs memory table. For more information, please follow other related articles on the PHP Chinese website!