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

MyISAM spatial index is not optimal with ST_Intersects

    XMLWordPrintable

Details

    Description

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (
       point point NOT NULL,
       SPATIAL KEY `point` (`point`)
      ) ENGINE=MyISAM;
       
      DELIMITER $$
      FOR i IN 1000..5000
      DO
        INSERT INTO t1 VALUES (POINT(900, 200));
      END FOR;
      $$
      DELIMITER ;
       
      explain SELECT * FROM t1 FORCE INDEX (point) WHERE ST_Intersects(point, GeomFromText('POLYGON((3 0, 3 3, 8 3, 8 0, 3 0))'));
      

      +------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
      | id   | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
      +------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
      |    1 | SIMPLE      | t1    | range | point         | point | 34      | NULL | 4001 | Using where |
      +------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
      

      The estimation of 4001 rows does not look good. All points inserted are outside of the polygon.

      ALTER TABLE t1 ENGINE=InnoDB;
      explain SELECT * FROM t1 FORCE INDEX (point) WHERE ST_Intersects(point, GeomFromText('POLYGON((3 0, 3 3, 8 3, 8 0, 3 0))'));
      

      +------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
      | id   | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
      +------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
      |    1 | SIMPLE      | t1    | range | point         | point | 34      | NULL |    4 | Using where |
      +------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
      

      Attachments

        Issue Links

          Activity

            People

              holyfoot Alexey Botchkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.