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.
MySQL doesn't support
R-Tree indexes over
3d points, only
2d. However, you could make a catalog over, say,
Y together which is more selective that the
B-Tree indexes on
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 really is only possible in case your table is
I do not have mySQL to check but I am curious how efficient its INTERSECT is:
select points.* from points join ( select id from points where x > 100 AND x < 200 intersect select id from points where y > 100 AND y < 200 intersect 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.