Почему версия PHP работает быстрее, чем MySQL

У меня есть две очень большие таблицы, которые нужно объединить, и поэтому я пытался оптимизировать обновление для скорости. Я заметил, что выполнение обновления частично на PHP значительно ускорило его так что я предположил, что это означает, что я не выполняю MySQL должным образом.

Я упростил задачу, чтобы попытаться сузить ее ...

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)

Я хочу обновить GRID_TABLE, указав ближайшийPostcode из POSTCODE_TABLE, используя широту (широта) и долгота (lng), чтобы найти ближайший почтовый индекс к каждой точке сетки ...

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 
    )

Идея состоит в том, что предложение 'where' ускоряет поиск за счет использования индексов для сужения набора до нескольких кандидатов, а затем предложение 'order by' находит ближайшую в этом наборе.

Это обновление MySQL занимает 30 секунд, но если я вместо этого обновляю каждую строку GRID_TABLE индивидуально в PHP, оно завершается в мгновение ока.

$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);

}

Разумеется, версия MySQL должна быть быстрее, чем версия PHP?

Вот MySQL для таблиц ...

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 находится здесь ... https://docs.google.com/leaf?id=0B93lksnTC7_cM2Y2ZDk1Y2YtMGQ3Yy00OTIxLTk0ZDAtZmE2NmQ3YTc1ZWRm&hl=en

(если вы запустите 12128 ANPDATE 12 [AFWTER], будет добавлено 12128 UPDATE, 10 будут добавлены следующие UPDATE 12128, будет добавлено 12128 UPDATE, 10, 10 выполнил это ...

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 

Он вернул ...

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

Удаление 'order by' caluse -> нет разницы в скорости.

Упрощение предложения 'where' путем удаления 'lng', то есть

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

- > быстрее: 3 секунды вместо 30 секунд.

Использование пространственного столбца и индекса (см. Ниже) -> намного медленнее (190 секунд). Не уверен, что я реализовал это правильно.

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 
     )