CREATE TABLE scripts are as below:

1.

   CREATE TABLE `test_customer_ip` (
  `ip_polygon` polygon NOT NULL,
  `cbiid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2. CREATE TABLE test_warn_current (
   ip_point point NOT NULL,
   Anomaly_origin_ID varchar(64) NOT NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8

3. CREATE TABLE test_customer_info (
   cbiid int(11) NOT NULL DEFAULT 0,
   NAME varchar(200) DEFAULT NULL,
   KEY cbiid_idx (cbiid) USING BTREE
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8

The query is as follows

SELECT SQL_NO_CACHE
    Anomaly_origin_ID anomalyOriginId,
    NAME
FROM
    test_warn_current wc
    INNER JOIN test_customer_ip cip ON MBRcontains ( cip.ip_polygon, wc.ip_point ) 
    INNER JOIN test_customer_info cinfo ON cip.cbiid = cinfo.cbiid

At first, there is no index. The query takes about 3.590s. EXPLAIN as below:

Usually index is created on polygon. CREATE TABLE scripts are as below:

  CREATE TABLE `test_customer_ip` (
  `ip_polygon` polygon NOT NULL,
  `cbiid` int(11) DEFAULT NULL,
  SPATIAL KEY `ip_poly_idx` (`ip_polygon`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

When the query is executed again, it takes 0.383s and the EXPLAIN is as follows：

Please ignore the surprising number of rows and type ALL. The index really works. I found that this problem may be due to a MySQL bug. (Sry, I mentioned MySQL not to say that MySQL is used, but I think this bug can explain the problem here.)

Obviously, When indexing only on polygon, it is very fast.

But when I tried to create indexes on both polygon and point, the query efficiency is particularly low, scanning the entire table. The query takes about 15 seconds, which is longer than not using the index. Scripts as below:

CREATE TABLE `test_warn_current` (
  `ip_point` point NOT NULL,
  `Anomaly_origin_ID` varchar(64) NOT NULL,
  SPATIAL KEY `ip_point_idx` (`ip_point`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

EXPLAIN:

I’d like to know what’s causing this problem, although apparently I just need to remove the index on point and the query will be efficient again

version: 10.2.38-MariaDB-log

There are 75 records in test_warn_current, 239836 records in test_customer_ip, 187758 records in test_customer_info.

If I remove the INNER JOIN test_customer_info cinfo ON cip.cbiid = cinfo.cbiid from the query, it will work fine, but I don’t know why this happen. Here’s the query.

SELECT SQL_NO_CACHE
    Anomaly_origin_ID anomalyOriginId
FROM
    test_warn_current wc
    INNER JOIN test_customer_ip cip ON MBRcontains ( cip.ip_polygon, wc.ip_point ) 

EXPLAIN: