can spatial indexes be used for join in mysql?

Question

Can spatial indexes be used for joining tables in mysql?

I have two tables with a GEOMETRY column and a spatial index on it. The table is MyISAM. I would like to join these tables on the rows which intersect. I cannot get mysql to use the spatial indexes despite using FORCE INDEX in my query.

The query is:

SELECT * 
FROM a FORCE INDEX FOR JOIN (asidx) 
JOIN b FORCE INDEX FOR JOIN (bsidx) 
     ON Intersects(a.g, b.g) -- g is the name of the GEOMETRY

The explain plan is:

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra                          |
|  1 | SIMPLE      | a     | ALL  | asidx         | NULL | NULL    | NULL | 50000 |                                |
|  1 | SIMPLE      | b     | ALL  | bsidx         | NULL | NULL    | NULL | 50000 | Using where; Using join buffer |

Why aren't the indexes use? For 50k rows tables it runs for 15 minutes. How can I make it faster?

Marcus Adams Marcus Adams · Accepted Answer · 2015-01-15T15:13:09

Yes, spatial indexes can be used for joins, but you aren't using an index for your join.

In order for a index to be used for a search, MySQL needs a constant, or an expression that refers to a data column that has already been read.

You're not referencing an indexed column in your ON clause. You're referencing 0 or 1, which is the result of the INTERSECTS() function.

Your ON clause specifies a function on columns from both tables in the join. MySQL won't have the column values required for the function until both records are already read, so no index can be used for the join, requiring a full scan.

Basically, MySQL doesn't know if two records go together until it tries it, so it must try every combination.

Perhaps a better solution would be to precalculate a table of intersection pairs, store them in a separate (junction) table, and join that way.

can spatial indexes be used for join in mysql?

1 Answers