MySQL Geo Spatial Query is very slow although index is used

I need to fetch records from a InnoDb Table by distance (must not be exactly) and sort by distance.
The table has 10 million records.

My best time is so far 8 sec (3 sec without order by distance), which make this not usable. How I could improve this?

I have a point column defined as SRID 4326.
I'm using MySQL 8.0.12.

SELECT mp.hash_id, 
ROUND(ST_Distance(ST_SRID(POINT(8.53955, 47.37706), 4326), mp.geo_pt), 2) AS distance
  FROM member_profile mp 
  WHERE
    MBRCONTAINS(ST_GeomFromText(
      CONCAT('POLYGON((', ST_X(POINT (8.53955, 47.37706)) - 0.43415340086831, ' ',
        ST_Y(POINT (8.53955, 47.37706)) - 0.43415340086831, ',',
        ST_X(POINT (8.53955, 47.37706)) + 0.43415340086831, ' ',
        ST_Y(POINT (8.53955, 47.37706)) - 0.43415340086831, ',',
        ST_X(POINT (8.53955, 47.37706)) + 0.43415340086831, ' ',
        ST_Y(POINT (8.53955, 47.37706)) + 0.43415340086831, ',',
        ST_X(POINT (8.53955, 47.37706)) - 0.43415340086831, ' ',
        ST_Y(POINT (8.53955, 47.37706)) + 0.43415340086831, ',',
        ST_X(POINT (8.53955, 47.37706)) - 0.43415340086831, ' ',
        ST_Y(POINT (8.53955, 47.37706)) - 0.43415340086831, ')) ')
           , 4326), geo_pt)
-- ST_Distance(ST_GeomFromText('POINT (8.53955 47.37706)', 4326), mp.geo_pt) <= 25000 -- need 16 sec
-- order by distance -- need 8 sec with MBRContains, 100 sec with ST_Distance
LIMIT 50;

A spatial Index was created:

CREATE SPATIAL INDEX geo_pt_index ON mp (geo_pt);

EXPLAIN shows me that my geo_pt Index is used.

my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_buffer_pool_size = 12G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
key_buffer_size = 1G
secure-file-priv = ""

This Server is only allocated for this database, no load on it (except when I execute a Query). There is no IOPS bottleneck.
innodb_buffer

"I have a point column defined as SRID 4326. I'm using MySQL 8.0.12."

I have a similar problem and changing the SRID to 0 improves performance significantly. I don't know if the side effect are unbearable for you, but at least you should try! Dont forget the other order of the lat and lon if you do that ;)

KR
Pete

StackExchange Database Administrators Q#214268, answer score: 5