ST_Distance_Sphere计算<0.5英里短距离过慢，如何优化？

Great question! Short-distance spatial queries can feel sluggish if you’re not leveraging targeted optimizations—especially with large datasets. Let’s break down the most impactful fixes for your specific query:

1. Add a Spatial Index to the coords Column #

The biggest performance killer here is almost certainly a missing spatial index. Without one, ST_Distance_Sphere has to calculate the distance for every single row in your property table, which is incredibly inefficient.

Create a spatial index (for MySQL/MariaDB) like this:

CREATE SPATIAL INDEX idx_property_coords ON property(coords);

This lets the database quickly narrow down rows that fall within a rough area around your target point, instead of scanning the entire table.

2. Filter with a Bounding Box First #

Instead of calculating exact spherical distances for every row, first filter out all points definitely outside your 0.5-mile radius using a bounding box. This drastically cuts down the number of rows you need to run the expensive ST_Distance_Sphere calculation on.

Update your query to include this check in the WHERE clause (convert 0.5 miles to meters, since spatial functions typically use metric units for WGS84 coordinates):

WHERE 
  property.lasttransferdate >= CURRENT_DATE() - INTERVAL 10 year
  AND ST_Within(coords, ST_Buffer(Point(-1.3429, 54.5924), 0.5 * 1609.34))

ST_Buffer creates a circle around your point, and ST_Within filters rows where coords falls inside that circle—this is way faster than calculating exact distances for every row.

3. Replace String Concatenation with a Precomputed Indexed Column #

Your join condition Concat(property.paon, ', ', property.street) = epc.address1 is a major bottleneck. String concatenation happens at query time, so the database can’t use indexes to speed up this comparison.

Fix this by:

• Adding a computed column to property for the full address:

  ALTER TABLE property ADD COLUMN full_address VARCHAR(255) AS (CONCAT(paon, ', ', street)) STORED;
  

• Creating an index on this new column:

  CREATE INDEX idx_property_fulladdress ON property(full_address);
  

• Adding an index to epc.address1 too:

  CREATE INDEX idx_epc_address1 ON epc(address1);
  

Now your join can use these indexes, making that part of the query significantly faster.

4. Rewrite the Query to Avoid HAVING for Distance Filter #

HAVING filters rows after all calculations and joins are complete. Instead, move the distance check to the WHERE clause (or a subquery) to filter earlier in the process. Here’s a revised query incorporating all the above optimizations:

SELECT 
  p.paon, p.saon, p.street, p.propertytype, p.postcode, p.lastsaleprice, 
  DATE(p.lasttransferdate), 
  ST_Distance_Sphere(Point(-1.3429, 54.5924), p.coords) * 0.000621371192 AS distance 
FROM (
  -- First filter rows by date and bounding box
  SELECT * FROM property 
  WHERE 
    lasttransferdate >= CURRENT_DATE() - INTERVAL 10 year
    AND ST_Within(coords, ST_Buffer(Point(-1.3429, 54.5924), 0.5 * 1609.34))
) p
INNER JOIN epc 
  ON p.postcode = epc.postcode 
  AND p.full_address = epc.address1
-- Now filter by exact distance
WHERE ST_Distance_Sphere(Point(-1.3429, 54.5924), p.coords) * 0.000621371192 < 0.5
LIMIT 10;

5. Optional: Use Planar Distance for Short Ranges #

For very short distances (like 0.5 miles), the difference between spherical and planar distance is negligible. You can speed things up by projecting your coordinates to a local planar system (e.g., EPSG:27700 for the UK) and using ST_Distance instead of ST_Distance_Sphere:

SELECT 
  -- ... other columns ...
  ST_Distance(
    ST_Transform(Point(-1.3429, 54.5924), 27700),
    ST_Transform(p.coords, 27700)
  ) * 0.000621371192 AS distance
-- ... rest of query ...

This is faster because planar distance calculations are simpler than spherical ones—just ensure your coords column is stored in WGS84 (EPSG:4326) for the transformation to work.

内容的提问来源于stack exchange，提问作者Mike