Why does the PHP version run faster than MySQL

I have two very large tables to merge and so I have been trying to optomize the update for speed. I noticed that doing the update partially in PHP speeded it up significantly so I assumed this means I'm not be doing the MySQL properly.

I have simplified the problem to try and narrow it down ...

GRID_TABLE                                  POSTCODE_TABLE

idNo, lat,  lng,  nearestPostcode           postcode,  lat,   lng

________________________________            _____________________

1     57.1  -2.3  -                         AB12 3BA   56.3  -2.5

2     56.8  -1.9  -                         AB12 1YA   56.2  -2.3

. . .                                       . . .



(200 entries)                               (35,000 entries)

I want to update the GRID_TABLE with the nearestPostcode from the POSTCODE_TABLE using latitude (lat) and longitude (lng) to find the nearest postcode to each grid point...

update grid_table set nearestPostcode = (

    select postcode from postcode_table 

    where lat > grid_table.lat -0.0037 and lat < grid_table.lat +0.0037 

        and lng > grid_table.lng -0.0068 and lng < grid_table.lng +0.0068

    order by POW(lat - grid_table.lat,2) + POW((lng - grid_table.lng) *0.546,2) 

    limit 1 

    )

The idea is that the 'where' clause speeds up the search by using indexes to narrow the set down to a few candidates and then the 'order by' clause finds the nearest one within this set.

This MySQL update takes 30 secs, but if I instead update each GRID_TABLE row individually in PHP it's over in the blink of an eye.

$queryStg = "select * from grid_table ;";

$sqlQuery1 = mysqli_query($mysqliLink, $queryStg);



while( $sqlRow = mysqli_fetch_assoc( $sqlQuery1 ) ) {



    $idNo = $sqlRow['idNo'];

    $lat = $sqlRow['lat'];

    $lng = $sqlRow['lng'];



    $queryStg = "

        update grid_table

            set nearestPostcode = (

                SELECT postcode

                FROM postcode_table

                where

                    lat > " . ($lat - 0.0037) . " and

                    lat < " . ($lat + 0.0037) . " and

                    lng > " . ($lng - 0.0068) . " and

                    lng < " . ($lng + 0.0068) . "

                ORDER BY

                    POW(lat - $lat, 2) +

                    POW((lng - $lng) * 0.546, 2)

                    ASC

                limit 1

                )

            where idNo= $idNo;

        ";



    $sqlQuery2 = mysqli_query($mysqliLink, $queryStg);



}

Surely the MySQL version should be faster than the PHP version?

Here is the MySQL for the tables...

CREATE TABLE `grid_table` (

    `idNo` INT(11) NOT NULL AUTO_INCREMENT,

    `lat` FLOAT(6,4) NOT NULL COMMENT 'latitude',

    `lng` FLOAT(6,4) NOT NULL COMMENT 'longitude',

    `nearestPostcode` CHAR(8) NOT NULL,

    PRIMARY KEY (`idNo`),

    INDEX `lat_lng` (`lat`, `lng`)

)

ENGINE=MyISAM

ROW_FORMAT=DEFAULT

AUTO_INCREMENT=30047

CREATE TABLE `postcode_table` (

    `postcode` CHAR(8) NOT NULL,

    `lat` FLOAT(6,4) NOT NULL COMMENT 'latitude',

    `lng` FLOAT(6,4) NOT NULL COMMENT 'longitude',

    PRIMARY KEY (`postcode`),

    INDEX `lat` (`lat`),

    INDEX `lng` (`lng`),

    INDEX `lat_lng` (`lat`, `lng`)

)

ENGINE=MyISAM

ROW_FORMAT=DEFAULT

MySQL import file is here... https://docs.google.com/leaf?id=0B93lksnTC7_cM2Y2ZDk1Y2YtMGQ3Yy00OTIxLTk0ZDAtZmE2NmQ3YTc1ZWRm&hl=en

(if you run the UPDATE, 10 nearestPostcodes will be added).

UPDATE AFTER ANSWERS...

I ran this...

explain extended

 SELECT postcode FROM postcode_table 

 where lat > 57.0 and lat < 57.0074

 and lng > -2.013 and lng < -2

 ORDER BY POW(lat - 57.0, 2) + POW((lng - -2) * 0.546, 2) ASC

It returned...

id,select_type,table,type,possible_keys,key,key_len,ref,rows,filtered,Extra

1,SIMPLE,postcode_table,range,lat,lng,lat_lng,lat_lng,8,NULL,65,100.00,Using where; Using filesort

Removing the 'order by' caluse -> no difference in speed.

Simplifying the 'where' clause by removing 'lng', ie

where lat between grid_table.lat - 0.0037 and grid_table.lat + 0.0037

-> faster: 3 secs rather than 30 secs.

Using spatial column and index (see below) -> much slower (190 sec). Not sure if I implemented this correctly though.

ALTER TABLE `grid_table` ADD COLUMN `coords` POINT NOT NULL;

update grid_table set coords = POINT(lat, lng);

ALTER TABLE `grid_table` ADD SPATIAL INDEX `coords` (`coords`);



ALTER TABLE `postcode_table` ADD COLUMN `coords` POINT NOT NULL;

update postcode_table set coords = POINT(lat, lng);

ALTER TABLE `postcode_table` ADD SPATIAL INDEX `coords` (`coords`);



analyze table grid_table;

optimize table grid_table;

analyze table postcode_table;

optimize table postcode_table;

update grid_table set nearestPostcode = (

    select postcode from postcode_table 

    WHERE MBRContains(GeomFromText(concat(

         'POLYGON((', 

          grid_table.lat - 0.0037, ' ', grid_table.lng - 0.0068, ', ',

          grid_table.lat - 0.0037, ' ', grid_table.lng + 0.0068, ', ',

          grid_table.lat + 0.0037, ' ', grid_table.lng - 0.0068, ', ',

          grid_table.lat - 0.0037, ' ', grid_table.lng - 0.0068, 

          '))')), postcode_table.coords)

     order by POW(lat - grid_table.lat,2) + POW((lng - grid_table.lng) *0.546,2)

     limit 1 

     )

Source: Tips4all, CCNA FINAL EXAM