Apply PHP compare function to all elements in MySQL table

Question

I'm building an algorithm that takes an euclidean coordinate—for example (-4, 2)—and searches through a large database table of other coordinates to find the closest coordinates (using euclidean distance formula). Currently, my code looks like this:

$x = -4;
$y = 2;
$query = mysql_query('SELECT `x`, `y` FROM `myTable`');
$closest = PHP_INT_MAX;
while($row=mysql_fetch_array($query)){
    $dist = distFormula($x, $y, $row[x], $row[y]);
    if($dist<$closest)
        $closest = $dist;
}
echo $closest;

distFormula($x1, $y1, $x2, $y2){
    return sqrt(pow($x2-$x1, 2)+pow($y2-$y1, 2));
}

Is there a more efficient way of doing this? My database table may be extremely large. Is there some way to do the comparison inside the query? Also, for the purpose of example, I used two-dimensions in this case, however in reality I intend to use high numbers of dimensions (longer coordinates).

Thank you for your help!

Answer 1

Yes you can make such calculations in MySQL within the query. Try this:

$query = <<<SQL
  SELECT * FROM (SELECT $x originX, $y originY, x closestX, y closestY, SQRT(
       ($x-x)*($x-x)
     + ($y-y)*($y-y)
  ) dist FROM `myTable`) a ORDER BY dist DESC LIMIT 1
SQL;

Make sure the last line is by itself, no other characters at the start or end.

It should give the distance within the 'dist' column in the results

Here's an example with 3 dimensions...

$query = <<<SQL
  SELECT * FROM (
      SELECT 
         $x originX, $y originY, $z originZ, 
         x closestX, y closestY, z closestZ 
         SQRT(
           ($x-x)*($x-x)
         + ($y-y)*($y-y)
         + ($z-z)*($z-z)
  ) dist FROM `myTable`) a ORDER BY dist DESC LIMIT 1
SQL;