Using R-tree indexes for non-spatial data in MySQL

Question

I'm upgrading an inventory database and would like to speed up the queries. The bulk of the sorting is on 2 columns each of which is a range. For example.

SELECT ....
WHERE price BETWEEN 10.50 AND 34.20 
AND date BETWEEN 1311264060 AND 1313942460
ORDER BY stockNo LIMIT 100

I understand that because ranges are used, MySQL can only use an index on one column. In other words, it can use an index to extract the records matching the price range, but then has to resort to a table scan to find the records matching the date range.

I had the idea of pretending that the price and data were x and y axis and filling a column of spatial data and taking advantage of MySQLs spacial searches and R-TREE indexes to extract the data.

Does anyone have experience of R-TREES? Is this likely to give me a speed-up?

Has QUIT--Anony-Mousse · Accepted Answer · 2011-11-25 22:06:05Z

2

R-trees work best for data where the dimensions have similar meanings, e.g. geo coordinates, and you do window queries such as range queries.

Your data has probably a couple of effects that can harm R-trees, for example that the prices will only have a number of discrete values and the date ranges being no an entirely different scale. Many R-tree optimization strategies such as "volume" and "area" do not make much sense when they in fact compute the product of a price difference and a time difference.

The R-tree may still work though. But you probably are better off with kB-trees and similar splitting trees. Not sure if MySQL has any of that, I don't think so (it usually lacks all advanced features).

answered Nov 25, 2011 at 22:06

Has QUIT--Anony-Mousse

77.8k14 gold badges146 silver badges198 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using R-tree indexes for non-spatial data in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related