1

I have a query on the following schema, on two separate databases, one on MySQL 5.7 and one on 8.0:

CREATE TABLE places
(
    id          bigint AUTO_INCREMENT
        PRIMARY KEY,
    position    point    NOT NULL,  -- This has SRID 4326 on 8.0
    wikidata_id char(50) NOT NULL,
    CONSTRAINT places_wikidata_id_uindex
        UNIQUE (wikidata_id)
);

CREATE SPATIAL INDEX position
    ON places (position);


CREATE TABLE place_names
(
    id             bigint AUTO_INCREMENT
        PRIMARY KEY,
    place_id       bigint    NOT NULL,
    name           char(255) NOT NULL,
    administration char(255) NULL,
    admin_abbr     char(50)  NULL,
    country        char(255) NOT NULL,
    language       char(3)   NOT NULL,
    CONSTRAINT place_names_places_id_fk
        FOREIGN KEY (place_id) REFERENCES places (id)
);

CREATE INDEX place_id
    ON place_names (place_id);

CREATE INDEX place_names_language_index
    ON place_names (language);

CREATE INDEX place_names_name_language_index
    ON place_names (name, language);

Here are a couple example records:

INSERT INTO places (id, position, wikidata_id)
VALUES  (1, 0xE610000001010000005C8FC2F5287C52C0F6285C8FC2554440, 'Q60'),
        (2, 0xE61000000101000000B2D424BF584253C0C3F5285C8F724340, 'Q61'),
        (3, 0xE610000001010000002E18941DA69A5EC0EC51B81E85E34240, 'Q62'),
        (4, 0xE610000001010000004267414444C42A40A1B3202222424A40, 'Q64'),
        (5, 0xE610000001010000001DACFF73988F5DC05FEFFE78AF064140, 'Q65'),
        (6, 0xE6100000010100000043FF04172BCA1D408CA19C6857794740, 'Q70'),
        (7, 0xE610000001010000009A999999999918409A99999999194740, 'Q71'),
        (8, 0xE6100000010100000014AE47E17A142140D8D2315476B04740, 'Q72'),
        (9, 0xE61000000101000000D6C86C98BA5C1E407728D048C0C74740, 'Q78'),
        (10, 0xE6100000010100000052B81E85EB51C0BFB1A563A8ECC04940, 'Q84'),
        (11, 0xE610000001010000005C30283B4C3D3F40EEAF3A4C5D0E3E40, 'Q85'),
        (12, 0xE61000000101000000A9CDA7AAAAEA3D403333333333333F40, 'Q87'),
        (13, 0xE610000001010000000000000000C451C09D26DDDDDD2D4540, 'Q100'),
        (14, 0xE61000000101000000183B8B25BFD853C0ADB985A9CBD54540, 'Q172'),
        (15, 0xE61000000101000000081A87888848394037C0767777574B40, 'Q216'),
        (16, 0xE61000000101000000B3E616A62EF72840391EF9A44FF24440, 'Q220'),
        (17, 0xE610000001010000009D26DDDDDDDD49C033333333330B5040, 'Q226'),
        (18, 0xE61000000101000000E040764E1B681140AB8AC4925F6C4940, 'Q239'),
        (19, 0xE610000001010000006666666666661140CDCCCCCCCC6C4940, 'Q240'),
        (20, 0xE61000000101000000A9CDA7AAAAEA30403333333333334A40, 'Q268');

INSERT INTO place_names (id, place_id, name, administration, admin_abbr, country, language)
VALUES  (2162656, 1, 'New York City', 'New York', 'NY', 'United States of America', 'ENG'),
        (4325311, 1, 'New York', 'État de New York', 'NY', 'États-Unis', 'FRA'),
        (2162657, 2, 'Washington, D.C.', 'District of Columbia', 'DC', 'United States of America', 'ENG'),
        (4325312, 2, 'Washington', 'district de Columbia', 'DC', 'États-Unis', 'FRA'),
        (2162658, 3, 'San Francisco', 'California', 'CA', 'United States of America', 'ENG'),
        (4325313, 3, 'San Francisco', 'Californie', 'CA', 'États-Unis', 'FRA'),
        (2162659, 4, 'Berlin', 'Germany', NULL, 'Germany', 'ENG'),
        (4325314, 4, 'Berlin', 'Allemagne', NULL, 'Allemagne', 'FRA'),
        (2162660, 5, 'Los Angeles', 'California', 'CA', 'United States of America', 'ENG'),
        (4325315, 5, 'Los Angeles', 'Californie', 'CA', 'États-Unis', 'FRA'),
        (2162661, 6, 'Bern', 'Canton of Bern', NULL, 'Switzerland', 'ENG'),
        (4325316, 6, 'Berne', 'canton de Berne', NULL, 'Suisse', 'FRA'),
        (2162662, 7, 'Geneva', 'Canton of Geneva', NULL, 'Switzerland', 'ENG'),
        (4325317, 7, 'Genève', 'canton de Genève', NULL, 'Suisse', 'FRA'),
        (2162663, 8, 'Zürich', 'Canton of Zürich', NULL, 'Switzerland', 'ENG'),
        (4325318, 8, 'Zurich', 'canton de Zurich', NULL, 'Suisse', 'FRA'),
        (2162664, 9, 'Basel', 'Basel-Stadt', NULL, 'Switzerland', 'ENG'),
        (4325319, 9, 'Bâle', 'canton de Bâle-Ville', NULL, 'Suisse', 'FRA'),
        (2162665, 10, 'London', 'England', NULL, 'United Kingdom', 'ENG'),
        (4325320, 10, 'Londres', 'Angleterre', NULL, 'Royaume-Uni', 'FRA'),
        (2162666, 11, 'Cairo', 'Cairo Governorate', NULL, 'Egypt', 'ENG'),
        (4325321, 11, 'Le Caire', 'Gouvernorat du Caire', NULL, 'Égypte', 'FRA'),
        (2162667, 12, 'Alexandria', 'Alexandria Governorate', NULL, 'Egypt', 'ENG'),
        (4325322, 12, 'Alexandrie', 'Alexandrie', NULL, 'Égypte', 'FRA'),
        (2162668, 13, 'Boston', 'Massachusetts', 'MA', 'United States of America', 'ENG'),
        (4325323, 13, 'Boston', 'Massachusetts', 'MA', 'États-Unis', 'FRA'),
        (2162669, 14, 'Toronto', 'Ontario', 'Ont.', 'Canada', 'ENG'),
        (4325324, 14, 'Toronto', 'Ontario', 'Ont.', 'Canada', 'FRA'),
        (2162670, 15, 'Vilnius', 'Vilnius City Municipality', 'Вильнюсско', 'Lithuania', 'ENG'),
        (4325325, 15, 'Vilnius', 'Municipalité de la ville de Vilnius', 'Вильнюсско', 'Lituanie', 'FRA'),
        (2162671, 16, 'Rome', 'Lazio', NULL, 'Italy', 'ENG'),
        (4325326, 16, 'Rome', 'Latium', NULL, 'Italie', 'FRA'),
        (2162672, 17, 'Nuuk', 'Greenland', NULL, 'Greenland', 'ENG'),
        (4325327, 17, 'Nuuk', 'Groenland', NULL, 'Groenland', 'FRA'),
        (2162673, 18, 'Brussels', 'Arrondissement of Brussels-Capital', NULL, 'Belgium', 'ENG'),
        (4325328, 18, 'Bruxelles', 'arrondissement administratif de Bruxelles-Capitale', NULL, 'Belgique', 'FRA'),
        (2162674, 19, 'Brussels', 'Belgium', NULL, 'Belgium', 'ENG'),
        (4325329, 19, 'Région de Bruxelles-Capitale', 'Belgique', NULL, 'Belgique', 'FRA'),
        (2162675, 20, 'Poznań', 'Greater Poland Voivodeship', NULL, 'Poland', 'ENG'),
        (4325330, 20, 'Poznań', 'voïvodie de Grande-Pologne', NULL, 'Pologne', 'FRA');

