•It is normally not useful to split a table into different tables just because the rows become large. In accessing a row, the biggest performance hit is the disk seek needed to find the first byte of the row. After finding the data, most modern disks can read the entire row fast enough for most applications. The only cases where splitting up a table makes an appreciable difference is if it is a MyISAM  table using dynamic row format that you can change to a fixed row size, or if you very often need to scan the table but do not need most of the columns. See Chapter 16, Alternative Storage Engines . 

•Use ALTER TABLE ... ORDER BY expr1 , expr2 , ...  if you usually retrieve rows in expr1 , expr2 , ...  order. By using this option after extensive changes to the table, you may be able to get higher performance. 

•If you often need to calculate results such as counts based on information from a lot of rows, it may be preferable to introduce a new table and update the counter in real time. An update of the following form is very fast: 

UPDATE tbl_name  SET count_col = count_col +1 WHERE key_col = constant ; 

This is very important when you use MySQL storage engines such as MyISAM  that has only table-level locking (multiple readers with single writers). This also gives better performance with most database systems, because the row locking manager in this case has less to do. 

•Use OPTIMIZE TABLE  periodically to avoid fragmentation with dynamic-format MyISAM  tables. See Section 16.2.3, “MyISAM Table Storage Formats” . 

•Declaring a MyISAM  table with the DELAY_KEY_WRITE=1  table option makes index updates faster because they are not flushed to disk until the table is closed. The downside is that if something kills the server while such a table is open, you must ensure that the table is okay by running the server with the myisam_recover_options  system variable set, or by running myisamchk  before restarting the server. (However, even in this case, you should not lose anything by using DELAY_KEY_WRITE , because the key information can always be generated from the data rows.) 

•Strings are automatically prefix- and end-space compressed in MyISAM  indexes. See Section 13.1.15, “CREATE INDEX Statement” . 

•You can increase performance by caching queries or answers in your application and then executing many inserts or updates together. Locking the table during this operation ensures that the index cache is only flushed once after all updates. 

## 8.6.2 Bulk Data Loading for MyISAM Tables 

These performance tips supplement the general guidelines for fast inserts in Section 8.2.5.1, “Optimizing INSERT Statements” . 

•For a MyISAM  table, you can use concurrent inserts to add rows at the same time that SELECT statements are running, if there are no deleted rows in middle of the data file. See Section 8.11.3, “Concurrent Inserts” . 

•With some extra work, it is possible to make LOAD DATA  run even faster for a MyISAM  table when the table has many indexes. Use the following procedure: 

1. Execute a FLUSH TABLES  statement or a mysqladmin flush-tables  command. 

2. Use myisamchk --keys-used=0 -rq /path/to/db/tbl_name  to remove all use of indexes for the table. 

3. Insert data into the table with LOAD DATA . This does not update any indexes and therefore is very fast. 

