Solution
Instead of using MySQL LIMIT you may use WHERE and BETWEEN which works pretty fast.
Here are sample queries generated by my script:
1st SELECT * FROM mytable ORDER BY id LIMIT 0, 10000 . . . 17th SELECT * FROM mytable ORDER BY id LIMIT 160000, 10000
Each queries (1st to 16th) took 3 seconds to complete including sub SELECTS and INSERTS. But when 17th query executes, MySQL hangs unlike the previous queries.
Here is my old PHP script that hangs on 17th query
<?php function process($startId = 0, $limit = 10000) { $sql = 'SELECT * FROM mytable ORDER BY id LIMIT ' . $startId . ', ' . $limit; $rs = mysql_query($sql); while($row = mysql_fetch_assoc($rs)) { // process records here } } $total = 30000000; // 30 million records $limit = 10000; // rows per batch for($a=0; $a < ($total/$limit); $a++) { $start = $a * $limit; process($start, $limit); }
Now, here is my new PHP script that executes SUPER fast.
<?php function process($minId = 0, $maxId = 10000) { $sql = 'SELECT * FROM bids WHERE id between ' . $minId . ' AND ' . $maxId; $rs = mysql_query($sql); while($row = mysql_fetch_assoc($rs)) { // process records here } } $total = 30000000; $limit = 10000; for($a=0; $a < ($total/$limit); $a++) { $minId = (($a * $limit)+1); $maxId = (($limit*$a)+$limit); process($minId, $maxId); }
No comments:
Post a Comment