MySQL Performance: MySQL => processing large result sets...

processing large result sets... [message #3002]

Fri, 25 April 2008 07:41

sentinel
Messages: 3
Registered: April 2008

Junior Member

Hello,

I have a large table with about half a million (potentially a few million) rows - i need to make a snapshot of the data at a specific moment and process it for export from the database (in an xml file, but that's not that important)...

I have some ideas on how to process them now but each has its drawbacks (i'm using php to process the data, if that is of any importance)...

* one could just make a 'select *' on this table and then process it one row at a time at the application level - the problem - the data might grow so large that the result set does not fit the available RAM...

* one could 'select id from...' and then process it one id at a time, fetching rows and the processing them... but that would require me to first make a copy of the table, so it doesn't change during the whole operation... and, theoretically, it doesn't stop the script from taking too much memory if there are enough rows (not a few million maybe but it's certainly possible)...

* I was also thinking in a direction of processing it part-by-part using 'select .... limit ...' which might (i'm not sure) allow me to process arbitrarily large result sets but I don't think it's a wise idea from performance point of view... of course, I would still need a copy first in this case...

Any other ideas? Maybe some obvious way I'm somehow missing? Some standard way of processing large amounts of data from a MySQL database?

Thanks in advance...

[Updated on: Fri, 25 April 2008 07:43]

Report message to a moderator