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:

```
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 `MyISAM`

.

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.