Bug #102599 Slow performance of MySQL 8 compared with 5.7 on geographical SELECT
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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.061 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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.