mysql temp table performance

-engine = InnoDB // The default data table Type of the new data table. The default value is MyISAM. Lower_case_table_names = 1 // MySQL implementation is case insensitive Transaction_isolation = REPEATABLE-READ // sets the default transaction isolation level for all connections Tmp_table_size = 256 M // Maximum length of the temporary HEAP data

More than 20 best experiences on MySQL Performance Optimization today, database operations are increasingly becoming the performance bottleneck of the entire application, which is particularly evident for Web applications. Concerning the database performance, this is not just something that DBAs need to worry about, bu

, which can seriously affect query performance. You can resolve this problem by executing analyze TABLE to regenerate the statistics. Reduce fragmentation of indexes and data B-tree indexes can be fragmented, and fragmented indexes may be stored on disk in a poorly or unordered manner, which can reduce query efficiency; The table data store m

In the MySQL execution plan:IDID used to indicate the order of execution, ID the same as a group, first execute ID A large number of groups, and then a small group of numbers are executed. within a group with the same ID, the order is executed from top to bottom. TypeIndicates how MySQL finds the desired row in the table, also known as the "access type", the co

MySQL Performance Optimization: Over 20 best experiences sharing bitsCN.com when we design the database table structure and perform database operations (especially the SQL statements used in table queries ), we need to pay attention to the performance of data operations. Her

MySQL database performance optimization solution bitsCN.com MySQL database performance optimization solutionThe actual operating scheme for database performance optimization. with the continuous development of MySQL database, the

1 selection of storage enginesInnoDB and MyISAM are the two most common table types used by many people when using MySQL, both of which have pros and cons, depending on the application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, and InnoDB type support. The MyISAM type of table emphasizes

stored in the memory, use the range query to load the first 20% of the data into the memory as hotspot data, for example: select count (*) FROM BT_KV_SHORT_INT_CHAR_10KW WHERE col1 Project Model Number of table records 1KW (3G), 2KW (6G), 5KW (15G), 10KW (30G) Key INT Value CHAR (250) Hotspot data 20% of total data The performance test resu

This article explains how to completely optimize MySQL database performance in eight aspects. 1. selecting the most suitable field attribute MySQL can support access to large data volumes. However, the smaller the table in the database, the faster the query will be executed on it. Therefore, when creating a

stored in the memory, use the range query to load the first 20% of the data into the memory as hotspot data, for example: select count (*) FROM BT_KV_SHORT_INT_CHAR_10KW WHERE col1 Project Model Number of table records 1KW (3G), 2KW (6G), 5KW (15G), 10KW (30G) Key INT Value CHAR (250) Hotspot data 20% of total data The performance test resu

maximum size allowed for each temporary table in memory. If the temporary table size exceeds this value, the temporary table is automatically converted to a disk Based table.Thread_cache_size=8 # The maximum number of threads cached. When the client connection is broken, the thread that handles the client task is put back into the cache if the total number of cl

Key parameters for mysql database performance optimization and key parameters for mysql Server optimization 1: back_log requires the number of connections available for MySQL. When the main MySQL thread receives a lot of connection requests in a very short period of time, th

This article provides details and benchmark test results for Mysql queries per second, explaining my earlier conversations on Mysql Connect. Review the improvement history of MySQL/InnoDB. You can easily find out. MySQL 5.6 has never been faster than read-only. It is easy to understand and has good expansion in read-on

MySQL improves Insert performance and mysqlinsert Performance The time required to insert a record is composed of the following factors, and the number indicates the approximate proportion: Connection: (3) Send query to server: (2) Analysis query: (2) Insert record: (1x record size) Insert index: (1x index) Close: (1) This does not take into account the i

server is 256, and then query the maximum number of connections on the server response:Mysql> show global status like 'max _ used_connections ';In the past, the maximum number of connections on the MySQL server was 245, but the maximum number of connections on the server was 256. There should be no 1040 error. the ideal setting isMax_used_connections/max_connections * 100% ≈ 85%.The maximum number of connections accounts for about 85% of the maximum

, Replace the row with the same data (that is, the direct original value overrides). Multi-line replace empty table--use "Replace INSERT into ... VALUES (..), (..), (..), ... "To insert data into an empty table. insert-duplicate--using INSERT into. VALUES (..), (..), (..), ... On DUPLICATE KEY UPDATE ... "The syntax is based on the previous step. The conclusions are as follows: For

difference is that the table divides the large table into several separate entity tables, while partitioning divides the data into multiple locations, which can be the same disk or on different machines. After partitioning, the surface is still a table, but the data is hashed to multiple locations. When the app reads or writes a large

Tags: MySQL performance tuning six InnoDB engine and MyISAM engine performanceThe choice of the two storage engines depends on the application characteristics of the project, and for the complex application system, it is possible to select a combination of various storage engines according to the actual situation. But this method is not recommended to be taken. MyISAM supports full-text indexing, which is a

splitting basis is the most important.(UNION) Vertical table Division: Some tables have a small number of records, which may contain 2 or 30 thousand records, but the fields are very long. The table occupies a large amount of space and requires a large number of I/O operations during table retrieval, seriously reducing the