I’m seeking insights into why MySQL might outperform PostGIS PostgreSQL in certain geospatial query scenarios and whether it’s advisable to continue using MySQL for such purposes over PostGIS PostgreSQL.

I recently conducted a performance comparison between MySQL and PostGIS PostgreSQL for executing geospatial queries. Surprisingly, the MySQL query outperformed the PostGIS PostgreSQL query in terms of execution time. Here are the queries and their respective execution times:

MySQL Query:

SELECT roads 
FROM tbl_allroads 
WHERE ST_Intersects(points, ST_Buffer(ST_GeomFromText('POINT(17.0809031 120.9019854)', 4326), 0.00027027)) 
ORDER BY ST_Distance(points, ST_GeomFromText('POINT(17.0809031 120.9019854)', 4326)) 
LIMIT 1;

As per MySQL, total query runtime: 0.0012 seconds

PostGIS PostgreSQL Query:

SELECT *
FROM tbl_road
WHERE ST_DWithin(
    tbl_road.coordinates::geography,
    ST_SetSRID(ST_MakePoint(120.9019854, 17.0809031), 4326)::geography,
    20  -- distance in meters
);

As per Postgre Sql: Total query runtime: 49 milliseconds

While the MySQL query executed significantly faster, I’m interested in understanding why this might be the case. Additionally, I’m unsure whether it’s advisable to continue using MySQL for geospatial queries over PostGIS PostgreSQL. Any insights into the performance difference and recommendations for choosing between the two databases for geospatial purposes would be greatly appreciated.

For context, I’m using MySQL 8 with spatial indexing, and the dataset contains thousands of rows. On the other hand, PostGIS PostgreSQL is version 16.2, compiled by Visual C++ build 1937, 64-bit, with the following configuration: “3.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1”.