Hi Franz,
Check out
MAX_ROWS = #
and
AVG_ROW_LENGTH = #
options in http://www.mysql.com/doc/en/CREATE_TABLE.html
Set MAX_ROWS to a very very large number
You can set these with ALTER TABLE. See section 6.5.4 ALTER TABLE
Syntax using table options at the end. Use the MAX_ROWS
parameter and set it to some really large value (anything over 2^32
like 100000000000). This ALTER statement will cause your indexes to
be rebuilt using long pointers. You can also create the table this
way using MAX_ROWS option in create statement.
You can verify your changes by running SHOW TABLE STATUS before the
change then again after the change.
Have you done this already? Ohterwisde your key size will be too
small.
Ken
----- Original Message -----
From: "Franz, Fa. PostDirekt MA" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, October 17, 2003 2:34 AM
Subject: repair with keycache during ADD INDEX
> 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]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