I run an equivalent query on both databases.

5.7:

SET @lon = 7.4653;
SET @lat = 51.5136;
SET @bound = 2.0;
SET @minLat = @lat -@bound/COS(RADIANS(@lat));
SET @maxLat = @lat +@bound;
SET @minLon = @lon -@bound/COS(RADIANS(@lat));
SET @maxLon = @lon +@bound;
SELECT
p.id,
ST_Distance_Sphere(p.position, ST_PointFromText(ST_AsText(POINT(@lon, @lat)), 4326)) AS distance,
pn.admin_abbr,
pn.name,
pn.administration,
pn.country
FROM (
    SELECT id, position
    FROM places
    WHERE MBRContains(ST_GeomFromText(CONCAT('Polygon((',
        @minLon, ' ', @minLat, ', ',
        @maxLon, ' ', @minLat, ', ',
        @maxLon, ' ', @maxLat, ', ',
        @minLon, ' ', @maxLat, ', ',
        @minLon, ' ', @minLat, '))'), 4326),
        position)
    OR st_equals(position, ST_PointFromText(ST_AsText(POINT(0.0, 0.0)), 4326))
    LIMIT 750
) p
JOIN place_names pn ON p.id = pn.place_id
WHERE pn.language = 'ENG'
AND LOWER(name) LIKE LOWER(CONCAT(?, '%'))
ORDER BY distance
LIMIT 10;

8.0, with latitude first since the signature of ST_ and MBR functions changed for srid 4326 in this version:

SET @lon = 7.4653;
SET @lat = 51.5136;
SET @bound = 2.0;
SET @minLat = @lat -@bound/COS(RADIANS(@lat));
SET @maxLat = @lat +@bound;
SET @minLon = @lon -@bound/COS(RADIANS(@lat));
SET @maxLon = @lon +@bound;
SELECT
p.id,
ST_Distance_Sphere(p.position, ST_PointFromText(ST_AsText(POINT(@lat, @lon)), 4326)) AS distance,
pn.admin_abbr,
pn.name,
pn.administration,
pn.country
FROM (
    SELECT id, position
    FROM places
    WHERE MBRContains(ST_GeomFromText(CONCAT('Polygon((',
        @minLat, ' ', @minLon, ', ',
        @maxLat, ' ', @minLon, ', ',
        @maxLat, ' ', @maxLon, ', ',
        @minLat, ' ', @maxLon, ', ',
        @minLat, ' ', @minLon, '))'), 4326),
        position)
    OR st_equals(position, ST_PointFromText(ST_AsText(POINT(0.0, 0.0)), 4326))
    LIMIT 750
) p
JOIN place_names pn ON p.id = pn.place_id
WHERE pn.language = 'ENG'
AND LOWER(name) LIKE LOWER(CONCAT(?, '%'))
ORDER BY distance
LIMIT 10;

However for some reason, all other things (including the host of the databases) being equal, the 8.0 database is consistently 150-250 milliseconds slower than the older version: the opposite of what I would expect. My tables contain millions of records, which may affect measurements compared to just the example rows I provided above.

On 8.0 I also tried using ST_Distance_Sphere(p.position, ST_SRID(POINT(@lon, @lat), 4326)) AS distance with the new override of ST_SRID() which allows a second parameter to convert geometry to a specific SRID, instead of converting to text, which I expected to be slower, but instead this seems to make the query even slower.

Why does this happen? Is there some optimisation I am missing? Can I leverage the newer SQL version to get higher performance?

New contributor
theberzi is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
1
  • Please provide EXPLAINs for each. – Rick James Jun 10 at 20:46

Your Answer

theberzi is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.