MySQL – Handling Temporary On-Disk Tables for Primary Key Retrieval

innodbMySQLprimary-keytemporary-tables

MySQL 5.5.24, Windows 7 64-bit.

I've InnoDB table with primary key defined on three columns:

CREATE TABLE `common_descriptions` (
  `object_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `object_type` varchar(32) NOT NULL DEFAULT '',
  `description` mediumtext NOT NULL,
  `lang_code` char(2) NOT NULL DEFAULT '',
  `object` varchar(12) NOT NULL DEFAULT '',
  `object_holder` varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`object_id`,`lang_code`,`object`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And I'm executing fairly simple query which (in my opinion) should use index to return rows

select SQL_NO_CACHE object_id, lang_code, object
from common_descriptions
limit 1

yet it creates 10 temporary tables of which 3 are on disk temporary tables (confirmed multiple times by monitoring show status like '%tmp%';). object column seems to trigger creation of temporary tables. When I replace object (varchar(12)) with description (mediumtext) temporary table is not created. explain extended of above query:

mysql> explain extended select SQL_NO_CACHE object_id, lang_code, object from co
mmon_descriptions limit 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: common_descriptions
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 47
          ref: NULL
         rows: 49765
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

Both tmp_table_size and max_heap_table_size are set to 16M.

After reading documentation on temporary tables (http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html) I don't see any good explanation on why temporary tables would be created in this case. Why is it happening?

Best Answer

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

Related Question