Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15066

Slow ST_INTERSECTS queries comparted to MySQL 5.7

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.2.11, 10.2.12, 10.3.4
    • Fix Version/s: 10.2
    • Component/s: GIS
    • Labels:
    • Environment:
      Debian Linux 9.3, virtual machine in Xen 4.8

      Description

      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 |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      

      This are the same queries ran on mysql.

      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).

        Attachments

          Activity

            People

            Assignee:
            holyfoot Alexey Botchkov
            Reporter:
            Vlk Zdeněk Mžourek
            Votes:
            1 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated: