Given a very brief structure of the two tables

1.
| customer | polygon |
| ——– | ——– |
| A | POLYGON1 |
2.

The SQL is roughly as follows

SELECT customer
FROM
table1 INNER JOIN table2 
FORCE INDEX polygon_idx
ON MBRContains(table1.polygon,table2.point)

Usually index is created on polygon.

Please ignore FORCE INDEX, Because I found that if not FORCE INDEX, MySQL will not even use the index of polygon. I found that this problem is due to a MySQL bug.

When indexing only on polygon, it is very fast and explain shows that the index is used.

But when I tried to create indexes on both polygon and point, the query efficiency was very low, almost scanning the entire table.

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 about 500,000 records in Table 1.