If you intend to change a global variable but want to do so only for the duration of a REPAIR TABLE operation to minimally affect other users, save its value in a user variable and restore it afterward. For example: 

SET @old_myisam_sort_buffer_size = @@GLOBAL.myisam_max_sort_file_size; SET GLOBAL myisam_max_sort_file_size = 100*1024*1024*1024; REPAIR TABLE tbl_name ; SET GLOBAL myisam_max_sort_file_size = @old_myisam_max_sort_file_size; 

The system variables that affect REPAIR TABLE  can be set globally at server startup if you want the values to be in effect by default. For example, add these lines to the server my.cnf  file: 

[mysqld] myisam_sort_buffer_size=256M key_buffer_size=1G myisam_max_sort_file_size=100G 

These settings do not include read_buffer_size . Setting read_buffer_size  globally to a large value does so for all sessions and can cause performance to suffer due to excessive memory allocation for a server with many simultaneous sessions. 

## 8.7 Optimizing for MEMORY Tables 

Consider using MEMORY  tables for noncritical data that is accessed often, and is read-only or rarely updated. Benchmark your application against equivalent InnoDB  or MyISAM  tables under a realistic workload, to confirm that any additional performance is worth the risk of losing data, or the overhead of copying data from a disk-based table at application start. 

For best performance with MEMORY  tables, examine the kinds of queries against each table, and specify the type to use for each associated index, either a B-tree index or a hash index. On the CREATE INDEX statement, use the clause USING BTREE  or USING HASH . B-tree indexes are fast for queries that do greater-than or less-than comparisons through operators such as >  or BETWEEN . Hash indexes are only fast for queries that look up single values through the =  operator, or a restricted set of values through the IN  operator. For why USING BTREE  is often a better choice than the default USING HASH , see Section 8.2.1.23, “Avoiding Full Table Scans” . For implementation details of the different types of MEMORY indexes, see Section 8.3.9, “Comparison of B-Tree and Hash Indexes” . 

## 8.8 Understanding the Query Execution Plan 

Depending on the details of your tables, columns, indexes, and the conditions in your WHERE  clause, the MySQL optimizer considers many techniques to efficiently perform the lookups involved in an SQL query. A query on a huge table can be performed without reading all the rows; a join involving several tables can be performed without comparing every combination of rows. The set of operations that the optimizer chooses to perform the most efficient query is called the “query execution plan” , also known as the EXPLAIN  plan. Your goals are to recognize the aspects of the EXPLAIN  plan that indicate a query is optimized well, and to learn the SQL syntax and indexing techniques to improve the plan if you see some inefficient operations. 

## 8.8.1 Optimizing Queries with EXPLAIN 

The EXPLAIN  statement provides information about how MySQL executes statements: 

• EXPLAIN  works with SELECT , DELETE , INSERT , REPLACE , and UPDATE  statements. 

•When EXPLAIN  is used with an explainable statement, MySQL displays information from the optimizer about the statement execution plan. That is, MySQL explains how it would process the statement, including information about how tables are joined and in which order. For information about using EXPLAIN  to obtain execution plan information, see Section 8.8.2, “EXPLAIN Output Format” . 

