Hi, Thanks for the ones reply this email.
The "SHOW PROCESSLIST" had a state "Repair by sorting" while creating the index. I assume the set-variable = myisam_max_sort_file_size=2500M set-variable = myisam_max_extra_sort_file_size=2500M is large enough to cache the index. My question is when creating the index, mysql first will write the original table data file to temporary file (something like #sql-xxxx.MYD) and sort the temporary file by the index column, and build the index file. Is it true? I also thinking to use a partial column for the index, now the column I use is VARCHAR(40), and I am not sure when I use create index idx1 on transaction(vendor_id(20)); will help??? -----Original Message----- From: Sergei Golubchik [mailto:[EMAIL PROTECTED]] Sent: Friday, October 26, 2001 10:53 AM To: Wai Lee Cc: [EMAIL PROTECTED] Subject: Re: Indexing Problem Hi! On Oct 26, Wai Lee wrote: > Hi all, > > Can someone tell me how to speed up the index creation??? > > I am trying to build an index for a 13,875,354 records(13 million) table > with 176,322 distinct vendor_id(VARCHAR(40)) in the table. > > the existing size of the transaction table: > transaction.MYD = 2,128,954,624 bytes > transaction.MYI = 1,096,510,464 bytes (there are already 5 indexes built in > this table) > transaciton.frm = 9,450 bytes > > I dig through the manual and changed any possible settings > > set-variable = max_heap_table_size=2000M > set-variable = key_buffer=2500M > set-variable = max_allowed_packet=1M > set-variable = table_cache=512 > set-variable = sort_buffer=2500M > set-variable = join_buffer=2500M > set-variable = record_buffer=2500M > set-variable = myisam_sort_buffer_size=2500M > set-variable = myisam_max_sort_file_size=2500M > set-variable = myisam_max_extra_sort_file_size=2500M > set-variable = thread_cache=8 > > > We are running mySQL in a 4G memory linux box, I believe the buffer size I > allocated to mySQL should cache the whole table. Instead, I am wrong, while > building a new index, I saw there was temporary files created for the > transaction table (the write disk activities were very source consuming) and > the single index creation took 4.5 hours to finished!!!!!!!!!!!!!!!!!!!! > Unbelievable > > Any suggestions will be greatly helpful!!! What did SHOW PROCESSLIST show ? Was it "repair by sort" or "repair with keycache" ? If the latter - increase myisam_max_sort_file_size (and, may be, myisam_max_extra_sort_file_size). Anyway, huge i/o indicates something wrong with variable settings. Regards, Sergei -- MySQL Development Team __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany <___/ --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
