MySQL performance is a tuning of the tmp_table_size

Increase the size of a temporary table by setting the Tmp_table_size option, such as a temporary table generated by the advanced group by operation. If this value is raised, MySQL will also increase the size of the heap table to improve the speed of the join query, it is recommended to optimize the query as far as possible, to ensure that the temporary table generated during the query in memory, to avoid the temporary table is too large to generate a hard disk-based MyISAM table.

Mysql> show global status like ' created_tmp% ';

+-------------------------+-------+

| variable_name | Value |

+-------------------------+-------+

| Created_tmp_disk_tables | 0 |

| Created_tmp_files | 5 |

| Created_tmp_tables | 23 |

+-------------------------+-------+

3 Rows in Set (0.00 sec)

Each time a temporary table is created, created_tmp_tables increases, and if the temporary table size exceeds tmp_table_size, the temporary table is created on disk, Created_tmp_disk_tables also increases, CREATED_TMP_ Files represent the number of temporary file files created by the MySQL service, and the ideal configuration is:

Created_tmp_disk_tables/created_tmp_tables * 100% <= 25%

Default is 16M, adjustable to 64-256 best, thread exclusive, too large may not have enough memory I/O blocking

If the dynamic page to adjust the point, more than 100M, if the site is mostly static content, general 64M enough

[Email protected] ~]# VI/ETC/MY.CNF

[Mysqld]

tmp_table_size=200m

Mysql> show Processlist;

Mysql> show columns from wp_posts;

The first left join of the SQL statement in the ON clause: the userid of the left join _myuser as T3 on T1.userid=t3.userid _mydata is involved in the conditional comparison operation. An index is established for the _mydata table based on the field UserID: mysql> ALTER table ' _mydata ' Add index (' userid ') increases the tmp_table_size value.

In the MySQL configuration file, the default size of Tmp_table_size is 32M. If a temporary table exceeds that size, MySQL produces an error in the form of the table tbl_name is, and if you do many advanced GROUP by queries, increase the tmp_table_size value.

This is the official MySQL explanation for this option:

Tmp_table_size

This variable determines the maximum size for a temporary table in memory. If the table becomes too large, a MYISAM table is created on disk. Try to avoid temporary tables by optimizing the queries where possible, and where this is not possible, try to ensure temp Orary tables is always stored in memory. Watching the processlist for queries with temporary tables so take too long to resolve can give you an early warning tha T tmp_table_size needs to be upped. Be aware this memory is also allocated per-thread. An example where upping this worked for more is a server where I upped this from 32MB (the default) to 64MB with Immediat E effect. The quicker resolution of queries resulted in less threads being active at any one time, with all-round benefits for the S Erver, and available memory.

The field that is used in the conditions in the WHERE, JOIN, MAX (), MIN (), ORDER by clauses should be indexed by index.

The index is used to quickly find a row with a specific value on a column. Without an index, MySQL had to start with the first record and then read through the entire table until it found the relevant line. The larger the table, the more time it takes. If the table has an index to the column of the query, MySQL can quickly reach a location to find the middle of the data file, and there is no need to consider all the data. If a table has 1000 rows, this is at least 100 times times faster than sequential reads. All MySQL indexes (PRIMARY, unique, and index) are stored in the B-tree.

According to the MySQL development documentation:

Indexed index is used to:

Quickly find the row that matches a WHERE clause

When a junction (join) is executed, rows are retrieved from other tables.

Find Max () or min () values for a specific index column

Sort or Group A table if sorting or grouping is done on the leftmost prefix of an available key (for example, order by key_part_1,key_part_2). If all key values are partially followed by DESC, the key is read in reverse order.

In some cases, a query can be optimized to retrieve values without consulting the data file. If all columns used for some tables are numeric and form the leftmost prefix of some keys, the values can be retrieved from the index tree for faster.

Assume that you issue the following SELECT statement:

Mysql> SELECT * from Tbl_name WHERE col1=val1 and Col2=val2; If a multi-column index exists on col1 and col2, the appropriate row can be fetched directly. If separate single-row column indexes exist on col1 and col2, the optimizer tries to find a more restrictive index by deciding which index will find fewer rows and using that index to fetch rows.

When setting the Tmp_table_size size dynamically, use:

Set Global tmp_table_size=64*1024*1024

Instead of:

Set Global tmp_table_size=64m

Otherwise, an error will occur:

#1232-incorrect argument type to variable ' tmp_table_size '

MySQL performance is a tuning of the tmp_table_size