Why tmp tables created when connected to one database

Question

I am wondering why temp tables created while switching to database. When I login to the mysql server, I have queried current session temp tables as follows:

mysql> show status like '%tmp%';  
+-------------------------+-------+  
| Variable_name           | Value |  
+-------------------------+-------+  
| Created_tmp_disk_tables | 0     |  
| Created_tmp_files       | 6     |  
| Created_tmp_tables      | 0     |  
+-------------------------+-------+  

3 rows in set (0.00 sec)

mysql> \u database_name  
Reading table information for completion of table and column names  
You can turn off this feature to get a quicker startup with -A  

Database changed  

mysql> show status like '%tmp%';  

+-------------------------+-------+  
| Variable_name           | Value |  
+-------------------------+-------+  
| Created_tmp_disk_tables | 0     |  
| Created_tmp_files       | 6     |  
| Created_tmp_tables      | 3     |  
+-------------------------+-------+  
3 rows in set (0.00 sec)

Any idea why were 3 temp tables created ?

score 1 · Accepted Answer · edited Apr 13 '17 at 12:42

Look at the message

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Table info is being read from the INFORMATION_SCHEMA. Why ?

According to the MySQL Documentation on auto rehashing for the mysql client:

--auto-rehash

Enable automatic rehashing. This option is on by default, which enables database, table, and column name completion. Use --disable-auto-rehash to disable rehashing. That causes mysql to start faster, but you must issue the rehash command or its # shortcut if you want to use name completion.

To complete a name, enter the first part and press Tab. If the name is unambiguous, mysql completes it. Otherwise, you can press Tab again to see the possible names that begin with what you have typed so far. Completion does not occur if there is no default database.

For your session, internal temp tables with info from INFORMATION_SCHEMA is needed for hitting the tab character in the event multiple columns or multiple tables have the same prefix characters.

CAVEAT

Please note that the INFORMATION_SCHEMA database is comprised of temp tables to begin with : See my post How is INFORMATION_SCHEMA implemented in MySQL?.

Therefore, it should be no surprise that more temp tables are created to hold table and column info for a database you just switched to. This is particularly true if you are not a super user like root@localhost and your grants only go as high as the database level.

score 1 · Answer 2 · answered Dec 12 '15 at 22:01

There are several reasons for tmp tables. Let's look at some from SELECT:

GROUP BY sometimes need to collect the data, sort it, then do the grouping.
ORDER BY ditto
GROUP BY one_thing ORDER BY something_else definitely needs to sort, hence a tmp table.
UNION always (until very recently) used a tmp table. (Now it is smarter about whether tmp is needed.)
subqueries might need a tmp table, especially FROM ( SELECT ... )
IN ( SELECT ... ) probably needs one in 5.6; previously it would re-evaluate the query repeatedly.

Note that one SELECT might need multiple tmp tables. This is OK.

Created_tmp_tables counts tmp tables, whether MEMORY or MyISAM; Created_tmp_disk_tables counts only the latter. MEMORY is preferred (faster), but there are restrictions (datatypes, size, etc) that lead to either starting out with MyISAM or converting to it when the tmp table exceeds max_heap_table_size and tmp_table_size (after trying to use MEMORY).

Why tmp tables created when connected to one database

2 Answers2

CAVEAT