Why tmp tables created when connected to one database 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&gt; 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&gt; \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&gt; 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 ?
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.