Can load data in file(mysql bulk uploads) read compressed files?

Question

I have a large xml file I want to load into mysql. Its about 20 GBs uncompressed but I think I can compress it down to about 25% of its original size then bulk upload it to a compressed table.

I know I can compress the data in the database itself but can it read compressed files during the bulk add process?

Edit: By compressed I don't meant its a .gz.tar file or anything. I mean when I'm creating the file in Java or C++ I'm outputting it as gzip, so the file itself is a .csv or .xml and retains the correct structure but the items in it(each row) is compressed.

If this is not possible, can I do something like bulk upload but somehow filter it through a program that uncompresses the contents? I was thinking of opening the file in C and compressing it while loading it to mysql. The problem is I want to do this in a bulk insert and not millions of individual inserts.

Answer 1

You should DEFINITELY be using LOAD DATA INFILE for this. Inserts can be an order of magnitude slower than LOAD DATA INFILE, especially on larger files.

Unfortunately, MySQL does not natively support directly loading compressed files (of any kind, as far as I'm aware). However if you look at the LOAD DATA INFILE link above, there is a trick to load data directly from a pipe into a table (just search the page for 'pipe'). I suppose you could decompress the file and pipe the output to MySQL using that method, but compressing/decompressing will obviously be slower than directly loading an uncompressed file.