next »

[kurdt]

Here's interesting behavior from MySQL.

Here's my script:

Code:

$count = exe_sql(DATABASE,"SELECT count(id) FROM sites");
$cycle = 10000;
for($i=0;$i<$count[0];$i++) {
$start = time();
echo $i."/".$count[0]."\n";
$urls = exe_sql(DATABASE,"SELECT id,url FROM sites ORDER by id LIMIT ".$i.",".$cycle);
foreach($urls as $url) {
$valid_url = valid_url($url['url']);
if(!$valid_url) {
echo $url['id']." - ".$url['url']." is not valid.\n";
exe_sql(DATABASE,"DELETE FROM sites WHERE id = ".$url['id']);
}
}
$i = $i+$cycle;
$end = time();
$timetook[] = $end-$start;
foreach($timetook as $took) {
$ave += $took;
}
echo "average time: ".$ave/count($timetook)." - ".count($urls)."\n";
}

This is a script that checks that url data is valid in certain table. Now here's the output:

Code:

0/8994994
average time: 0 - 10000
10001/8994994
average time: 0 - 10000
20002/8994994
average time: 0 - 10000
30003/8994994
average time: 0.25 - 10000
40004/8994994
average time: 0.4 - 10000
50005/8994994
average time: 0.666666666667 - 10000
60006/8994994
average time: 0.857142857143 - 10000
70007/8994994
average time: 1.125 - 10000
80008/8994994
average time: 1.44444444444 - 10000
90009/8994994
average time: 1.7 - 10000
100010/8994994
average time: 2 - 10000
110011/8994994
average time: 2.33333333333 - 10000
120012/8994994
average time: 2.69230769231 - 10000
130013/8994994
average time: 3.07142857143 - 10000
140014/8994994
average time: 3.46666666667 - 10000
150015/8994994
average time: 3.875 - 10000
160016/8994994
average time: 4.29411764706 - 10000
170017/8994994
average time: 4.72222222222 - 10000
180018/8994994
average time: 5.21052631579 - 10000
190019/8994994
average time: 5.7 - 10000
200020/8994994
average time: 6.2380952381 - 10000
210021/8994994
average time: 6.77272727273 - 10000
220022/8994994
average time: 7.34782608696 - 10000
230023/8994994
average time: 7.91666666667 - 10000
240024/8994994
...ctrl+c

Any ideas why it's slowing down like that?

[perkiset]

I believe that there are processes that will run in the background to clean up the tables you've deleted from, even though the initial command is complete. As you push on it in rapid fire, you'd be binding up the processor more and more. I'd wager if you put a small sleep between them you'd do better.

You'd also probably do better if you through the whole thing up as a multiquery, because it'd be done really in sequence I think - given that the entire thing would be prepared as a query. I THINK.