increasing performance on a SELECT query with large 3D point data set

Question

I have a large dataset (around 1.9 million rows) of 3D points that I'm selecting from. The statement I use most often is similar to:

SELECT * FROM points 
WHERE x > 100 AND x < 200 
AND   y > 100 AND y < 200 
AND   z > 100 AND z < 200 
AND otherParameter > 10

I have indicies on x, y, and z as well as the otherParameter. I've also tried adding a multi-part index to x,y,z but that hasn't helped.

Any advice on how to make this SELECT query quicker?

Answer 1

B-Tree indexes won't help much for such a query.

What you need as an R-Tree index and the minimal bounding parallelepiped query over it.

Unfortunately, MySQL does not support R-Tree indexes over 3d points, only 2d. However, you may create an index over, say, X and Y together which will be more selective that any of the B-Tree indexes on X and Y alone:

ALTER TABLE points ADD xy POINT;

UPDATE  points
SET     xy = Point(x, y);

ALTER TABLE points MODIFY xy POINT NOT NULL;


CREATE SPATIAL INDEX sx_points_xy ON points (xy);

SELECT  *
FROM    points
WHERE   MBRContains(LineString(Point(100, 100), Point(200, 200), xy)
        AND z BETWEEN 100 and 200
        AND otherParameter > 10;

This is only possible if your table is MyISAM.