MySQL internal temporary table policy

Through tracking and debugging the MySQL database source code, and referring to the MySQL official documentation, the MySQL internal temporary table Usage Policy is organized for a more in-depth understanding. Using the internal temporary table condition MySQL internal temporary table has a certain policy, from the source code about SQL query requires internal temporary table. It can be summarized as follows: 1. DISTINCT queries, but simple DISTINCT queries, such as DISTINCT queries such as primary key and unique key, the query optimizer optimizes DISTINCT conditions to remove DISTINCT conditions, no temporary table is created. 2. The fields not in the first table use order by or group by. 3. order by and group by use different orders. 4. You need to cache the results; www.2cto.com 5 and ROLLUP query. The source code is as follows: SQL _select.cc: 854, function: JOIN: optimize (), Location: SQL _select.cc: 1399/* Check if we need to create a temporary table. this has to be done if all tables are not already read (const tables) and one of the following conditions holds:-We are using DISTINCT (simple distinct's are already optimized away) -We are using an order by or group by on fields not in the first table-We are using differen T order by and group by orders-The user wants us to buffer the result. when the with rollup modifier is present, we cannot skip temporary table creation for the DISTINCT clause just because there are only const tables. */www.2cto.com need_tmp = (const_tables! = Tables & (select_distinct |! Simple_order |! Simple_group) | (group_list & order) | test (select_options & OPTION_BUFFER_RESULT) | (rollup. state! = ROLLUP: STATE_NONE & select_distinct); how does one store an internal temporary table while using an internal temporary table? The principle is as follows: 1. When the query result is small, the heap Storage engine is used for storage. That is to say, the query results are stored in the memory. 2. Use the myisam storage engine to store large query results. 3. When the query results are small but increase, the heap Storage engine is converted to the myisam storage engine to store the query results. What is the small query result? The if parameters in the source code can be seen as follows: 1. blob fields exist; 2. Unique restrictions are used; 3. The current table is defined as a large table; 4. When the query result option is a small result set, 5. the query result option is forced to use myisam. The source code of www.2cto.com is as follows: SQL _select.cc: 10229, function: create_tmp_table (), Location: SQL _select.cc: 10557/* If result table is small; use a heap * // * future: storage engine selection can be made dynamic? */If (blob_count | using_unique_constraint | (thd-> variables. big_tables &&! (Select_options & SELECT_SMALL_RESULT) | (select_options & outputs) {share-> db_plugin = ha_lock_engine (0, myisam_hton); table-> file = get_new_handler (share, & table-> mem_root, share-> db_type (); if (group & (param-> group_parts> table-> file-> max_key_parts () | param-> group_length> table-> file-> max_key_length () using_unique_constraint = 1;} else {share-> db_plugin = Ha_lock_engine (0, heap_hton); table-> file = get_new_handler (share, & table-> mem_root, share-> db_type ();} www.2cto.com Code address: SQL _select.cc: 11224, function: create_myisam_from_heap (), Location: SQL _select.cc: 11287/* copy all old rows from heap table to MyISAM table This is the only code that uses record [1] to read/write but this is safe as this is a temporary MyISAM table timestamp/autoincrement or part Itioning. */while (! Table-> file-> rnd_next (new_table.record [1]) {write_err = new_table. file-> ha_write_row (new_table. record [1]); DBUG_EXECUTE_IF ("raise_error", write_err = HA_ERR_FOUND_DUPP_KEY;); if (write_err, query the official MySQL documentation to obtain more authoritative official information. Conditions for creating a temporary table: 1. If the order by condition is different from the group by condition, or if the order by or group by condition is not the field of the first table in the join queue. 2. query by DISTINCT combined with the order by condition. 3. If the SQL _SMALL_RESULT option is used, MySQL uses the memory temporary table. Otherwise, the query results must be stored on the disk. The principle that temporary tables do not use memory tables: 1. Tables have BLOB or TEXT types. 2. Fields in the group by or distinct condition must be greater than 512 bytes. 3. If UNION or union all is used, the fields in any query list must be greater than 512 bytes. In addition, the maximum size of a memory table is the minimum value of tmp_table_size and max_heap_table_size. If this value is exceeded, it is converted to myisam storage engine storage to disk.
Reference: 1, MySQL source code: MySQL-5.5.202, MySQL official documentation: http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html author king_wangheng