MySQL Forums
Forum List  »  InnoDB

Re: Mass Import Innodb
Posted by: Aftab Khan
Date: January 31, 2012 07:06AM

>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!

Options: ReplyQuote


Subject
Views
Written By
Posted
3783
January 29, 2012 07:10AM
1179
January 30, 2012 09:07AM
1163
January 30, 2012 10:24AM
Re: Mass Import Innodb
1108
January 31, 2012 07:06AM
1090
January 31, 2012 02:51AM
1072
January 31, 2012 03:21AM
1141
January 31, 2012 10:20AM
1367
January 31, 2012 10:44AM
1058
February 01, 2012 02:22AM
1185
February 01, 2012 04:33AM
1124
February 06, 2012 05:59AM
1271
February 07, 2012 10:38AM
1138
February 07, 2012 11:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.