[RESOLVED] Optimized query?
I was having problem quering 2 tables, like:
Code:
$query = "SELECT siteid,url FROM sites join user on sites.userid=user.userid WHERE user.frozen=0 && userid!=".$_SESSION['userid'];
But then I solved it this way breaking it on 2 queries and generating second with PHP
Code:
$query = "SELECT userid FROM user WHERE frozen=1";
//First we found all frozen accounts....
$result = mysqli_query($link, $query);
while ($data = mysqli_fetch_row($result))
{
$n .= $data[0] . ',' ;
}
$n = substr($n,0,strlen($n)-1);
$bad = explode( ',' , $n );
@mysqli_free_result($result);
$query = "SELECT siteid,url FROM sites WHERE";
foreach ($bad as $qu)
{
$query .= " userid!='$qu' &&";
}
$query .= " userid!=".$_SESSION['userid']." ORDER by rand() LIMIT 1";
$result = mysqli_query($link, $query);
In effect this will produce (ie: found 5 frozen accounts...):
Code:
$query = "SELECT siteid,url FROM sites WHERE userid!='5001' && userid!='5002' && userid!='5003' && userid!='5004' && userid!='5005' && userid!=".$_SESSION['userid']." ORDER by rand() LIMIT 1";
...and it works.
I'm just asking myself is it optimal solution because php pass one query to MySQL, which might be long if there are many frozen accounts.
So to end this... change it or leave it as is?
