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
3466
January 29, 2012 07:10AM
1052
January 30, 2012 09:07AM
1048
January 30, 2012 10:24AM
Re: Mass Import Innodb
1009
January 31, 2012 07:06AM
964
January 31, 2012 02:51AM
888
January 31, 2012 03:21AM
1029
January 31, 2012 10:20AM
1257
January 31, 2012 10:44AM
963
February 01, 2012 02:22AM
1056
February 01, 2012 04:33AM
999
February 06, 2012 05:59AM
1139
February 07, 2012 10:38AM
1004
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.