I've got a large dataset (around 1.9 million rows) of three dimensional points that I am choosing from. The statement I personally use most frequently is comparable 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've indicies on x, y, and z along with the otherParameter. I have also attempted adding a multi-part index to x,y,z but that has not assisted.

Any advice regarding how to get this to SELECT query faster?

B-Tree indexes will not help much for this type of query.

The thing you need being an R-Tree index and also the minimal bounding parallelepiped query regarding this.

Regrettably, MySQL doesn't support R-Tree indexes over 3d points, only 2d. However, you could make a catalog over, say, X and Y together which is more selective that the B-Tree indexes on X and Y alone:


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


CREATE SPATIAL INDEX sx_points_xy ON points (xy);

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

This really is only possible in case your table is MyISAM.

I do not have mySQL to check but I am curious how efficient its INTERSECT is:

     select points.*
     from points 
     select id from points where   x > 100 AND x < 200 
     select id from points where   y > 100 AND y < 200 
     select id from points where   z > 100 AND z < 200 
     ) as keyset
     on points.id = keyset.id

Not always suggesting this -- but it is something to test, particularly if you have separate indexes on x, y, and z.

EDIT: Since mySQl does not support INTERSECT the query above might be rewritten using JOINS of inline sights. Each view would have a keyset and every view might have the benefit of the separate indexes you've positioned on x, y, and z. The performance would rely on the numnber of secrets came back as well as on the intersect/join formula.

When i first examined the intersect approach (in SQLite) to ascertain if there have been methods to improve performance in spatial queries lacking utilizing their R-Tree module. INTERSECT was really reduced than utilizing a single non-composite index on among the spatial values after which checking the subset from the base table to obtain the other spatial values. However the results can differ with respect to the size the database. Following the table has arrived at gargantuan size and disk i/o gets to be more essential as a performance factor, it might be more effective to intersect discrete keysets, as both versions continues to be instantiated from a catalog, rather than perform a scan from the base table subequent for an initial fetch-from-index.