OBSERVATION #1

You need to look at what the Query Optimizer sees and what it is interpreting

First, look carefully at the query

select SQL_NO_CACHE object_id, lang_code, object
from common_descriptions
limit 1

There is no WHERE clause. It is generating a temp table for an unbounded result set.

LIMIT has low precedence in the eyes of the Query Optimizer. Why ?

According to the MySQL Documentation on SELECT

The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.)

Another section of that same page says the following:

With one argument, the value specifies the number of rows to return from the beginning of the result set:

SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows

In other words, LIMIT row_count is equivalent to LIMIT 0, row_count.

Once you use the clause LIMIT, the SELECT must generate a result first. Your result has no boundary (no WHERE clause). The LIMIT is applied afterwards.

That explains the temp table creation.

OBSERVATION #2

You mentioned the following in your question

When I replace object (varchar(12)) with description (mediumtext) temporary table is not created

According to the MySQL Documentation on the Temp Tables (your link)

Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

• Presence of a BLOB or TEXT column in the table
• Presence of any string column in a GROUP BY or DISTINCT clause larger than 512 bytes
• Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the SELECT list, if UNION or UNION ALL is used
• The SHOW COLUMNS and The DESCRIBE statements use BLOB as the type for some columns, thus the temporary table used for the results is an on-disk table.

The problem has a lot to do with the result set and the first bulletpoint (Presence of a BLOB or TEXT column in the table) Since TEXT field cannot occupy an in-memory temp table, it goes straight to disk. That's just the tip of the iceberg.

When you ran

select SQL_NO_CACHE object_id, lang_code, object
from common_descriptions
limit 1

you were essentially read the entire row. How come ?

The common_descriptions is defined with the InnoDB Storage Engine

The primary key of an InnoDB table sits in a logical structure call the Clustered Index (internally it is called gen_clust_index, which can only be seen when running SHOW ENGINE INNOD STATUS\G)

That document says

Accessing a row through the clustered index is fast because the row data is on the same page where the index search leads. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record. (For example, MyISAM uses one file for data rows and another for index records

Since you accessed every column of the PRIMARY KEY (object_id, lang_code, object), internally you were essentially fetching every row (including description) which explains why a PRIMARY KEY retrieval still yielded a temp table on disk. When you changed the SELECT list (object_id, lang_code, description) things evident changed. a little. An in-memory temp table would not have been made. Why?

MySQL Documentation says this:

When the server creates an internal temporary table (either in memory or on disk), it increments the Created_tmp_tables status variable. If the server creates the table on disk (either initially or by converting an in-memory table) it increments the Created_tmp_disk_tables status variable.

Thus, Created_tmp_tables would not have incremented because of the description being mediumtext. It went straight to disk. Created_tmp_disk_tables would have incremented instead.

SUMMARY

Many factors were involved

• LIMIT used again a result set with no WHERE clause
• InnoDB Clustered Index
• description being MEDIUMTEXT

SUGGESTION

Try adding an ORDER BY

select SQL_NO_CACHE object_id, lang_code, object
from common_descriptions
order by object_id, lang_code, object
limit 1;

I hae seen it when done against a single column PRIMARY KEY.

Give it a Try !!!

Since it is a temp table, you are OK to just delete the files.

rm -f *#sql-ib32694.*

Is this harmful to InnoDB ? No. There was a data dictionary entry still inside ibdata1. However, that entry is either missing or invalid (due to the tablespace_id). There is actually a one in 1,099,511,627,766 chance of it causing a problem.

See my older posts on why it is not that harmful and how to deal with

• Jun 04, 2014 : temp table (#sql-7a87_230c32.ibd along with its .frm) still exists on slave
• Dec 23, 2014 : #1025 - Error on rename of table errno: 150 : Table was deleted while tried to assign foreign key

Please keep in mind that temp tables (regardless of storage engine) are transient in nature. A temp table will disappear under these circumstances:

• When a query is finished using it
• When a DB connection terminates normally
• When a DB connection terminates abnormally

If a temp table still exists due to a crash, rest assured that the data dictionary no longer acknowledges its existence. Please delete them and keep a good conscience.

If you are worried about, your only recourse when this happens is to perform a fully InnoDB Cleanup. See my post Howto: Clean a mysql InnoDB storage engine? in StackOverflow. The lesson here

• Use CREATE TEMPORARY TABLE (...) ENGINE=MyISAM; if you have to use temp tables in your app.
• Any system-generated InnoDB temp tables can be deleted after a crash.