On 25 Oct 2018, at 5:13pm, siscia <sisciamir...@yahoo.com> wrote: > CREATE TABLE ranges ( > start int, > end int, > value int, > ); > > The query that I am interested in optimizing is > "select value from ranges > where (? between start and end)"
First, "END" is a reserved keyword in SQLite. Your use of it might work right now but you may find yourself in trouble later when you introduce a trigger or some other construction. I suggest you replace it as a column name with "finish" or perhaps both ends with "low" and "high". See <https://www.sqlite.org/lang_keywords.html> As an experiment to figure out a good optimization for your search problem, try the following: 1. Create two indexes on that table, one on (low,high,value), the other on (high,low,value). 2. Ensure that your 'ranges' table has plausible data in, both the number of rows and the contents of those rows must be similar to what you expect the table to contain in normal use. 3. Run the SQL command "ANALYZE". This tells SQLite to look at the table and figure out good ways to run future searches and sorts. The results of this are stored in the database. You will not need to run the command again even if you change the content of the database. Now run your query again and see whether the timing has changed. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
