How should I arrange my table to be as fast/small as possible?

• Use NOT NULL if possible. It makes everything faster and you save one bit per column.
• All columns have default values. Only insert when the default value isn't acceptable. You don't have to insert the columns of a timestamp or an autoincremented index in the insert statement. See section How can I get the unique ID for the last inserted row?.
• Use the smaller INT types if possible to get smaller tables. For example, MEDIUMINT is often better than INT.
• If you don't have any VARCHAR columns, a fixed size record format will be used. This is much faster but may unfortunately waste some space. See section What are the different row formats? Or when to use VARCHAR/CHAR?.
• To make MySQL optimize queries better, run isamchk --analyze on the table once it is loaded with relevant data. This updates a value for each index that tells how many rows that have the same value for this index on average. Of course, this is always 1 for unique indexes.
• 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 numeric order, this is a good way to make that faster.
• When loading a table with data use LOAD DATA FROM INFILE. This is usually 20 times faster than using a lot of INSERTs. If the text file isn't on the server, rcp it to the server first. See section LOAD DATA INFILE syntax. You can even get more speed when loading data to tables with many indexes by doing:
  • Create the table in mysql or perl with CREATE TABLE...
  • Do mysqladmin refresh.
  • Use isamchk --keys-used=0 database/table_name. This will remove all usage of all indexes from the table.
  • Insert data into the table with LOAD DATA INFILE....
  • If you have pack_isam and want to compress the table, run pack_isam on it.
  • Recreate the indexes with isamchk -r -q database/table_name.
  • Do mysqladmin refresh.
  The other possibility to get some more speed for both LOAD DATA FROM INFILE and INSERT is to enlarge the key buffer. This can be done with the -O key_buffer=# option to (safe)mysqld. For example 16M should be a good value if you have much RAM :)
• When dumping data as textfiles to other programs, use SELECT ... INTO OUTFILE. See section LOAD DATA INFILE syntax.
• When doing many inserts/updates in a row, you can get more speed by using LOCK TABLES on the tables. ...FROM INFILE... and ...INTO OUTFILE... are atomic so you don't have to use LOCK TABLES when using these. See section LOCK TABLES syntax.

To check how you are doing, run isamchk -evi on the .ISM file. @c See section The MySQL table check, optimise and repair program.

Go to the first, previous, next, last section, table of contents.