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
)
In your MySQL version your subquery works with all 30000 grid_table records, whether in your PHP version -- it's only one. As you add where on outer table PK.
I suggest you here to change update query. For example, try to make it without subquery, multiple-update as here http://dev.mysql.com/doc/refman/5.0/en/update.html.
I believe it should help.
Something like:
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))
May be this version could help, but I`m not sure. I assume, the main root problem in your 1st version is subquery over all records.
To have explain update , you can "convert" it to similar select:
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
And you will see:
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
But in case of idNo we have:
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
So we have 35605 rows vs ~18*224 (~4000).
To find correct query try to find good select 1st.
Update
Subquery isn't a root here :( So I think we should try some precalculated + indexed column may be. Target is to avoid order by SOMEFUNC()
Look at the execution plan to know what is taking so long. http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html
My guess is that the difference is due to your providing the value of $lat with in the row-by-row query, thereby saving large scans for the lookup here:-
order by POW(lat - grid_table.lat,2)
Like Mr47 says, you'll be able to see by EXPLAIN-ing the SQL statements.
链接地址: http://www.djcxy.com/p/52756.html上一篇: 点击发布不工作从VS2010
下一篇: 为什么PHP版本比MySQL运行得更快？