[#MDEV-15066] Slow ST_INTERSECTS queries comparted to MySQL 5.7

We are using a lot of ST_INTERSECTS queries in our application and after migration from mysql (version 5.7) to maria (10.2) we've noticed notable performance hit using these queries.

Here is an example query running with and without a spatial index (take a look at table defintion in attachements - there is also a complete dataset for testing).

Server version: 10.2.12-MariaDB-10.2.12+maria~stretch-log mariadb.org binary distribution

MariaDB [(none)]> SELECT SQL_NO_CACHE count(*) FROM `global`.`address_country_shape` acs where  ST_INTERSECTS(0x0000000001030000000100000005000000000000004018314000000000c0bd4840000000008023314000000000c0bd484000000000802331400000000020b8484000000000401831400000000020b84840000000004018314000000000c0bd4840,shape);

+----------+

| count(*) |

+----------+

|        1 |

+----------+

1 row in set (0.36 sec)

MariaDB [(none)]> explain extended SELECT SQL_NO_CACHE count(*) FROM `global`.`address_country_shape` acs  where  ST_INTERSECTS(0x0000000001030000000100000005000000000000004018314000000000c0bd4840000000008023314000000000c0bd484000000000802331400000000020b8484000000000401831400000000020b84840000000004018314000000000c0bd4840,shape);

+------+-------------+-------+-------+---------------+-------+---------+------+------+----------+-------------+

| id   | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra       |

+------+-------------+-------+-------+---------------+-------+---------+------+------+----------+-------------+

|    1 | SIMPLE      | acs   | range | shape         | shape | 34      | NULL |    1 |   100.00 | Using where |

+------+-------------+-------+-------+---------------+-------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

MariaDB [(none)]> SELECT SQL_NO_CACHE count(*) FROM `global`.`address_country_shape` acs ignore index (shape)  where  ST_INTERSECTS(0x0000000001030000000100000005000000000000004018314000000000c0bd4840000000008023314000000000c0bd484000000000802331400000000020b8484000000000401831400000000020b84840000000004018314000000000c0bd4840,shape);

+----------+

| count(*) |

+----------+

|        1 |

+----------+

1 row in set (0.37 sec)

MariaDB [(none)]> explain extended SELECT SQL_NO_CACHE count(*) FROM `global`.`address_country_shape` acs ignore index (shape)  where  ST_INTERSECTS(0x0000000001030000000100000005000000000000004018314000000000c0bd4840000000008023314000000000c0bd484000000000802331400000000020b8484000000000401831400000000020b84840000000004018314000000000c0bd4840,shape);

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+

|    1 | SIMPLE      | acs   | ALL  | NULL          | NULL | NULL    | NULL |  215 |   100.00 | Using where |

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+

Server version: 5.7.21-log MySQL Community Server (GPL)

mysql> SELECT SQL_NO_CACHE count(*) FROM `global`.`address_country_shape` acs where  ST_INTERSECTS(0x0000000001030000000100000005000000000000004018314000000000c0bd4840000000008023314000000000c0bd484000000000802331400000000020b8484000000000401831400000000020b84840000000004018314000000000c0bd4840,shape);

+----------+

| count(*) |

+----------+

|        1 |

+----------+

1 row in set, 1 warning (0,08 sec)

mysql> explain extended SELECT SQL_NO_CACHE count(*) FROM `global`.`address_country_shape` acs where  ST_INTERSECTS(0x0000000001030000000100000005000000000000004018314000000000c0bd4840000000008023314000000000c0bd484000000000802331400000000020b8484000000000401831400000000020b84840000000004018314000000000c0bd4840,shape);

+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | acs   | NULL       | range | shape         | shape | 34      | NULL |    1 |   100.00 | Using where |

+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+

1 row in set, 3 warnings (0,00 sec)

mysql> SELECT SQL_NO_CACHE count(*) FROM `global`.`address_country_shape` acs ignore index (shape)  where  ST_INTERSECTS(0x0000000001030000000100000005000000000000004018314000000000c0bd4840000000008023314000000000c0bd484000000000802331400000000020b8484000000000401831400000000020b84840000000004018314000000000c0bd4840,shape);

+----------+

| count(*) |

+----------+

|        1 |

+----------+

1 row in set, 1 warning (0,37 sec)

mysql> explain extended SELECT SQL_NO_CACHE count(*) FROM `global`.`address_country_shape` acs ignore index (shape)  where  ST_INTERSECTS(0x0000000001030000000100000005000000000000004018314000000000c0bd4840000000008023314000000000c0bd484000000000802331400000000020b848400000000040183140      002

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | acs   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  215 |   100.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 3 warnings (0,00 sec)

As you can see using the spatial index on maria did not have an effect on query speed, however, using the index on mysql had a very positive performace impact.

We've also tested it using fresh installation of both mariadb and mysql getting same results.

Can you take a look at this issue? It is quite severe for us as we are thinking about going back to mysql. Both mysql and maria are using same configuration (see attachments).

[MDEV-15066] Slow ST_INTERSECTS queries comparted to MySQL 5.7 Created: 2018-01-25 Updated: 2022-08-04
Status:	Confirmed
Project:	MariaDB Server
Component/s:	GIS
Affects Version/s:	10.2.11, 10.2.12, 10.3.4
Fix Version/s:	10.3

[MDEV-15066] Slow ST_INTERSECTS queries comparted to MySQL 5.7 Created: 2018-01-25 Updated: 2022-08-04