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
AUTO_INCREMENT column in an
Use the smaller integer types if possible to get smaller tables. For example,
MEDIUMINT is often better than
If you don't have any variable-length columns (
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
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!
INSERT statements, use multiple value lists if possible. This
is much faster than using separate
When loading a table with data, use
LOAD DATA INFILE. This is usually
20 times faster than using a lot of
You can even get more speed when loading data into a table with many indexes
using the following procedure:
Create the table in
mysql or Perl with
isamchk --keys-used=0 -rq /path/to/db/tbl_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 /path/to/db/tbl_name.
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
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.
When doing many successive inserts or updates, you can get more speed by
locking your tables using
LOAD DATA INFILE and
SELECT ...INTO OUTFILE are atomic, so you don't have to use
TABLES when using them.
To check how fragmented your tables are, run
isamchk -evi on the
13 Maintaining a MySQL installation.