How to partition a spatial index in MySQL or combine it with a BTREE index?

Question

I have a table in MySQL that contains the individual points of geographic line strings that I am showing on an interactive map. Among others, the table contains the following fields:

`pos` point NOT NULL,
`zoom` int(10) unsigned NOT NULL

pos contains the coordinates of the point, while zoom is a number from 1 to 20 at which zoom level of the map this particular point needs to be loaded. When the map is zoomed out very far (low zoom level), a large section of the line needs to be loaded, but with a low resolution (few points need to be loaded). When the map is zoomed in (high zoom level), a small section of the line needs to be loaded, but with a high resolution. Generally this means that the table contains much more points with higher zoom levels than with lower zoom levels.

When the user moves or zooms the map, the line points for the current map view are loaded using a query like this:

SELECT * FROM LinePoints WHERE zoom <= 7 AND MBRContains(LINESTRING(POINT(4.8449,49.8804), POINT(27.0373,54.6038)), pos);

I would like to optimize this query so that it runs fast even when there are a lot of line points.

I have already added a SPATIAL index on the pos field and a BTREE index on the zoom field. This works well for very high and very low zoom levels:

At very high zoom levels, the bounding box is small, so only a small number of points is selected using the spatial index. It is fast to then filter this small number of points by zoom level.
At very low zoom levels, only a small number of points exist for this zoom level, so the BTREE index is used. It is then fast to filter this small number of points by the bounding box.
At medium zoom levels however, it depends on the data which index MySQL chooses, but both cases are slow. If it uses the BTREE index, a lot of points are returned for the medium zoom levels, and filtering those by bounding box takes long. If it uses the spatial index, a lot of points are returns for the rather large bounding box, and it takes long to filter those by zoom level. In both cases, the query takes several seconds for the data currently in my database, which is unacceptable for an interactive map.

How can I optimise my database?

Is there any way to combine the SPATIAL and BTREE indexes?
Since the number of zoom levels is fixed, will I benefit from somehow partitioning the table? I failed to try this out because it seems that MySQL does not support partitioning tables that contain spatial columns. Would I benefit from splitting the data into 20 different tables?
Would I be able to achieve better results by switching to a different database system, for example PostgreSQL?

Rick James Rick James · Accepted Answer · 2021-05-29T16:41:42

Since you have a strong clue of which way to do the query, pick one of two queries.

SELECT * FROM LinePoints WHERE zoom <= 3 
    HAVING  MBRContains(LINESTRING(POINT(4.8449,49.8804), POINT(27.0373,54.6038)), pos);

SELECT * FROM LinePoints
    WHERE MBRContains(LINESTRING(POINT(4.8449,49.8804), POINT(27.0373,54.6038)), pos)
    HAVING  zoom <= 10;

(If the Optimizer decides to fold the HAVING into the WHERE, then perhaps a subquery is necessary. Or maybe some kind of IF() function.)

How to partition a spatial index in MySQL or combine it with a BTREE index?

1 Answers