0

I have to export a huge amount of data. Also I have to transform every record a little bit through php. Whats the right strategy to export large amounts of data?

  • Do I split Zend_Db requests in multiple chunked queries with limit(1000,x)?
  • Do I use fetchAll or fetchRow?
  • Which fetchrow or fetchall is performing better considering high performance?

I cannot use SQL OUTFILE since I have to interpret the xml/html coming from one column. As far as I know mysql is not able to do this. This means for me, I either can use fetchrow or fetchall, but I cannot process on the mysql server. Since I'm fetching a huge amount of data fetchAll may leed to a out of memory of php. So I'm not sure if I can avoid this by using fetchrow or if I have to use chunks anyways? Is fetchrow slower than fetchall?

Manuel
  • 9,112
  • 13
  • 70
  • 110
  • 1
    justy `query` with `SELECT whatever INTO OUTFILE '/tmp/result.csv'` – Gordon Jun 25 '12 at 22:03
  • Hey, this is not a duplicate question! I cannot use INTO OUTFILE since I have to process every record in php. Thus the performance of chucks with fetchAll vs. select all with fetchrow matters. I haven't found the answer yet :( links? – Manuel Jun 26 '12 at 08:36
  • it depends on what you need to process. Since you will write the SQL Query in PHP, you could add any processing logic into the query and then let your database do the calculations and write the result into the outfile. But, okay, update your question please and I'll vote to reopen this. Although you could simply benchmark possible solutions. Will be more accurate to test against your own code than asking here anyway. – Gordon Jun 26 '12 at 09:02
  • @Gordon, I tried to update my question and make it more precise. I guess not being native speaker still makes tiny problems. Is the question now understandable? – Manuel Jun 26 '12 at 11:15
  • 1
    it explains why you cannot use OUTFILE. I still think the best option would be to benchmark it and see for yourself, but voting to reopen. – Gordon Jun 28 '12 at 15:00

1 Answers1

1
SELECT field, field1, field2 
INTO OUTFILE '/home/user/out.csv' 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM your_table WHERE 1=1