Imran Chaudhry <[EMAIL PROTECTED]> wrote on 01/31/2006 12:44:17 PM: > > I am using mysql5.0 on Hp-UX. IT took about 14 hours to insert 1.7 > millin records. How do I make >my insert run faster.The table has > three foreign key references and the referencing columns are > >indexed . Is that impacting the insert statement performance. > > Just thought I'd add a little to the good advice already given to you. > I find an easy way to derive the extended insert syntax is to perform > a mysqldump of a small table. > From 4.1 onwards this wrapped the table dump in the extended insert syntax. > > Regards, > Imran > > -- > http://www.ImranChaudhry.info > MySQL Database Management & Design Services >
The only drawback to doing that for really large tables is you have to provide mysqldump with the --max_allowed_packet value for the server you want to send the data to. If you don't, mysqldump will make a single HUMONGOUS extended insert statement that will be refused by the destination server. I found this out the hard way a long time ago. For example: If on server A your max_allowed_packet value is 4193280... serverA>show variables like 'max%'; +----------------------------+------------+ | Variable_name | Value | +----------------------------+------------+ | max_allowed_packet | 4193280 | | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824 | | max_connect_errors | 10 | | max_connections | 100 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 16777216 | | max_insert_delayed_threads | 20 | | max_join_size | 4294967295 | | max_length_for_sort_data | 1024 | | max_relay_log_size | 0 | | max_seeks_for_key | 4294967295 | | max_sort_length | 1024 | | max_tmp_tables | 32 | | max_user_connections | 0 | | max_write_lock_count | 4294967295 | +----------------------------+------------+ 17 rows in set (0.00 sec) ...and the data you want to load comes from server B then you have to tell mysqldump to use the value 4193280 or it will make just one large extended INSERT statement per table, regardless of table size \mysql\bin> mysqldump -u login -p -h serverB --max_allowed_packet=4193280 sourcedatabasename Of course there are other options you need to worry about too (like quoting and compressing) but you get the picture. You can read about them in the fine manual or check the --help option of mysqldump. mysqldump --help Shawn Green Database Administrator Unimin Corporation - Spruce Pine
