Understanding the precise difference in how SQL treats temp tables vs inline views

Question

I know similar questions have been asked, but I will try to explain why they haven't answered my exact confusion.

To clarify, I am a complete beginner to SQL so bear with me if this is an obvious question.

Despite being a beginner I have been fortunate enough to be given a role doing some data science and I was recently doing some work where I wrote a query that self-joined a table, then used an inline view on the result, which I then selected from. I can include the code if necessary but I feel it is not for the question.

After running this, the admin emailed me and asked to please stop since it was creating very large temp tables. That was all sorted and he helped me write it more efficiently, but it made me very confused.

My understanding was that temp tables are specifically created by a statement like

SELECT INTO #temp1

I was simply using a nested select statement. Other questions on here seem to confirm that temp tables are different. For example the question here along with many others.

In fact I don't even have privileges to create new tables, so what am I misunderstanding? Was he using "temp tables" differently from the standard use, or do inline views create the same temp tables?

From what I can gather, the only explanation I can think of is that genuine temp tables are physical tables in the database, while inline views just store an array in RAM rather than in the actual database. Is my understanding correct?

Answer 1

There are two kind of temporary tables in MariaDB/MySQL:

1. Temporary tables created via SQL

CREATE TEMPORARY TABLE t1 (a int)

Creates a temporary table t1 that is only available for the current session and is automatically removed when the current session ends. A typical use case are tests in which you don't want to clean everything up in the end.

2. Temporary tables/files created by server

If the memory is too low (or the data size is too large), the correct indexes are not used, etc. the database server needs to create temporary files for sorting, collecting results from subqueries, etc. Temporary files are an indicator of your database design / and / or instructions should be optimized. Disk access is much slower than memory access and unnecessarily wastes resources.

A typical example for temporary files is a simple group by on a column which is not indexed (information displayed in "Extra" column):

MariaDB [test]> explain select first_name from test group by first_name;
+------+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra                           |
+------+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+
|    1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL | 4785970 | Using temporary; Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+
1 row in set (0.000 sec)

The same statement with an index doesn't need to create temporary table:

MariaDB [test]> alter table test add index(first_name);
Query OK, 0 rows affected (7.571 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> explain select first_name from test group by first_name;
+------+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra                    |
+------+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
|    1 | SIMPLE      | test  | range | NULL          | first_name | 58      | NULL | 2553 | Using index for group-by |
+------+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+