Mysql query eating large amount of space using limit

Question

I'm trying to query a very large table some 35+ millions rows to process each row 1 by 1 because I can't pull in the full database in php at once (out of memory) I'm using 'limit' in a loop but every time it trys to query the 700K mark it throws an out of disk space error (error 28)

select * from dbm_new order by id asc  limit 700000,10000

I'm pulling in 10K rows at once into php and I can even make it pull in 100K rows it still throws the same error trying to start at row 700K, I can see it's eating a huge amount of disk space.

In php I'm freeing the result set after each loop

mysql_free_result ($res);

But it's not a PHP related issue, I've run the query in mysql only and it gives the same error

Why does starting the limit at the 700K mark eat up so much disk space, I'm talking over 47gig here, surely it doesn't need that much space, What other options do I have?

here's the code

$start = 0;

$increment = 10000;

$hasResults = true;


while ($hasResults) {

$sql = "select * from dbm_new order by id asc  limit $start,$increment ";

    ....

}

Why you are using limit at all? Just run this query without limit and process your records one by one. — Your Common Sense, Mar 17 '14 at 08:27
you can't just run the query to pull in 34+ million rows it dies, besides it's not a PHP problem, I run the same query in mysql and it dies too. I put the code above but the query is problem with out PHP — user2760338, Mar 17 '14 at 09:04
If it is not a PHP related issue, why tag the question with php? — hjpotter92, Mar 17 '14 at 09:05
@hjpotter92 giving some context and there maybe other solutions — user2760338, Mar 17 '14 at 09:06
You should look here: http://stackoverflow.com/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down — piotrekkr, Mar 17 '14 at 09:13
When you use LIMIT OFFSET in MySQL, what it does is pull the entire data set it can find, then positions itself at the OFFSET discarding the data and returning LIMIT number of rows. Internally, what it does is load entire contents of your table. To get around that issue, you should specify values of primary keys, such as `SELECT * FROM table WHERE id > 1 AND id < 10000`; etc. — N.B., Mar 17 '14 at 09:13

score 0 · Answer 1 · answered Mar 17 '14 at 11:49

You can use the PK instead of OFFSET to get chunks of data:

$start = 0;
while(1) {
    $sql = "SELECT * FROM table WHERE id > $start ORDER BY id ASC LIMIT 10000";
    //get records...

    if(empty($rows)) break;

    foreach($rows as $row) {
        //do stuff...

        $start = $row['id'];
    }
}

Mysql query eating large amount of space using limit

1 Answers1