Specifications

You can also use the myisamchk utility to sort a table index and the data according to that
index, like this:
>myisamchk --sort-index --sort-records=1 pathtomysqldatadirectory/*/*.MYI
Using Indexes
Use indexes where required to speed up your queries. Keep them simple, and don’t create
indexes that are not being used by your queries. You can check which indexes are being used
by running EXPLAIN as shown previously.
Use Default Values
Wherever possible, use default values for columns, and only insert data if it differs from the
default. This reduces the time taken to execute the
INSERT statement.
Use Persistent Connections
This particular optimization tip applies particularly to Web databases. We’ve already discussed
it elsewhere so this is just a reminder.
Other Tips
There are many other minor tweaks you can make to improve performance in particular situa-
tions and when you have particular needs. The MySQL Web site offers a good set of additional
tips. You can find it at
http://www.mysql.com
Different Table Types
One last useful thing to discuss before we leave MySQL for the time being is the existence of
different types of tables. You can choose a table type when you create a table, using
CREATE TABLE table TYPE=type ....
The possible table types are
• MyISAM. This is the default, and what we have used to date. This is based on ISAM, which
stands for Indexed Sequential Access Method, a standard method for storing records and
files.
• HEAP. Tables of this type are stored in memory, and their indexes are hashed. This makes
HEAP tables extremely fast, but, in the event of a crash, your data will be lost. These char-
acteristics make HEAP tables ideal for storing temporary or derived data. You should spec-
ify the
MAX_ROWS in the CREATE TABLE statement, or these tables can hog all your
memory. Also, they cannot have
BLOB, TEXT, or AUTO INCREMENT columns.
Using MySQL
P
ART II
262
14 7842 CH11 3/6/01 3:35 PM Page 262