8

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.

0

2 Answers 2

9

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

1
2

You should store the 4 values concatenated and then converted to an int in a Generated Column. You can then index that column for better for performance and then your queries are just a comparison to one column which is simpler / more maintainable.

Your query to find the next Tuple would look like this:

select a,b,c,d from tuple
where 1113 < GeneratedColumn
order by GeneratedColumn
limit 1;
2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.