Introduction

Finding nearby points in MySQL is a common requirement for store locators, delivery matching, and map-based search. The general pattern is to store coordinates in a spatial type, reduce the candidate set as early as possible, and only then compute exact distances for the remaining rows.

The important optimization is that “find the nearest points” should not mean “compute exact distance against every row” once the table is large. A spatial index and a candidate filter make the query much more practical.

Store Coordinates Properly

Use a POINT column and store longitude and latitude consistently. For geospatial latitude/longitude data, SRID 4326 is the usual choice.

1CREATE TABLE points (
2    id BIGINT PRIMARY KEY AUTO_INCREMENT,
3    name VARCHAR(120) NOT NULL,
4    category VARCHAR(40) NOT NULL,
5    location POINT SRID 4326 NOT NULL,
6    SPATIAL INDEX idx_location (location)
7);
8
9INSERT INTO points (name, category, location)
10VALUES
11    ('Cafe A', 'cafe', ST_SRID(POINT(-79.3832, 43.6532), 4326)),
12    ('Cafe B', 'cafe', ST_SRID(POINT(-79.4000, 43.6400), 4326)),
13    ('Cafe C', 'cafe', ST_SRID(POINT(-79.3700, 43.6700), 4326));

Remember that POINT(x, y) means longitude first, latitude second in this style of storage.

The Simple Distance Query

For a small table, the direct solution is acceptable.

1SET @lon = -79.38;
2SET @lat = 43.65;
3
4SELECT
5    id,
6    name,
7    ST_Distance_Sphere(location, ST_SRID(POINT(@lon, @lat), 4326)) / 1000 AS distance_km
8FROM points
9ORDER BY distance_km ASC
10LIMIT 10;

This computes spherical distance and returns the nearest rows. It is easy to read, but it can get expensive on large datasets because every row participates in the sort.

Reduce the Candidate Set First

A better pattern is to prefilter by an approximate bounding area and then compute exact distance on that smaller set.

1SET @lon = -79.38;
2SET @lat = 43.65;
3
4SELECT
5    p.id,
6    p.name,
7    ST_Distance_Sphere(p.location, ST_SRID(POINT(@lon, @lat), 4326)) / 1000 AS distance_km
8FROM points p
9WHERE MBRContains(
10    ST_Buffer(ST_SRID(POINT(@lon, @lat), 4326), 0.05),
11    p.location
12)
13ORDER BY distance_km ASC
14LIMIT 20;

The exact geometry of the candidate filter depends on your use case, but the principle stays the same: first limit the area, then do precise ranking.

Apply Business Filters Early

If you only want points of a certain category or availability state, include those filters before ordering by distance.

1SELECT
2    p.id,
3    p.name,
4    ST_Distance_Sphere(p.location, ST_SRID(POINT(@lon, @lat), 4326)) / 1000 AS distance_km
5FROM points p
6WHERE p.category = 'cafe'
7ORDER BY distance_km ASC
8LIMIT 20;

This reduces work and also makes the query match the actual business problem instead of doing unnecessary spatial ranking first.

Verify With EXPLAIN

As the dataset grows, inspect the execution plan.

1EXPLAIN FORMAT=TREE
2SELECT id, name
3FROM points
4ORDER BY ST_Distance_Sphere(location, ST_SRID(POINT(-79.38, 43.65), 4326))
5LIMIT 20;

A query that looks harmless on test data can degrade sharply once the table has millions of rows.

Data Quality Matters Too

Spatial accuracy depends on clean input. Common problems include:

• swapped latitude and longitude
• mixed SRIDs
• duplicate points from repeated imports
• unclear distance units in application output

A “bad nearest-neighbor query” is sometimes really a data-quality problem rather than a SQL problem.

Common Pitfalls

A common mistake is storing coordinates as plain numeric columns and then repeatedly reimplementing distance formulas by hand even though spatial types and functions are available.

Another issue is reversing latitude and longitude when constructing the POINT value. The query runs, but the results are nonsense.

Developers also often compute exact distance against every row in a large table and then wonder why the locator is slow. Candidate reduction matters.

Finally, be explicit about units. ST_Distance_Sphere returns meters, so divide or label accordingly before exposing values to application code.

Summary

• Store geospatial locations in a spatial POINT column with consistent SRID usage.
• Use exact spherical distance for ranking, but reduce candidates early on large tables.
• Apply business filters before final ordering when possible.
• Inspect execution plans as data volume grows.
• Validate coordinate order and units so correct SQL is not undermined by bad data.