# [ACCEPTED]-SQLite - getting closest value-sqlite

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
```

or

```
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.

This should work:

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

Where `?`

represents the value 1 you want to compare against.

More Related questions

We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.