Re: Mass Import Innodb

[Aftab Khan]

>INSERT IGNORE INTO `newDB`.`documentlist` (`id_document`, `name`, `longHTMLdescr`, `fileData`, `lastupdated`, `status`, `code`)
SELECT `b`.`id_document`, `a`.`name`, `a`.`longHTMLdescr`, `a`.`fileData`, `a`.`lastupdated`, `a`.`status`, `a`.`code`
FROM `oldDB`.`documentlist` as `a`
INNER JOIN `newDB`.`temp_docids` as `b` ON `a`.`id_document`=`b`.`id_document_old`
WHERE `b`.`id_document` >= 1 AND `b`.`id_document` < 10000
ORDER BY `b`.`id_document`;

Assuming your SELECT query is optimized, right? can you provide EXPLAIN output for the SELECT part of the above sql. how long the above INSERT- SELECT take to finish and how many records are expected to be copied using this SQL?

>WHERE `b`.`id_document` >= ? AND `b`.`id_document` < ?

The values for '?' are hard coded or do you have a script that changes id_document between loop iterations?


I think you may need to tidy up your mysql options file (/etc/my.cnf) as I see alot of dups:


>innodb_flush_log_at_trx_commit=1
>innodb_flush_log_at_trx_commit=2

>tmp_table_size = 256M

The value for this appear to be very large!