Can I force MySQL to output results before query is completed?

I have a large MySQL table (about 750 million rows) and I just want to extract a couple of columns.

SELECT id, delid FROM tbl_name;

No joins or selection criteria or anything. 开发者_StackOverflow社区There is an index on both fields (separately).

In principle, it could just start reading the table and spitting out the values immediately, but in practice the whole system just chews up memory and basically grinds to a halt.

It seems like the entire query is being executed and the output stored somewhere before ANY output is produced...

I've searched on unbuffering, turning off caches etc, but just cannot find the answer.

(mysqldump is almost what I want except it dumps the whole table - but at least it just starts producing output immediately)

Have you looked at load data infile and select into?

I think the following is close to what you want to do:

select id,delid 
  into outfile 'C:\\ronnis.csv'
       fields terminated by ','
  from tbl_name;

<perform unix sort>

load data infile 'C:\\ronnis.csv' 
into table new_tbl_name
fields terminated by ',';

The only thing I can think of is manually doing this with a LIMIT. I don't know how much of a speed improvement you will get in total, but at least your resultset will be smaller. You will, on the other hand, have to deal with several queries, which might make this a bad sollution.

You could use an unbuffered query, but as you cannot "sneak in" other queries while this is running, I don't know if that is a sollution in your case