MySQL MariaDB – Query Using Temp Table

2 years ago

Yesterday I got a question from my client of Comprehensive Database Performance Health Check, who usually hires me for SQL Server Performance Tuning but this time the question was more related to the MySQL installation they had in their system. Today we will discuss Query Using Temp Table.

Query Using Temp Table

A common question which I often receive is that how do we know if the query is using the temp table or not. Well, it is difficult to know by just looking at the query but it is easy to know it by various available counters.

Here is the command which you can run with MYSQL or MariaDB and figure out how many temp tables your query is using.

SHOW SESSION STATUS LIKE 'Created_tmp_disk_tables';
YourQueryOverHere
SHOW SESSION STATUS LIKE 'Created_tmp_disk_tables';

You have to run the command for measuring the counter before and after your query and it will give you how many temp tables have been created for your query. Well, it is that simple to figure out. There are many counters in MySQL and MariaDB.

MariaDB Learning Path

MariaDB is great for its open-source innovation and enterprise-grade reliability, as well as its modern relational database. It has emerged as a smart alternative to legacy databases.

The original blog post is here: Learn MariaDB – New Technology Week

Course 1: Querying Data from MariaDB
Course 2: Capturing Logic with Stored Procedures in MariaDB
Course 3: MariaDB Data Manipulation Playbook
Course 4: Combining and Filtering Data with MariaDB
Course 5: MariaDB Functions Playbook

PostgreSQL – Learning Path

PostgreSQL is considered to be one of the most advanced open-source databases. PostgreSQL is very easy to learn as well as it is very implemented and easy to implement.