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.
- Course 1: PostgreSQL: Getting Started
- Course 2: PostgreSQL: Introduction to SQL Queries
- Course 3: PostgreSQL: Advanced SQL Queries
- Course 4: PostgreSQL: Advanced Server Programming
- Course 5: PostgreSQL: Index Tuning and Performance Optimization
MySQL – Learning Path
MySQL is an open-source relational database management system (RDBMS).
- Course 1: MySQL Fundamental – 1
- Course 2: MySQL Fundamental – 2
- Course 3: MySQL Indexing for Performance
- Course 4: MySQL Query Optimization and Performance Tuning
- Course 5: MySQL Workbench Guided Tour
- Course 6: MySQL Backup and Recovery Fundamentals
- Course 7: Monitoring MySQL with Performance Schema
- Course 8: phpMyAdmin Fundamentals
Reference: Pinal Dave (https://blog.sqlauthority.com)