为什么PHP版本比MySQL运行得更快？
我有两个非常大的表进行合并，所以我一直在尝试对速度更新进行optomize。 我注意到在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)
我想使用latitude（lat）和longitude（lng）从POSTCODE_TABLE中的nearestPostcode更新GRID_TABLE，以找到每个网格点的最近邮编...
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秒，但是如果我改为在PHP中单独更新每个GRID_TABLE行，它就会在瞬间完成。
$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
（如果您运行UPDATE，则会添加10个nearestPostcodes）。
在答案后更新...
我跑这个...
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
删除'按'caluse命令 - >没有速度差异。
通过删除'lng'来简化'where'子句，即
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
)
在您的MySQL版本中，您的子查询可以与所有30000个grid_table记录一起使用，无论是在您的PHP版本中 - 它只是一个。 当你添加外表PK的位置时。
我建议你在这里更改更新查询。 例如，尝试使其无子查询，多更新如http://dev.mysql.com/doc/refman/5.0/en/update.html。
我相信它应该有所帮助。
就像是：
update grid_table, postcode_table
set grid_table.nearestPostcode = postcode_table.postcode
where postcode_table.lat > grid_table.lat - 0.0037
and postcode_table.lat < grid_table.lat + 0.0037
and postcode_table.lng > grid_table.lng - 0.0068
and lng < grid_table.lng + 0.0068
group by grid_table.idNo
having (POW(lat - grid_table.lat,2) + POW((lng - grid_table.lng) *0.546,2)) = min(POW(lat - grid_table.lat,2) + POW((lng - grid_table.lng) *0.546,2))
可能是这个版本可以帮助，但我不确定。 我假设你的第一个版本的主要问题是对所有记录的子查询。
要解释更新 ，你可以“转换”为类似的选择：
explain
select
*,
(
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
) nearestPostcode
from grid_table
你会看到：
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY grid_table ALL 224
2 DEPENDENT SUBQUERY postcode_table ALL lat,lng,lat_lng 35605 Using where; Using temporary; Using filesort
但是如果是id，我们有：
explain
select
*,
(
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
) nearestPostcode
from grid_table
where idNo = 1487;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY grid_table const PRIMARY PRIMARY 4 const 1
2 DEPENDENT SUBQUERY postcode_table range lat,lng,lat_lng lat 4 18 Using where; Using filesort
所以我们有35605行vs〜18 * 224（〜4000）。
要找到正确的查询，请尝试找到好的选择1st。
更新
子查询不在这里:(所以我认为我们应该尝试一些预先计算的+索引列可能是目标是为了避免order by SOMEFUNC()
查看执行计划以了解正在花费那么长时间。 http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html
我的猜测是，这种差异是由于您在逐行查询中提供了$lat的值，从而为此查找节省了大量扫描： -
order by POW(lat - grid_table.lat,2)
像Mr47说的那样，通过解释SQL语句，你将能够看到。
链接地址: http://www.djcxy.com/p/52755.html