MySQL : retrieve a large select by chunks

Question

I have select with more then

70 milion rows

I'd like to save the selected data into the one large csv file on win2012 R2

Q: How to retrieve the data from MySQL by chunks for better performance ?

because when I try to save one the large select I got

out of memory errors

Answer 1

You could try using the LIMIT feature. If you do this:

SELECT * FROM MyTable ORDER BY whatever LIMIT 0,1000

You'll get the first 1,000 rows. The first LIMIT value (0) defines the starting row in the result set. It's zero-indexed, so 0 means "the first row". The second LIMIT value is the maximum number of rows to retrieve. To get the next few sets of 1,000, do this:

SELECT * FROM MyTable ORDER BY whatever LIMIT 1000,1000 -- rows 1,001 - 2,000
SELECT * FROM MyTable ORDER BY whatever LIMIT 2000,1000 -- rows 2,001 - 3,000

And so on. When the SELECT returns no rows, you're done.

This isn't enough on its own though, because any changes done to the table while you're processing your 1K rows at a time will throw off the order. To freeze the results in time, start by querying the results into a temporary table:

CREATE TEMPORARY TABLE MyChunkedResult AS (
  SELECT *
  FROM MyTable
  ORDER BY whatever
);

Side note: it's a good idea to make sure the temporary table doesn't exist beforehand:

DROP TEMPORARY TABLE IF EXISTS MyChunkedResult;

At any rate, once the temporary table is in place, pull the row chunks from there:

SELECT * FROM MyChunkedResult LIMIT 0, 1000;
SELECT * FROM MyChunkedResult LIMIT 1000,1000;
SELECT * FROM MyChunkedResult LIMIT 2000,1000;
.. and so on.

I'll leave it to you to create the logic that will calculate the limit value after each chunk and check for the end of results. I'd also recommend much larger chunks than 1,000 records; it's just a number I picked out of the air.

Finally, it's good form to drop the temporary table when you're done:

DROP TEMPORARY TABLE MyChunkedResult;

Answer 2

The LIMIT OFFSET approach slows query down when a size of the data is very large. Another approach is to use something called Keyset pagination. It requires a unique id in your query, which you can use as a bookmark to point to the last row of the previous page. The next page is fetched using the last bookmark. For instance:

SELECT user_id, name, date_created
FROM users
WHERE user_id > 0
ORDER BY user_id ASC
LIMIT 10 000;

If the resultset above returns the last row with user_id as 12345, you can use it to fetch the next page as follows:

SELECT user_id, name, date_created
FROM users
WHERE user_id > 12345
ORDER BY user_id ASC
LIMIT 10 000;

For more details, you may take a look at this page.

Answer 3

Another approach for such a large dataset, to avoid the need to chunk the output, would be to query the relevant data into its own new table (not a temporary table) containing just the data you need, and then use mysqldump to handle the export to file.

Answer 4

Use an unbuffered result set using MYSQLI_USE_RESULT to be able to read through the database and perform a function such as write output to a CSV file row by row.

In short: It writes to CSV/File while reading from Database.

When using mysqli_query it uses MYSQLI_USE_STORE by default and reads the whole database and gets a result set which causes excess memory usage.

Read this for more info on MYSQLI_USE_RESULT and be careful since you may not being able to perform other tasks/queries on the database while the function is running