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 !!!
Best Answer
Use HAVING for checking rownames