Optimize MySQL DataBase Query

Optimize MySQL DataBase Query
The simplest and safest format is the fastest disk format. the speed comes from the difficulty that data can be found on the disk. when locking an index or static format, it is very simple, just multiply the length of the row by the number. in addition, when scanning a table, it is easy to read constant records from a disk each time. security comes from the fact that if the computer is down when a static myisam file is written, myisamchk can easily point out where each row starts and ends. Therefore, it can usually retrieve all records, except for some written records. all indexes in mysql can always be rebuilt.
　　
1.2 dynamic myisam
　　
In this format, each line must have a header indicating how long it is. when a record changes for a long time, it can end at more than one position. you can use optimize tablename or myisamchk to organize a table. if you want to access/change static data as some varchar or blob columns in the same table, move the dynamic column into another table to avoid fragmentation.
　　
1.2.1 compress myisam and use the optional myisampack tool to generate
　　
1.2.2 memory
　　
This format is useful for small/medium-sized tables. copying/creating a common query table to a foreign heap table may speed up the joining of multiple tables. using the same data may take 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 accelerate it, you can use tablename2 and tablename3 to create a temporary table, because you can use the same column (tablename1.a) to search for 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 static table features
　　
1.3.1 default format. used when the table does not contain varchar, blob, and text Columns
　　
1.3.2 fill all char, numeric, and decimal columns with column width
　　
1.3.3 fast
　　
1.3.4 easy Buffering
　　
1.3.5 it is easy to re-create after being down because the record is in a fixed position
　　
1.3.6 do not need to be reorganized (with myisamchk) unless a large number of records are deleted and the storage size is optimized
　　
1.3.7 generally requires more storage space than dynamic tables
　　
1.4 Characteristics of Dynamic tables
　　
1.4.1 if the table contains any varchar, blob, and text columns, use this format
　　
1.4.2 all string columns are dynamic
　　
1.4.3 each record is preceded by a single digit.
　　
1.4.4 usually requires more disk space than a fixed-length table
　　
1.4.5 each record only uses the required space. If a record becomes large, it is divided into many segments as needed, resulting in record fragmentation.
　　
1.4.6 if the row is updated with information that exceeds the length of the row, the row is segmented.
　　
1.4.7 it is difficult to recreate the table after the system is down, because a record can be multiple segments
　　
1.4.8 the expected row length for dynamic size records is 3 + (number of columns + 7)/8 + (number of char columns) + packed size of numeric columns + length of strings + (number of NULL columns + 7)/8
　　
Penalty of 6 bytes for each connection. A dynamic record is connected whenever the change causes the record to become larger. each new connection has at least 20 bytes, so the next change may be in the same connection. if not, there will be another connection. you can use myisamchk-to check the number of connections. all connections can be deleted using myisamchk-r.
　　
1.5 features of a compressed table
　　
1.5.1 a read-only table created using the myisampack utility.
　　
1.5.2 The decompression code is stored in all mysql distributions so that connections without myisampack can read tables compressed with myisampack.
　　
1.5.3 occupies a small disk space
　　
1.5.4 each record is compressed separately. The header of a record is a fixed length (1 ~~ 3 bytes) depending on the maximum table record. Each column is compressed in different ways. Some common compression types are:
　　
A: Normally, there is a different table for each column. Table B: suffix blank compression c: prefix blank compression d: Use a number with a value of 0 to use one-bit storage.
　　
E: if the value of an integer column has a small range, the column uses the smallest possible type for storage. For example, if all values are between 0 and 255, A bigint can be stored as a tinyint.
　　
G: If a column has only one small set of possible values, the column type is converted to enum h: the combination of the preceding compression methods can be used for the column.
　　
1.5.5 can process records with a fixed or dynamic length, but cannot process blob or text columns 1.5.6 and decompress with myisamchk
　　
Mysql supports different index types, but the general type is isam. This is a B-tree index and can roughly calculate the size of the index file as (key_length + 4) * 0.67, the sum of all keys.
　　
String indexes are empty compressed. If the first index is a string, it can compress the prefix. If the string column contains many trailing spaces or a varchar column with the full length of the primary path, empty compression will make the index file smaller. if many strings have the same prefix.
　　
1.6 features of memory tables
　　
The heap table in mysql uses the 100% dynamic hash for every occasional overflow and there are no deletion problems. you can only use an index in the heap table to access things using equations (usually using the '=' operator)
　　
The disadvantages of heap tables are:
　　
1.6.1 all heap tables to be used at the same time require sufficient additional memory
　　
1.6.2 cannot be searched in one part of the Index
　　
1.6.3 The next entry cannot be searched in sequence (that is, an order by index is used)

: Memory limit. When the cpu needs to exceed the data suitable for the cpu cache, the cache bandwidth becomes a bottleneck in the memory. However, the memory size is surprisingly large, and this problem will not occur.
　　
Step 2: (I am using the linux platform of the school website (Linux ADVX.Mandrakesoft.com 2.4.3-19mdk ))
　　
1: Adjust server Parameters
　　
Use the shell> mysqld-help command to output the following information:
　　
Possible variables for option -- set-variable (-o) are:
　　
Back_log current value: 5 // the number of connections that mysql can have. back_log indicates how many connection requests can be stored in the stack during the time when mysql suspends the connection.
　　
Connect_timeout current value: 5 // mysql Server waits for a connection time before responding with bad handshake
　　
Delayed_insert_timeout current value: 200 // The time for an insert delayed to wait for the insert before termination
　　
Delayed_insert_limit current value: 50 // The insert delayed processor checks whether any select statements are not executed. If yes, run these statements before resuming.
　　
Delayed_queue_size current value: 1000 // the size of the queue allocated for insert delayed
　　
Flush_time current value: 0 // if it is set to a non-0 value, all tables are closed for each flush_time.
　　
Interactive_timeout current value: 28800 // time the server waits for a foreign interactive connection before closing it
　　
Join_buffer_size current value: 131072 // the buffer size connected to all
　　
Key_buffer_size current value: 1048540 // the buffer size of the clause index block. You can increase it to better process the index.
　　
Lower_case_table_names current value: 0 //
　　
Long_query_time current value: 10 // If a query takes more time 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 concurrent connections allowed
　　
Max_connect_errors current value: 10 // if there are more than this number of interrupted connections, further connections will be blocked. You can use flush hosts to solve this problem.
　　
Max_delayed_threads current value: 15 // Number of insert delayed processes that can be started
　　
Max_heap_table_size current value: 16777216 //
　　
Max_join_size current value: 4294967295 // Number of connections that can be read
　　
Max_sort_length current value: 1024 // when sorting blob or text