10.13 How to arrange a table to be as fast/small as possible

You can get better performance on a table and minimize storage space using the techniques listed below:

• Declare columns to be NOT NULL if possible. It makes everything faster and you save one bit per column.
• Take advantage of the fact that all columns have default values. Insert values explicitly only when the value to be inserted differs from the default. You don't have to insert a value into the first TIMESTAMP column or into an AUTO_INCREMENT column in an INSERT statement. mysql_insert_id().
• Use the smaller integer types if possible to get smaller tables. For example, MEDIUMINT is often better than INT.
• If you don't have any variable-length columns (VARCHAR, TEXT or BLOB columns), a fixed-size record format is used. This is much faster but unfortunately may waste some space. 10.17 What are the different row formats? Or, when should VARCHAR/CHAR be used?.
• To help MySQL optimize queries better, run isamchk --analyze on a table after it has been loaded with relevant data. This updates a value for each index that indicates the average number of rows that have the same value. (For unique indexes, this is always 1, of course.)
• To sort an index and data according to an index, use isamchk --sort-index --sort-records=1 (if you want to sort on index 1). If you have a unique index from which you want to read all records in order according to that index, this is a good way to make that faster. Note however that this sorting isn't written optimally and will take a long time for a large table!
• For INSERT statements, use multiple value lists if possible. This is much faster than using separate INSERT statements.
• When loading a table with data, use LOAD DATA INFILE. This is usually 20 times faster than using a lot of INSERT statements. LOAD DATA. You can even get more speed when loading data into a table with many indexes using the following procedure:
  1. Create the table in mysql or Perl with CREATE TABLE.
  2. Execute mysqladmin flush-tables.
  3. Use isamchk --keys-used=0 -rq /path/to/db/tbl_name. This will remove all usage of all indexes from the table.
  4. Insert data into the table with LOAD DATA INFILE.
  5. If you have pack_isam and want to compress the table, run pack_isam on it.
  6. Recreate the indexes with isamchk -r -q /path/to/db/tbl_name.
  7. Execute mysqladmin flush-tables.
• To get some more speed for both LOAD DATA INFILE and INSERT, enlarge the key buffer. This can be done with the -O key_buffer=# option to mysqld or safe_mysqld. For example, 16M should be a good value if you have much RAM. :)
• When dumping data as text files for use by other programs, use SELECT ... INTO OUTFILE. LOAD DATA.
• When doing many successive inserts or updates, you can get more speed by locking your tables using LOCK TABLES. LOAD DATA INFILE and SELECT ...INTO OUTFILE are atomic, so you don't have to use LOCK TABLES when using them. LOCK TABLES.

To check how fragmented your tables are, run isamchk -evi on the `.ISM' file. 13 Maintaining a MySQL installation.