…SELECT * FROM tbl1 INFO OUTFILE ‘/tmp/tbl1.txt’; LOAD DATA INFILE ‘/tmp/tbl1.txt’ INTO TABLE tbl2; instead of: INSERT INTO tbl2 SELECT * from tbl1; INSERT … INTO…
Post: Concurrent inserts on MyISAM and the binary log
May 14, 2008 By 7 Comments
…SELECT INTO OUTFILE followed by LOAD DATA INFILE. You can use InnoDB instead. Or you can do something more elaborate and…
Post: Quick comparison of MyISAM, Infobright, and MonetDB
September 29, 2009 By 30 Comments
…and skipped that. I used LOAD DATA INFILE instead (MonetDB’s version of that is COPY INTO). This is the only way to get data into…and Infobright is 317MB. Next I ran three queries: select sum(c19), sum(c89), sum(c129) from t; select… there is no REPLACE, and there is no INSERT/ON DUPLICATE KEY UPDATE…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
…LOAD DATA INFILE ‘/data/d1/AirData_ontime/${YEAR}_$i.txt.tr’ INTO TABLE ontime FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘\”‘” ontime The load…and space is price for that as I understand), while InfoBright ICE edition allows you only LOAD DATA. InfoBright Enterprise allows INSERT…
Post: Faster MySQL failover with SELECT mirroring
February 1, 2009 By 9 Comments
…, 2, 3 into outfile ‘/tmp/buffer-pool-contents.txt’; I loaded this file into a table on my laptop with LOAD DATA INFILE and kept it for… SELECTs do is keep the insert buffer in a production steady-state. The buffered records are forced to be merged by the SELECTs, and a lot more of the pages from the insert buffer are in the buffer pool…
Comment: Quick comparison of MyISAM, Infobright, and MonetDB
September 30, 2009 By 30 Comments
…, I’m surprised by those LOAD DATA INFILE results. I didn’t catch how many rows you ended up loading, but from the size… using INSERT INTO, then I’m not surprised; that execution path is essentially useful only in the form of INSERT INTO .. SELECT .. and large batches, and even…
Comment: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… done both by LOAD DATA INFILE and INSERT INTO … SELECT if target is empty MyISAM table. In such case the speed is very close. LOAD DATA INFLE in theory… the file but in practice overhead is minimal: mysql> load data infile ‘/tmp/z.txt’ into table two; Query OK, 7340032 rows affected (2…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… make it write out a CSV file and import that with LOAD DATA INFILE. Keep those big fat log… back up, and restore. And the partitioning functionality is not done yet and not fully integrated into the server…it on the master with INSERT..SELECT queries, it will propagate to the slaves and it’ll be …
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
July 23, 2006 By 22 Comments
… which is described SELECT is only setting locks as it is running as the part of INSERT … SELECT statement. INSERT .. INTO OUTFILE/ LOAD DATA INFILE is just one way to work it around, which is simple and efficient. You also…
Comment: How to load large files safely into InnoDB with LOAD DATA INFILE
September 30, 2009 By 29 Comments
…_per_table and indeed all my tables are created correctly as separate files. when i occasionally do ‘insert into new_bigtable select * from bigtable… 100GB+). i think this also happens when i do a ‘load data infile‘ again we’re talking 100GB+ files. Can I make sure… remain at 100GB. and now i have no way of shrinking this back (unless i do a mysqldump and load which for a…
