Hello, everyone I have the following situation:
After "enjoying" problems related to deleting a large portion of a table, subsequent slow selects and such, I decided to do an alternate route when removing data from a table: The table had transactions for one year, and the table really only needs transactions for 6 months. So, I did a mysqldump without table def that extracted the data I really want. Then, I dumped the table, created a new without indexes, and loaded the data. All fine. 64 so million records. Having read a bit about mysql and used it for a few years, I understood that first loading data, and then creating indexes is faster than vice versa. All fine, still. Then, I started creating indexes. First, it seems that mysql makes a complete copy of the table, and then in reindexes it, and then drops the original table, replacing it with the newly indexed table. Is this correct? If so - how do make sure mysql makes one copy, and then performs all the reindexing on that table, rather than making a temp table for each index? Obviously, 64 million rows will take days no matter, but how do I really speed this up? My current hypothesis is that the temp table copy operation is my bottleneck, and that I should only _need_ to perform this operation once. [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking datadir = /data/mysql set-variable = key_buffer=384M set-variable = max_allowed_packet=1M set-variable = table_cache=512 set-variable = sort_buffer=2M set-variable = record_buffer=2M set-variable = thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable = thread_concurrency=8 set-variable = myisam_sort_buffer_size=3000M set-variable = myisam_max_sort_file_size=16000M set-variable = myisam_max_extra_sort_file_size=2500M log-bin server-id = 1 is the relevant portion of the my.cnf file. The table is MyISAM, and the mysql version is 3.23.41-log from red hat 7.2 (yes, I know, 7.3 is better). We are performing the reindexing as a series of create index calls. Please do not hesitate if you have further comments. Thanks in advance! Mvh / Best Regards Jens-Petter Salvesen Lead Developer, Risk Management, Europay Norway Phone : +47 2332 5119 Mobile : +47 9829 7319 --------------------------------------------------------------------- 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
