ok, can you run the following SQLs to dump the output and then import the file into the new DB (table):
--create output file:
SELECT `b`.`id_document`, `a`.`name`, `a`.`longHTMLdescr`, `a`.`fileData`, `a`.`lastupdated`, `a`.`status`, `a`.`code`
INTO OUTFILE '/tmp/__data.txt'
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`;
how long did it take?
#TEST CASE 1:
--load the file using LOAD DATA command
LOAD DATA INFILE '/tmp/__data.txt' INTO TABLE `newDB`.`documentlist`;
how long did it take?
#TEST CASE 2 (using compress function -
http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html#function_compress):
--Create a new table in `newDB`.documentlist_2 but use ROW_FORMAT=DYNAMIC, and load the data file:
LOAD DATA INFILE '/tmp/__data.txt' INTO TABLE `newDB`.`documentlist_2`
SET longHTMLdescr=compress(longHTMLdescr), fileData=compress(fileData);
how long did it take?
--Compare the disk space used `newDB`.`documentlist`, you can simply check the file sizes i.e. ls -lh /path/to/datadir/newDB
#TEST CASE 3 :
Drop the table used in TEST CASE 2 i.e. DROP TABLE `newDB`.`documentlist_2`; and recreate it with following options:
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=16;
(You haven't specified the KEY_BLOCK_SIZE used with `newDB`.documentlist table, Assuming it is left to the default 8KB. However, if you are currently using value 16 then try using value 4,8 for this test)
--load the file using LOAD DATA command
LOAD DATA INFILE '/tmp/__data.txt' INTO TABLE `newDB`.`documentlist_2`;
how long did it take?
Edited 2 time(s). Last edit at 02/06/2012 12:27AM by Aftab Khan.