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 · Accepted Answer · 2015-01-15 15:18:38Z

1

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.

edited Jan 15, 2015 at 15:18

answered Jan 15, 2015 at 15:13

Marcus Adams

54k9 gold badges98 silver badges145 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amnon Over a year ago

Since I need the intersecting records, there would be nothing to pre-calculate. There is just the calculation of the intersection. Are you saying that there is no way to speed it up?

Collectives™ on Stack Overflow

can spatial indexes be used for join in mysql?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related