Shulou(Shulou.com)06/02 Report--

What is an internal temporary watch?

Unlike hand-created temporary tables, temporary files, called internal temporary table, may be used to store query results during sql execution

This process is completed automatically by Mysql, and users cannot intervene manually.

These tables are stored either in memory using the memory engine or on disk using the MyISAM engine

When to generate

The columns that use order by / group by are not all from the first table of join queue

Combined use of Distinct and order by

Multi-table joins need to save intermediate result sets

How to save

SQL_SMALL_RESULT uses memory temporary tables unless there are conditions under which disk temporary tables must be used:

A disk temporary table must be used when the table contains blob/text columns, or when the column of group by/distinct is larger than 512 bytes

When temporary table > min (tmp_table_size, max_heap_table_size), the memory temporary table is automatically converted to disk temporary table.

You can view the usage of internal temporary tables through the status variable created_tmp_tables/created_tmp_disk_tables

Internal temporary tables automatically generate indexes in some cases to improve performance

MySQL does create two keys on internal temporary tables namely 'group_key' and' distinct_key' in the following conditions:

If there is any aggregate function and/or group-by (group_key)

Distinct column name (group_key)

Distinct in combination with group-by/aggregation functions (distinct_key)

Http://venublog.com/2010/03/08/when-indexes-are-created-in-internal-temporary-tables/

Patch has been developed and index can be forcibly created on internal temporary tables through hint

SELECT

SUM (aggrpt.imps) as imps

SUM (aggrpt.clicks) as clicks

SUM (aggrpt.pos) as pos

FROM aggrpt

LEFT JOIN

(

SELECT

DISTINCT ext_group_id, group_id

FROM sub

) sub2 ON (sub2.ext_group_id=aggrpt.adgroupid)

GROUP BY

Aggrpt.report_date

Aggrpt.campaignid

Aggrpt.adgroupid

Aggrpt.keywordid

ORDER BY NULL

INTO OUTFILE'/ tmp/test-sub.txt'

-

Query OK, 47827 rows affected (6 min 47.48 sec)

There are two ways to improve: 1 rewrite the subquery to a temporary table and create an index on ext_group_id, 2 add an index to the intermediate result set, and run time after sub2 USE INDEX (ext_group_id) ON (sub2.ext_group_id=aggrpt.adgroupid) improvement

Query OK, 47827 rows affected (7.18 sec)

Http://venublog.com/2010/03/06/how-to-improve-subqueries-derived-tables-performance/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.