Selecting 200k rows from MySQL, Performance issue

Question

I'm just ruin into this issue, I need to select good amount of rows, to perform certain operation on that, of course I don't need them at once, I can afford a delay of 1-2 seconds in between, I approached PHP loop by following below method.

$last_id=0;
for($i=1;$i<20;$i++)
{
Mysql_query...... WHERE id>last_id;

$last_id=$TablerowID;
}

I'm having 1 million rows in this table, but I dont feel its a good approach mysql is not returning data in proper id incremented form, it also sometimes return 1 & leaving 50 jump to 100 then after 100 it return 50 etc which will skip some rows or create other issues in this function.

I don't want order by id as its also decreasing performance a lot & I can't compromise on performance.

Please your suggestions ?

You want them in order but don't want to order them? MySQL makes no promises on the order if you give no ORDER clause. See http://stackoverflow.com/questions/1793147/sql-best-practice-to-deal-with-default-sort-order — Andy Jones, Jul 21 '13 at 03:22
What is the context of the data you are trying to prepare / present. Andy is right about the order. Maybe you can get the results in a different way, such as date ranges that would return smaller amounts in spurts as you are looking... ex: monthly, quarterly, then it doesn't matter what the "ID" order is — DRapp, Jul 21 '13 at 03:25
But the demand will grow either from time-to-time that's why I want good solution to the problem, ordering is performance killer, how can I combine PHP & mysql to override this issue, it returns only two int type fields, one is row id (PRIMARY) & user id in another table. — user1089705, Jul 21 '13 at 03:32
In order to let us try to help you you need to provide much more information and as specific and precise as possible: table schema as a result of `DESC your_table`, you real query, result of `EXPLAIN your query`. What is the nature of your operation? Maybe it can be solved on MySql side... Hitting database continuously with your select statements is also a performance killer — peterm, Jul 21 '13 at 03:33
you want mysql to imagine that you want order by and do it for you? just do it and forget the performance issues. pefrorming for loops over thousands of rows in php is already destroying your performance. — Aris, Jul 21 '13 at 05:18

score 1 · Answer 1 · answered Jul 21 '13 at 05:26

order by is executed last. So if you retrieve junks of say 100 rows each time, the performance ovehead will be minimal.

You can adjust your query to something like this:

$last_id=0;
for($i=1;$i<20;$i++)
{
Mysql_query...... WHERE id>last_id and id<(last_id+junk_size) order by id;

$last_id=$TablerowID;
}

By changing the junk_size you can find empirically which is the most optimal.

Selecting 200k rows from MySQL, Performance issue

1 Answers1