MySQL : retrieve a large select by chunks

by

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;

More Related Contents:

  1. Recreating a query using INNER JOIN? [duplicate]
  2. MySQL – UPDATE query based on SELECT Query
  3. quick selection of a random row from a large table in mysql
  4. MySQL: is a SELECT statement case sensitive?
  5. Create a temporary table in a SELECT statement without a separate CREATE TABLE
  6. MySQL SELECT WHERE datetime matches day (and not necessarily time)
  7. MySQL Query – Records between Today and Last 30 Days
  8. Ordering by specific field value first
  9. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  10. INSERT INTO…SELECT for all MySQL columns
  11. Combine Multiple child rows into one row MYSQL
  12. #1139 – Got error ‘repetition-operator operand invalid’ from regexp
  13. MySQL select statement with CASE or IF ELSEIF? Not sure how to get the result
  14. How to get an age from a D.O.B field in MySQL?
  15. Checking multiple columns for one value
  16. MySQL difference between two rows of a SELECT Statement
  17. MySQL – How to search for exact word match using LIKE?
  18. MySQL – How to select data by string length
  19. MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?
  20. How do I get the first day of the week of a date in mysql?
  21. MySQL error 1241: Operand should contain 1 column(s)
  22. quick selection of a random row from a large table in mysql
  23. Converting SELECT DISTINCT ON queries from Postgresql to MySQL
  24. mysql select sum group by date
  25. SELECT query return 1 row from each group
  26. Comma separated string of selected values in MySQL
  27. SQL query to check if a name begins and ends with a vowel
  28. SELECT INTO Variable in MySQL DECLARE causes syntax error?
  29. MySQL Error: #1142 – SELECT command denied to user
  30. mySQL returns all rows when field=0

Leave a Comment