Description:
Performance on some geographical SELECT queries is critically inferior under MySQL 8.0 compared with MySQL 5.7. This makes the newer version unusable for some purposes.
In testing, the following run-times were observed for the same queries (after RESET QUERY CACHE) for MySQL 5.7, MySQL 8.0, and MariaDB 10.5.8. There were two test queries: Test 1 (Quick), Test 2 (Slow).
Timings (in milliseconds) were
Test 1 (Quick): SQL8.0 172ms, SQL5.7 31ms, Maria 16ms
Test 2 (Slow): SQL8.0 7672ms, SQL5.7 1547ms, Maria 516ms
MySQL8.0 is running about five times slower than MySQL5.7 (which itself is slower than MariaDB).
This performance impact is not supportable and it prohibits me from switching over to MySQL 8.0.
How to repeat:
The table 'oa_bdy' lives in a database called 'postcodes'. It has around 230,000 rows and contains a geographic column called 'shape'.
The two test queries are:
Test 1 (Quick):
SELECT `oacode`,ST_asWKB(`shape`) as `shape` FROM
(SELECT * FROM oa_bdy WHERE seatname='Barnsley Central') AS `vtable`
WHERE MBRIntersects(`shape`,ST_GeomFromText('POLYGON
((426519 410113, 426519 4156675, 432073 415667, 432073 410113,
426519 410113))', 27700));
Test 2 (Slow):
SELECT `oacode`,ST_asWKB(`shape`) as `shape` FROM
(SELECT * FROM oa_bdy WHERE seatname = 'Barnsley Central') as `vtable`
WHERE MBRIntersects(`shape`, ST_GeomFromText('POLYGON
((-2734 -2734, -2734 702734, 702734 702734, 702734 -2734, -2734 -2734))',
27700));
Suggested fix:
Might be some performance problem with the MBRIntersects condition, or some other change introduced by the refactoring of GIS functionality between 5.7 and 8.0.
Description: Performance on some geographical SELECT queries is critically inferior under MySQL 8.0 compared with MySQL 5.7. This makes the newer version unusable for some purposes. In testing, the following run-times were observed for the same queries (after RESET QUERY CACHE) for MySQL 5.7, MySQL 8.0, and MariaDB 10.5.8. There were two test queries: Test 1 (Quick), Test 2 (Slow). Timings (in milliseconds) were Test 1 (Quick): SQL8.0 172ms, SQL5.7 31ms, Maria 16ms Test 2 (Slow): SQL8.0 7672ms, SQL5.7 1547ms, Maria 516ms MySQL8.0 is running about five times slower than MySQL5.7 (which itself is slower than MariaDB). This performance impact is not supportable and it prohibits me from switching over to MySQL 8.0. How to repeat: The table 'oa_bdy' lives in a database called 'postcodes'. It has around 230,000 rows and contains a geographic column called 'shape'. The two test queries are: Test 1 (Quick): SELECT `oacode`,ST_asWKB(`shape`) as `shape` FROM (SELECT * FROM oa_bdy WHERE seatname='Barnsley Central') AS `vtable` WHERE MBRIntersects(`shape`,ST_GeomFromText('POLYGON ((426519 410113, 426519 4156675, 432073 415667, 432073 410113, 426519 410113))', 27700)); Test 2 (Slow): SELECT `oacode`,ST_asWKB(`shape`) as `shape` FROM (SELECT * FROM oa_bdy WHERE seatname = 'Barnsley Central') as `vtable` WHERE MBRIntersects(`shape`, ST_GeomFromText('POLYGON ((-2734 -2734, -2734 702734, 702734 702734, 702734 -2734, -2734 -2734))', 27700)); Suggested fix: Might be some performance problem with the MBRIntersects condition, or some other change introduced by the refactoring of GIS functionality between 5.7 and 8.0.