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

InnoDB 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=InnoDB;
       
      BEGIN;
      DELIMITER $$
      SET @i=1000;
      WHILE @i<=5000
      DO
        INSERT INTO t1 VALUES (POINT(900, 200));
        SET @i=@i+1;
      END WHILE;
      $$
      DELIMITER ;
      COMMIT;
       
      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. In fact, no rows match the condition.

      Attachments

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.