Finding People Near You with MySQL 8.4

A nearby search is a radius query: given a center and a distance, return every stored position whose geographic distance from the center does not exceed that radius. Calculating that distance for every row is simple, but it turns each request into a full table scan. MySQL 8.4 can reduce the work with an SRID-restricted spatial index and still apply an exact final distance test.

Store a geographic point

Latitude and longitude belong together. Keeping them in one geographic POINT prevents the values from drifting apart and gives the optimizer a geometry it can index:

CREATE TABLE profile (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    display_name VARCHAR(100) NOT NULL,
    location POINT NOT NULL SRID 4326,
    location_updated_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    SPATIAL INDEX idx_profile_location (location)
) ENGINE = InnoDB;

SRID 4326 identifies WGS 84, the coordinate system used by GPS. The explicit restriction is not decorative: MySQL's optimizer only considers a spatial index when every indexed value is constrained to the same spatial reference system. Spatial columns in such an index must also be NOT NULL.

Make coordinate order explicit

Most application APIs write a position as longitude followed by latitude, while EPSG:4326 formally defines its axes in latitude-longitude order. MySQL follows the SRS definition unless instructed otherwise. The axis-order=long-lat option makes each WKT string unambiguous:

INSERT INTO profile (
    display_name,
    location,
    location_updated_at
) VALUES (
    'Ada',
    ST_GeomFromText(
        'POINT(13.4050 52.5200)',
        4326,
        'axis-order=long-lat'
    ),
    UTC_TIMESTAMP()
);

The text therefore remains POINT(longitude latitude), while the resulting geometry carries SRID 4326. Mixing conventions is one of the easiest ways to store valid-looking points on the wrong continent. Validate latitude in [-90, 90] and longitude in (-180, 180] before constructing the geometry.

Why distance alone does not scale

ST_Distance_Sphere() returns the spherical distance between two geographic points in meters. For WGS 84, MySQL uses the SRS mean radius (2a + b) / 3 unless a positive radius is supplied explicitly:

SET @origin = ST_GeomFromText(
    'POINT(13.4050 52.5200)',
    4326,
    'axis-order=long-lat'
);
SET @radius_m = 15000;

SELECT
    id,
    display_name,
    ST_Distance_Sphere(location, @origin) AS distance_m
FROM profile
WHERE ST_Distance_Sphere(location, @origin) <= @radius_m
ORDER BY distance_m;

This query is correct for the spherical model, but the function must still be evaluated for every profile. A spatial index does not turn an arbitrary distance expression into an index range. The useful optimization is to reject most rows with an indexable rectangle first, then calculate distance only for the survivors.

Rectangle first, circle second

A circle fits inside its minimum bounding rectangle. The R-tree can use that rectangle for a cheap candidate lookup without discarding a true result. Points near the rectangle's corners are false positives; the spherical distance predicate removes them in the second step. A cached polygon with a small number of sides is unnecessary and can even omit valid points between its corners.

Let R be Earth's mean radius, r the search radius, and phi the center latitude, all angles in radians. The angular radius is delta = r / R. The bounding box extends by delta in latitude and by asin(sin(delta) / cos(phi)) in longitude. The latter is wider away from the equator because meridians move closer together.

SET @latitude = 52.5200;
SET @longitude = 13.4050;
SET @radius_m = 15000;
SET @earth_radius_m = 6371008.77141506;

SET @angular_radius = @radius_m / @earth_radius_m;
SET @latitude_delta = DEGREES(@angular_radius);
SET @longitude_delta = DEGREES(
    ASIN(
        SIN(@angular_radius) /
        COS(RADIANS(@latitude))
    )
);

SET @min_latitude = @latitude - @latitude_delta;
SET @max_latitude = @latitude + @latitude_delta;
SET @min_longitude = @longitude - @longitude_delta;
SET @max_longitude = @longitude + @longitude_delta;

At Berlin's latitude, a 15 km radius produces approximately 0.135 degrees of latitude and 0.222 degrees of longitude in each direction.

Build the search geometry

The four limits form a polygon whose minimum bounding rectangle is the index key. Construct the origin and rectangle with the same SRID and explicit axis order as the stored data:

SET @origin = ST_GeomFromText(
    CONCAT(
        'POINT(',
        @longitude, ' ', @latitude,
        ')'
    ),
    4326,
    'axis-order=long-lat'
);

SET @search_box = ST_GeomFromText(
    CONCAT(
        'POLYGON((',
        @min_longitude, ' ', @min_latitude, ',',
        @max_longitude, ' ', @min_latitude, ',',
        @max_longitude, ' ', @max_latitude, ',',
        @min_longitude, ' ', @max_latitude, ',',
        @min_longitude, ' ', @min_latitude,
        '))'
    ),
    4326,
    'axis-order=long-lat'
);

ST_MakeEnvelope() may look shorter, but MySQL calculates that function in a Cartesian coordinate system, gives its result SRID 0, and rejects geographic inputs. A WGS 84 polygon keeps both arguments in SRID 4326.

Run the indexed radius query

SELECT
    p.id,
    p.display_name,
    ST_Distance_Sphere(p.location, @origin) AS distance_m
FROM profile AS p
WHERE MBRIntersects(@search_box, p.location)
HAVING distance_m <= @radius_m
ORDER BY distance_m
LIMIT 100;

MBRIntersects() performs the coarse candidate lookup. A point intersects the rectangle exactly when it lies inside or on its boundary. MBRContains() excludes that boundary and could therefore lose a result at precisely the requested radius. MBRCovers() is inclusive, but MySQL 8.4 does not turn that predicate into a spatial range scan in this argument arrangement. MBRIntersects() provides the required inclusive semantics and uses the R-tree. HAVING then filters the computed distance_m alias, so the expensive function appears only once in the statement. The limit is applied after distance ordering and does not alter which profiles qualify.

Check the actual plan rather than assuming the index was selected:

EXPLAIN ANALYZE
SELECT
    p.id,
    ST_Distance_Sphere(p.location, @origin) AS distance_m
FROM profile AS p
WHERE MBRIntersects(@search_box, p.location)
HAVING distance_m <= @radius_m;

For a selective rectangle and a populated table, the plan should show spatial range access through idx_profile_location. The optimizer may still prefer a table scan when most rows fall inside the box or the table is tiny. Index use is a cost decision, not a semantic requirement. Geographic R-tree optimization requires MySQL 8.4; earlier releases treated indexed bounding boxes as Cartesian even for geographic SRIDs.

Handle poles and the antimeridian

The single-box construction is intended for local searches whose circle neither reaches a pole nor crosses longitude +/-180 degrees. Near a pole, the longitude range becomes the full 360 degrees. Across the antimeridian, a range such as 179.8 to 180.2 degrees must be normalized and split into two boxes, one on each side of the date line. Run both indexed candidate queries with UNION ALL, deduplicate by profile identifier, and apply the same final distance test.

Do not clamp an overflowing longitude range into [-180, 180]; that silently removes valid candidates. Applications serving global traffic should implement the split explicitly and test centers on both sides of the date line.

Accuracy, updates, and privacy

ST_Distance_Sphere() models Earth as a sphere. Its result is appropriate for discovery radii, nearby venues, and social proximity, but it is not a surveying measurement on the WGS 84 ellipsoid. Applications requiring centimeter-scale or legally significant distances need an appropriate geodesic implementation or projected coordinate system.

Update the indexed point when a person's location changes; do not cache every pairwise distance. A pair table grows quadratically with the number of people and becomes stale after either endpoint moves. The R-tree remains linear in the number of stored positions and lets each request choose its own center and radius.

Precise location is sensitive personal data. Store it only with informed consent, retain it for no longer than the product needs, restrict access independently of profile visibility, and consider reducing precision before storage. Never return a person's exact coordinates merely because they passed a nearby search; a rounded distance band is often sufficient.

References

• [MySQLDistance]Oracle, MySQL 8.4 Reference Manual: Spatial Convenience Functions.
• [MySQLIndex]Oracle, MySQL 8.4 Reference Manual: SPATIAL Index Optimization.
• [MySQLMBR]Oracle, MySQL 8.4 Reference Manual: Spatial Relation Functions That Use Minimum Bounding Rectangles.
• [MySQLWKT]Oracle, MySQL 8.4 Reference Manual: Functions That Create Geometry Values from WKT Values.