HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Sqlite: Finding the next or previous element in a table consisting of integer tuples

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
previousthetuplessqlitenextelementconsistingfindingintegertable

Problem

I have a sqlite table called tuples defined like

create table tuples
(
    a INTEGER not null,
    b INTEGER not null,
    c INTEGER not null,
    d INTEGER not null,
    primary key (a, b, c, d)
) without rowid;


filled with millions of unique tuples (>1TB). New tuples are inserted quite often, with "random" values. Rows are removed only in very rare occasions.

For an external process accessing the database, I need to find the "next" or "previous" existing 4-tuple in the table.

For example: Given tuples (1-1-1-1), (1-1-1-4) and (1-2-3-4), for the tuple (1-1-1-3) (which does not need to exist in the table) the "next" element is (1-1-1-4) and the previous is (1-1-1-1) (both which need to exist). For (1-1-1-4) (1-2-3-4) is the "next" element. Corner-case: If there there actually is no "next" or "previous" element the result is allowed to be empty. (1-2-3-4) has no "next" element.

At the moment I try to find the next tuple with ("center" is (1-1-1-3))

select a,b,c,d from tuple
where (a == 1 AND b == 1 AND c == 1 AND d > 3) OR
      (a == 1 AND b == 1 AND c > 1) OR
      (a == 1 AND b > 1) OR
      (a > 1)
order by a, b, c, d
limit 1;


which is really slow.

The short question here is: Is there a way to speed up the the process? Ideally the response should only take milliseconds, like searching for the exact value of a tuple (which is basically instantaneous). Using other/more indices, multiple and/or other queries, even changing the database structure is a valid solution.

Edit: Each element of a tuple may cover the whole allowed integer range.

Solution

At the moment I try to find the next tuple with ("center" is (1-1-1-3))

Test

SELECT *
FROM tuples
WHERE (1,1,1,3) < (a,b,c,d)
ORDER BY a,b,c,d LIMIT 1


fiddle

Code Snippets

SELECT *
FROM tuples
WHERE (1,1,1,3) < (a,b,c,d)
ORDER BY a,b,c,d LIMIT 1

Context

StackExchange Database Administrators Q#281171, answer score: 9

Revisions (0)

No revisions yet.