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 !!!

I think you will find an extra "statement" in the binlog that assigns the auto_inc value for the subsequent statement. This guarantees that the Slave has the same ids for SBR.

For RBR (Row-Based-Replication), there is no issue, since the id is part of the INSERT.

In addition to the possibility of "burning" ("losing") ids, the ids can be replicated out of order. This because ids are grabbed as needed, but replication is in COMMIT-order. (This applies to SBR and RBR.)

For multi-master, auto_increment_increment > 1 -- another reason for gaps in the ids.