Query in a while loop, how can I improve the speed of this? Two DB Types

edit Thought i need to add more info. This first query is MySQL, the latter two are PGSQL, and its located on a seperate server. When i disable the second query in the second part of this, its still just as slow.

SELECT n.id, n.number, n开发者_运维问答.assigned, n.btn, n.status as stat, s.status 
FROM numbers n 
LEFT JOIN status s on n.status=s.id 
where number like '239333%'
LIMIT $start, $limit

I have the following query, this pulls a range of numbers from a DB, this is a pretty quick query, as I have it limited to 50 or 100.

This lists the output in a while loop in PHP..

Within this while loop, there is a second connection to a seperate server (postgres) to do additional checks on this number which is done through a function, so in the loop i have for example $mq = addition_checks($number);

$query = "SELECT count(baseinformation_directorynumber) as count FROM \"public\".\"meta_subscriber\" WHERE baseinformation_directorynumber = $number";
$result = pg_query($query) or die('Query failed: ' . pg_last_error());
$row = pg_fetch_assoc($result);
$did = "0";
if ($row['count'] == '0') {
    $query2 = "SELECT count(lastdirectorynumber) as count FROM \"public\".\"meta_pbx_directinwardcalling\" where firstdirectorynumber <= $number and $number <= lastdirectorynumber";
    $result2 = pg_query($query2) or die('Query failed: ' . pg_last_error());
    $row2 = pg_fetch_assoc($result2);
    if ($row2['count'] == '1') { 
        $did = $row2['count'];
        $row['count'] = "0";
    }

}

pg_free_result($result);

// Closing connection

$line = $row['count'];
$match = array('line' => $line, 'did' =>  $did);
return $match;

After this I do a couple more checks to display the information as needed.

The problem is that when I added the additional_checks query, it takes about 30 seconds to load 50 items. Im asking (due to lack of knowledge) how I can increase the speed of this to be a little more acceptable.

If it helps, here it my main while look

while ($q->getrow()) {
$number = $q->field('number'); $assigned = $q->field('assigned'); $btn = $q->field('btn'); $id = $q->field('id'); $status = $q->field('status'); $stat = $q->field('stat');
$nc++; //Start Counter
$now = time(); 
$diff = $now - $time; 
if ($alternate == "1") {
    $color = "#EFEFEF";
    $alternate = "2";
} else {
    $color = "#DFDFDF";
    $alternate = "1";
}
$match = meta_query($q->field('number'));
if ($match['line'] == '1' and $match['did'] == '0') { 
    $numcolor = "green";
    $did = "";
} elseif ($match['did'] == '1' AND $match['line'] == '0') { 
    $did = "[DID]"; 
    $numcolor = "green";
} else {
    $did = "";
    if ((!$assigned) AND (!$btn) AND ($stat == '1' OR $stat == '4')) {
        $numcolor = "";
    } else {
        $numcolor = "red";
    }

}
//if ($numcolor = "red" AND $assigned == '' AND $btn == '' AND $stat == '1') {
//  $numcolor = "";
//}
//print_r($match);
if ($stat == '4') { $color = "C2FACB"; }
elseif ($stat == '2') { $color="FCFBEA"; }
// else {
$ajaxedits .= "EditInPlace.makeEditable({ id: 'btn:$id' });\n";
$ajaxedits .= "EditInPlace.makeEditable({ id: 'assigned:$id' });\n";
$ajaxedits .= "EditInPlace.makeEditable({
    id: 'status:$id',
    type: 'select',
    save_url: 'optionedit_eip.php',
    options: {
        1: 'Free',
        2: 'In use',
        3: 'Disconnected',
        4: 'Ported Out'
    }
});";
//}
//<span class="red" id="status:$id">Dark Green</span>
//
if (isset($right[admin])) {
     $clear = "<a href=\"index.php?a=clearrow&id=$id\"> (Clear)</a>";
}   
eval("\$list_numbers .= \"".addslashes($TI[5])."\";");
}
$q->free;

the following

$match = meta_query($q->field('number'));

is the function where it calls to do the additional postgres checks which is listed above. Yes my code is ugly, im still a noob. :)

---

SQL queries within loops can be avoided. One way is to collect all $numbers in a php array and then run a single query after the loop.

..WHERE baseinformation_directorynumber IN ($num1, $num2, ...);

You'll get counts of all numbers in a single go. This will speed up the execution.

I've found a blog post containing more ways to avoid sql in loops. check article

---

Look into using a JOIN on the two tables. Then you can compare the speeds, but depending on the number of rows that you have, a JOIN should be quicker than two queries.