List:General Discussion« Previous MessageNext Message »
From:Dan Buettner Date:May 2 2007 2:28pm
Subject:Re: [X-POST] Fastest way to dump this huge table
View as plain text  
A few observations:

1 - if the table is in the InnoDB format, you aren't going to lock up their
server, as InnoDB doesn't do table locking.  SHOW TABLE STATUS LIKE
'tablename' will tell you.

2 - Ryan's mysqldump script looks useful - also, there's a little-used
option with mysqldump that lets you specify a where clause to get just the
records you want into the SQL file.

3 - since you're not operating on the server itself, but transferring over
the net, the time for the transfer could become a problem, especially if
you're not using InnoDB.  You could copy the data into a temp table and then
work with that to your heart's content, without tying up production tables.
Something like this:
DROP TABLE IF EXISTS _tmp_tablename;
CREATE TABLE _tmp_tablename LIKE tablename;
INSERT INTO _tmp_tablename SELECT * FROM tablename WHERE whatiwant blah blah
then use select into outfile, mysqldump, php etc. on the _tmp_tablename
While this does involve copying lots of records, in my experience, this sort
of thing can be very fast, since it's all self-contained in the database
software.  Not having any indices on your temp table will help too.


On 5/2/07, Brian Dunning <brian@stripped> wrote:
> I have a huge MySQL table, 2.1 million records, 200MB. Once a week I
> need to dump it in CSV format and zip the file.
> This is not on my server, and it's in production, so I don't want to
> risk testing different methods and possibly hanging up their server
> for a period of time, so I wanted to seek advice here first to find
> what's the best way to proceed.
> I can easily use PHP to query the table for the results I want and
> write a file line by line and then zip it, but I'm worried that might
> take too long and hang up the machine. The other way to go is some
> kind of sql dump command, which I guess would be faster, but not sure
> how much control I'd have over the exact format of the file. Any
> suggestions which way I should proceed? Not hanging up their server
> is my prime concern.
> --
> MySQL General Mailing List
> For list archives:
> To unsubscribe:

[X-POST] Fastest way to dump this huge tableBrian Dunning2 May
  • RE: [X-POST] Fastest way to dump this huge tableJay Blanchard2 May
  • Re: [X-POST] Fastest way to dump this huge tableRyan Stille2 May
  • Re: [X-POST] Fastest way to dump this huge tableDan Buettner2 May
    • Re: [X-POST] Fastest way to dump this huge tableBrian Dunning2 May
      • Re: [X-POST] Fastest way to dump this huge tableDan Buettner2 May