I use an SQLite DB in my python application. I have to do many queries of the form
SELECT * from positions where (x between ? and ? ) and (y between ? and ?)
I already created an index with the following structure:
CREATE INDEX IF NOT EXISTS positions_idx ON positions (x, y)
Is there any way to improve the query speed? Especially, is a single combined index better than two separate indices?
Edit
A similar question was already asked on this page. The solution presented there makes use of an R*Tree. I dont quite understand how to construct such an R*Tree in SQL. The line
CREATE VIRTUAL TABLE r_tree_pos USING rtree(xMin, xMax, yMin, yMax);
would just create an empty table which I would have to populate. Do I have to create manually an R*Tree from my original positions table and insert the result into the r_tree_pos table? A simple example - maybe in python - would be great.
