Hi,
Instead of using separate "CREATE INDEX" statements, you can build all your index at once with "ALTER TABLE": ALTER TABLE my_table ADD INDEX ..., ADD INDEX ... , ADD INDEX ... ; Hope this helps, -- Joseph Bueno Salvesen, Jens-Petter wrote: > 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 > --------------------------------------------------------------------- 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
