Re: How to shrink the size of the index file for a myisam table ?

[JinNo Kim]

table1 is a table created with PACK_KEYS=1. It has a number of columns, mostly
integers with a few varchar fields. There are ~20 Million rows in the table and
it was updated with the indexes intact in batches of ~1 million rows. Below is
the output of a couple commands.

Fow whatever reason, the index seems to grow to size+new for each batched insert.
I don't analyze it, just know it's happening. The mysqloptimize seems to fix the
problem quickly (about the time it takes to write the new disk file on our
hardware). We don't normally bother with the mysqloptimize because we will
myisampack the table in a few days and the myisamcheck that follows takes care of
the index at the same time.


Check this out:

$ ls -l table1.M*
-rw-rw---- 1 mysql mysql 3410895664 Apr 22 04:43 table1.MYD
-rw-rw---- 1 mysql mysql 12627474432 Apr 22 18:41 table1.MYI
$ mysqloptimize -u root -p -o database table1
Enter password:
database.table1 OK
$ ls -l table1.M*
-rw-rw---- 1 mysql mysql 3410895664 Apr 22 04:43 table1.MYD
-rw-rw---- 1 mysql mysql 294864896 Apr 24 20:20 table1.MYI


Does running the mysqloptimize have any effect on the disk file for your index
when PACK_KEYS=1 on the table?

-JNK



Edited 1 time(s). Last edit at 04/24/2006 03:16PM by JinNo Kim.