Take a look at
http://lists.mysql.com/mysql/158737
for an interesting 'trick' to optimze index creation.
Basically it amounts to:
* Create the data without indexes
* Move (rename) the table.MYD file to a backup
* Create an empy MYD file using TRUNCATE TABLE
* Add indexes
* Move the backed up MYD
http://www.geocrawler.com/archives/3/8/2002/11/50/10245455/
Kyle
- Original Message -
From: "Jeffrey Horner" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, March 29, 2004 12:05 PM
Subject: Creating index on very large table
> Hi folks,
>
> I've got a problem creating an index
I had same problem. I fixed it by adding/adjusting the
myisam_max_sort_file_size and myisam_max_extra_sort_file_size variables in
my.cnf to much larger values.
I adjust it to 4096M in my case (data = 35 Gb, index = 2.1 Gb ) and the
indexes go much faster. On similar box my index creation takes a
8 hours to
reindex
-pete
-Original Message-
From: Richard Davey [mailto:[EMAIL PROTECTED]
Sent: Monday, March 29, 2004 10:12 AM
To: [EMAIL PROTECTED]
Subject: Re: Creating index on very large table
Hello Jeffrey,
Monday, March 29, 2004, 7:05:27 PM, you wrote:
JH> I've got a problem
Hello Jeffrey,
Monday, March 29, 2004, 7:05:27 PM, you wrote:
JH> I've got a problem creating an index on a MYISAM table with 302,000,000 lines,
JH> roughly 58 GB on disk. Attached you will find the table definition, output
JH> of "mysqladmin variables", and "mysql -e 'show status'".
JH> and it'
