MySQL LIMIT Optimization
I have this scenario where MySQL LIMIT syntax is very slow for table having 30 million records.
Solution
Instead of using MySQL LIMIT you may use WHERE and BETWEEN which works pretty fast.
Here are sample queries generated by my script:
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
Now, here is my new PHP script that executes SUPER fast.
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); }
Comments