Dear Sirs and Ladies,
I tried to put some keys on a large table (more than 100.000.000 in 13G)
with the Statement:
ALTER TABLE mytable
ADD INDEX i1 (COL1(4)),
ADD INDEX i2 (COL2(4)),
ADD INDEX i3 (COL3(12)),
ADD INDEX i4 (COL4(12)),
ADD INDEX i5 (COL5(5)),
ADD INDEX i5 (COL6(11));
After about 7 hours, a 'SHOW PROCESSLIST' doesn't say 'copy to temp-table'
anymore but 'repair with keycache'.
Watching the the directory for this database, it seems to happpen at the
time when all data from .MYD is copied to
the temp-table.
The 'repair with keycache' now lasts about 12 hours and I don' know when it
comes to an end.
Is this a usual thing or did I do something wrong?
I thought 'repair with keycache' just happens while recovering of a crashed
table.
How can I speed up this process ?
Is innodb on MySQL 4.x faster doing this ?
In order ro increase speed of creating an index, I increased the
key_buffer-varibale to 1024M,
which is 60% of the memory on that engine.
OS is SUSE-LINUX 8.0 (Kernel 2.4.4)
MySQL is 2.23.52.
FS is Reiser.
The Table is myisam and looks like this:
Field Type Null Key Default Extra
COL_A char(2)
COL1 varchar(4)
COL2 varchar(4)
COL6 varchar(11)
COL_B enum('-1','0','1','2') YES (NULL)
COL3 varchar(35)
COL4 varchar(55)
COL_C varchar(35)
COL_D varchar(55)
COL_E varchar(61)
COL_F varchar(30)
COL_5 varchar(5)
COL_G varchar(50)
COL_H varchar(50)
COL_I varchar(50)
COL_J varchar(14)
COL_K int(11) 0
COL_O int(11) 0
To make it a bit easier to read, i named all collumns with an index with a
number (like COL_1).
This table will be filled once a week and there will be no further updates
or inserts till one wwek later,
when it will be dropped and rebuild with new data.
It is the only table in this database and (except mysql's privilege-tables)
the only table on this engine at all.
Please Help me
Klaus
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
