[ACCEPTED]-SQLite - getting closest value-sqlite

Accepted answer

Score: 18

Using an order-by, SQLite will scan the 20 entire table and load all the values into 19 a temporary b-tree to order them, making 18 any index useless. This will be very slow 17 and use a lot of memory on large tables:

explain query plan select * from 'table' order by abs(10 - value) limit 1;
0|0|0|SCAN TABLE table
0|0|0|USE TEMP B-TREE FOR ORDER BY

You 16 can get the next lower or higher value using 15 the index like this:

select min(value) from 'table' where x >= N;
select max(value) from 'table' where x <= N;

And you can use union to 14 get both from a single query:

explain query plan 
        select min(value) from 'table' where value >= 10
  union select max(value) from 'table' where value <= 10;
1|0|0|SEARCH TABLE table USING COVERING INDEX value_index (value>?)
2|0|0|SEARCH TABLE table USING COVERING INDEX value_index (value<?)
0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)

This will be 13 pretty fast even on large tables. You could 12 simply load both values and evaluate them 11 in your code, or use even more sql to select 10 one in various ways:

explain query plan select v from
   (      select min(value) as v from 'table' where value >= 10
    union select max(value) as v from 'table' where value <= 10)
  order by abs(10-v) limit 1;
2|0|0|SEARCH TABLE table USING COVERING INDEX value_index (value>?)
3|0|0|SEARCH TABLE table USING COVERING INDEX value_index (value<?)
1|0|0|COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)
0|0|0|SCAN SUBQUERY 1
0|0|0|USE TEMP B-TREE FOR ORDER BY

explain query plan select 10+v from
   (      select min(value)-10 as v from 'table' where value >= 10
    union select max(value)-10 as v from 'table' where value <= 10)
  group by v having max(abs(v)) limit 1;
2|0|0|SEARCH TABLE table USING COVERING INDEX value_index (value>?)
3|0|0|SEARCH TABLE table USING COVERING INDEX value_index (value<?)
1|0|0|COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)
0|0|0|SCAN SUBQUERY 1
0|0|0|USE TEMP B-TREE FOR GROUP BY

Since you're interested 9 in values both arbitrarily greater and less 8 than the target, you can't avoid doing two 7 index searches. If you know that the target 6 is within a small range, though, you could 5 use "between" to only hit the index once:

explain query plan select * from 'table' where value between 9 and 11 order by abs(10-value) limit 1;
0|0|0|SEARCH TABLE table USING COVERING INDEX value_index (value>? AND value<?)
0|0|0|USE TEMP B-TREE FOR ORDER BY

This 4 will be around 2x faster than the union 3 query above when it only evaluates 1-2 values, but 2 if you start having to load more data it 1 will quickly become slower.

Score: 17

This should work:

SELECT * FROM table
ORDER BY ABS(? - value)
LIMIT 1

Where ? represents the value 1 you want to compare against.

Source: stackoverflow.com

[ACCEPTED]-SQLite - getting closest value-sqlite

Python, Sqlite3 - How to convert a list to a BLOB cell

django error 'too many values to unpack'

SQLite parameter substitution and quotes

How to insert duplicate rows in SQLite with a unique ID?

Create SQLite database in memory

How to discover table properties from SQLAlchemy mapped object

How would you convert secs to HH:MM:SS format in SQLite?

Are there any reserved words in SQLite?

Only first table in create table statement being created

Why do I get a "sqlite3: not found" error on a rooted Nexus One when I try to open a database using the adb shell?