MySQL Storing different nested sets in same table

Question

I have a table that stores nested sets. It stores different nested sets differentiated by a collectionid (yes i'm mixing terms here, really should be nestedsetid). it looks somewhat like this:

id | orgid | leftedge | rightedge | level | collectionid
1  | 123   |  1       |  6        |  1    |   1
2  | 111   |  2       |  3        |  2    |   1
3  |  23   |  4       |  5        |  2    |   1
4  |  67   |  1       |  2        |  1    |   2
5  | 123   |  3       |  4        |  1    |   2
6  | 600   |  1       |  6        |  1    |   3
7  |  11   |  2       |  5        |  2    |   3
8  | 111   |  3       |  4        |  3    |   3

Originally I wanted to take advantage of the R-Tree Indexes, but the code i have seen for this: LineString(Point(-1, leftedge), Point(1, rightedge)) won't quite work since it doesn't take into account the collectionid and this id:1 and id:6 would end up being the same.

Is there a way I can use the R-Tree index with my current set up... Surely you can have different nested sets in the same table? My main aim is to be able to use the MBRWithin and MBRContains functions. Using MySQL 5.1

Answer 1

For single-dimensional data (these are 1d intervals, right?) there exist better index structures than r-trees. These are designed for dynamic data in 2-10 dimensions (at higher dimensions, performance isn't too good, as the split strategies and distance functions don't work very well anymore)

Actually for your use case, classic SQL should work very well. And the database can make use of its indexes efficiently. Having a good index structure is one thing, but you want to have the database exploit the indexes it has as good as possible.

As such, I'd just index leftEdge and rightEdge and the <, <=, >, >= functions. They are fast! And for the collectionid column, a bitmap index should be good.