3

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 ?

1

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.

  • 1
    Why these internal temp tables are created(when switched to one database) when we already have information_schema tables which are temporary tables and having meta data. Its redundant right . I have checked the "How is INFORMATION_SCHEMA implemented in MySQL?". But i have seen some of the information_schema tables are created with myisam storage engine but not memory . Examples: COLUMNS ,EVENTS. – kasi Dec 1 '15 at 7:13
  • Auto-rehash predates information_schema; I can't imagine how it has anything to do with the OP's question. – Rick James Dec 12 '15 at 21:54
  • I have checked the behavior by disabling auto-rehash on mysql client command line using -A option ,Created_tmp_tables count did not increased to 3.It stands on zero itself. – kasi Dec 16 '15 at 9:01
  • James,please let me know whether my understanding is correct or not here. As i commented earlier , Some information_schema tables are Myisam (ex:columns,events) but not using memory storage engine. As you mentioned in previous answer ,these table used memory storage engine first ,but due to some constraints they switched to myisam. Am i right? – kasi Dec 16 '15 at 9:04
1

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).

  • you are absolutely right james . The scenario you explained is when you fire some queries on database using group by,order by etc. But i haven't fired anything, i just redirected to one database. So whats the purpose of temp tables here. – kasi Dec 15 '15 at 12:23
  • Hmmm... No increased value for me: SHOW... \u... SHOW. You are not using FEDERATED, I assume? – Rick James Dec 15 '15 at 15:35
  • Maybe something to do with the VARIABLES innodb_stats.... (I turned on innodb_stats_on_metadata, but it still did not happen for me.) What version are you running? – Rick James Dec 15 '15 at 15:42
  • i am running on mysql 5.5.40 – kasi Dec 16 '15 at 8:21
  • I am not using federated storage engine – kasi Dec 16 '15 at 10:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.