Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

Record format: Packed

table description:

Key Start Len Index Type

1 1 6 multip. text

161 2 text

191 3 text

Note Note that you will need to be in the correct directory, such as

/var/lib/mysql/databasename/, or explicitly specify the path to the tables.

Also note that you can use the * wildcard to check all the tables in a database

directory.

This output tells you that the table has 15 records and 5 deleted blocks. The latter is wasted space on

the hard disk.

Reclaiming Wasted Space

You can recover the wasted space shown in the previous example by running isamchk -r:

[root@tigger book]# isamchk -r customers

• recovering ISAM-table 'customers.ISM'
  Data records: 15


• Fixing index 1
  This command looks at the table and recreates it without using unnecessary space. In this particular
  example, running ls -l under Linux reveals that customers.ISD now occupies just 756 bytes
  compared with 1015 before isamchk -r was run.
  It's a good idea to run isamchk -d on a regular basis on a growing database to keep an eye on what
  spare space is being left in the tables. This is especially important if your application performs a lot of
  DELETEs, which will most likely result in gaps among the data on your hard disk. Whenever the deleted
  blocks count appears to be getting high, or a significant proportion of the data records, run isamchk -
  r to clear out the wasted[[$I~databases;corrupted;repairing, reclaiming wasted space]][[$I~corrupted
  databases;repairing;wasted space, reclaiming]][[$I~repairing;corrupted databases;wasted space,
  reclaiming]][[$I~wasted space;reclaiming]][[$I~space;wasted;reclaiming]][[$I~reclaiming wasted space]]
  space.
  Caution If any clients are accessing your database via mysqld at the time you run
  isamchk, isamchk may be fooled into thinking the tables are corrupted when
  they're not. Any updates currently in progress may appear as corruptions to
  isamchk. Thus, carrying out an isamchk repair at this point could therefore
  be very harmful.
  To avoid this, shut down mysqld before running isamchk. Alternatively,
  provided you're quite sure that nobody is accessing the tables, just run
  mysqladmin flush-tables before isamchk.
  If you're going to do table repairs or optimization, it is essential that you shut
  down mysqld before letting isamchk loose. If you can't shut down mysqld
  for a while, at least run mysqladmin flush-tables and prevent clients
  from accessing the database until repairs are complete.

Repairing Damaged Tables with isamchk

The general syntax for using isamchk is as follows:

isamchk [options] table_name

While isamchk works on .ISM/.ISD table types, myisamchk does the same thing on the newer

MyISAM table types with .MYI and .MYD extensions, respectively.

Options can be specified as follows:

ƒ -a, --analyze Analyzes the distribution of keys; useful to speed up some join

operations.

ƒ -#=debug_options, --debug=debug_option Creates a log for debugging, often

used in the form 'd:t:o,filename' to save to filename.

ƒ -d, --description Gets some descriptive information about the state of the table.