Optimizing MySQL database query

Optimizing MySQL database query
The simplest and safest format, which is the fastest in the disk format. Speed comes from the ease with which data can be found on disk. When the lock has an index and the static format something is, it's very simple, just the line length multiplied by the quantity. And when you scan a table, it's easy to read a constant number of records at a time with disk reads. Security comes from if the computer is down when writing a static MyISAM file, Myisamchk can easily point out where each row starts and ends, so it usually reclaims all records, except for a partially written record. All indexes in MySQL are always rebuilt
　　
1.2 Dynamic MyISAM
　　
Each line of this format must have a header indicating how long it is. When a record becomes longer during a change, it can end in more than one position. can use optimize tablename or myisamchk to organize a table. If you have static data that is accessed/altered in the same table as some varchar or BLOB columns, move the dynamic column into another table to avoid fragmentation.
　　
1.2.1 Compression MyISAM, using optional myisampack tools to generate
　　
1.2.2 Memory
　　
This format is useful for small/medium tables. For copying/Creating a common lookup table to the Ocean heap table it is possible to speed up multiple table joins, with the same data may be several times faster.
　　
Select tablename.a,tablename2.a from Tablename,tablanem2,tablename3 where
　　
Tablaneme.a=tablename2.a and tablename2.a=tablename3.a and tablename2.c!=0;
　　
To speed it up, you can create a temporary table with tablename2 and Tablename3 joins, because the same column (tablename1.a) is used to find it.
　　
Create temporary table test Type=heap
　　
SELECT
　　
Tablename2.a as a2,tablename3.a as A3
　　
From
　　
Tablenam2,tablename3
　　
WHERE
　　
Tablename2.a=tablename3.a and c=0;
　　
SELECT tablename.a,test.a3 from Tablename,test where tablename.a=test.a1;
　　
SELECT Tablename.a,test,a3,from tablename,test where tablename.a=test.a1 and ...;
　　
1.3 Features of static tables
　　
1.3.1 the default format. Used when a table does not contain Varchar,blob,text columns
　　
1.3.2 all char,numeric and decimal columns to column widths
　　
1.3.3 very fast.
　　
1.3.4 Easy to Buffer
　　
1.3.5 easily rebuilt after down because the record is in a fixed position
　　
1.3.6 do not have to be organized (with MYISAMCHK) unless a huge amount of records is deleted and the storage size is optimized
　　
1.3.7 typically requires more storage than dynamic tables
　　
1.4 Characteristics of dynamic table
　　
1.4.1 If the table contains any Varchar,blob,text columns, use this format
　　
1.4.2 All string columns are dynamic
　　
1.4.3 each record with a bit before it is placed.
　　
1.4.4 usually requires more disk space than a fixed-length table
　　
1.4.5 Each record uses only the space needed, and if a record becomes large, it is divided into segments as needed, which results in a record fragment
　　
1.4.6 If you update the row with information that exceeds the length of the line, the row is segmented.
　　
1.4.7 is not easy to rebuild after the system down, because one record can be multiple segments
　　
1.4.8 the desired line length for dynamic Dimension Records is 3+ (number of columns+7)/8+ (number of char columns) +packed size of numeric columns+length of Strings + (n Umber of NULL columns+7)/8
　　
There is a penalty of 6 bytes for each connection. Whenever a change causes a record to become larger, a dynamic record is connected. Each new connection has at least 20 bytes, so the next variable may be in the same connection. If not, there will be another connection. can use Myisamchk-malicious check for how many connections. All connections can be deleted with Myisamchk-r.
　　
1.5 Characteristics of the compressed table
　　
1.5.11 read-only tables made using the Myisampack utility.
　　
1.5.2 uncompressed code exists in all MySQL distributions so that connections without myisampack can also read tables with Myisampack compression
　　
1.5.3 occupies a small disk space
　　
1.5.4 Each record is compressed individually. The header of a record is a fixed-length (1~~3 byte) depending on the maximum record of the table. Each column is compressed in a different way. Some common types of compression are:
　　
A: There's usually a different Huffman for each column. Table B: Suffix blank compression c: prefix blank compression d: Use 1-bit storage with a value of 0
　　
E: If the value of an integer column has a small range, the column is stored with the smallest possible type. For example, if all values are between 0 and 255, a bigint can be stored as a tinyint
　　
G: If the column has only one small set of possible values, the column type is converted to the enum H: column can use a combination of the above compression methods
　　
1.5.5 can process fixed or dynamic length records to not handle blobs or text columns 1.5.6 can be decompressed with Myisamchk
　　
MySQL can support different index types, but the general type is ISAM, which is a B-tree index and can be roughly computed for index file size (key_length+4) *0.67, sum on all keys.
　　
The string index is blank compressed. If the first index is a string, it can compress the prefix if the string column has many trailing blanks or a varchar column with a headquarters-length, blank compression makes the index file smaller. If many strings have the same prefix.
　　
1.6 Features of the memory table
　　
The heap table inside MySQL uses a 100% dynamic hash with every occasional overflow and there is no problem with the deletion. You can access things using an equation only by using one of the cables in the heap table (usually the ' = ' operator)
　　
The disadvantages of the heap table are:
　　
1.6.1 need enough extra memory for all the heap tables you want to use at the same time
　　
1.6.2 cannot search in one part of the index
　　
1.6.3 cannot search for the next item sequentially (that is, use this index to make an order by)

: Memory limitations. When the CPU needs to exceed the CPU-cached data, the bandwidth of the cache is a bottleneck in memory---but now the memory is so amazing that it doesn't usually happen.
　　
The second step: (I am using the School website Linux platform (Linux ADVX). Mandrakesoft.com 2.4.3-19mdk))
　　
1: Adjust the server parameters
　　
Use shell>mysqld-help This command sound factory a table of all MySQL options and configurable variables. Output The following information:
　　
Possible variables for option--set-variable (-O) are:
　　
Back_log current Value:5//requires the number of connections that MySQL can have. Back_log indicates how many connection requests can be in the stack when MySQL pauses to accept connections
　　
Connect_timeout current Value:5//mysql server waits for a connection before it is answered with bad handshake (poor translation)
　　
Delayed_insert_timeout current value:200//An insert delayed waits for an insert before terminating
　　
Delayed_insert_limit current VALUE:50//insert delayed processor will check that any SELECT statements are not executed and, if so, execute them before continuing
　　
Delayed_queue_size current value:1000//How many teams are allocated for insert delayed
　　
Flush_time Current value:0//If set to non 0, then every flush_time time, all tables are closed
　　
Interactive_timeout current value:28800//server wait time on Ocean Interactive connection before closing it
　　
Join_buffer_size current value:131072//buffer size with all connections
　　
Key_buffer_size current value:1048540//term the size of the buffer of the index block, increasing it to handle the index better
　　
Lower_case_table_names Current value:0//
　　
Long_query_time Current Value:10//If a query takes longer than this time, the slow_queried count will increase
　　
Max_allowed_packet current value:1048576//size of a package
　　
Max_connections current value:300//number of simultaneous connections allowed
　　
Max_connect_errors Current VALUE:10//If there are more than this number of interrupt connections, will block further connections, you can use the flush hosts to resolve
　　
Max_delayed_threads current VALUE:15//The number of processing insert delayed that can be started
　　
Max_heap_table_size Current value:16777216//
　　
Max_join_size current value:4294967295//number of connections allowed to read
　　
　　Max_sort_length current value:1024//when ordering a blob or text