5

I have a table that has an integer timestamp as primary key. Almost every query on this table will be of the pattern

SELECT * FROM table WHERE timestamp BETWEEN x AND y

These ranges are usually significantly smaller than the total range of stamps. Insertions happen very rarely and usually at timestamps after every other existing stamp.

Can I somehow tell my database (SQLite) to keep these entries 'sorted' and then make the selects only from the start timestamp, search through rows from there, and then stop as soon as the end timestamp has been surpassed in order to avoid having to scan the entire table and compare the value for each row? Or is this already happening?

Improve this question

1 Answer 1

Reset to default
4

What you're looking for is called indexing and it will allow both of your goals to be met.

Indexes save the data pre-sorted on the order of the column(s) defined for the index. They also (typically) use a B-Tree data structure to efficiently store the data so that the entire table doesn't have to be scanned when it's faster to just seek on the range of nodes in the tree that contain the data needed to serve your query.

In SQLite, the primary key is automatically created as a clustered index too. So you actually don't need to do anything additional. But if in your specific example, you cared about indexing a field that wasn't the primary key, then you'd want to define an index like such:

CREATE INDEX IX_IndexName ON TableName (SomeOtherFieldNotInThePrimaryKey)

For further information on indexing, please see this article.

Improve this answer
4
  • Damn, I was hoping for some way to speed up my DB, but if it already does that per default, I guess I need to find another way :D Thank you for the explanation!
    – Krateng
    Feb 27 at 3:32
  • @Krateng With how your table is indexed and the query you're trying to run, your query itself should execute very quickly (less than 1 second) already. If you're pulling a lot of data then your bottleneck could be the rendering of that data on the client / application side which would not be a database bottleneck then. Otherwise you can also make sure your query is getting the proper execution plan that it should be by running an EXPLAIN QUERY PLAN. It's possible it's not respecting the index and doing a scan operation if it mistakenly thinks that's faster.
    – J.D.
    Feb 27 at 12:31
  • It is under 1 second, but I have a webpage that needs a lot of queries and I was hoping to get this biggest one reduced a bit - I'd like this application to be feasible on a RasPi 4. I checked with cProfile and I'm fairly sure it's the database, I think I just have too many queries for that page. I'll check with the EXPLAIN_QUERY_PLAN though, thanks!
    – Krateng
    Feb 27 at 21:52
  • @Krateng NP usually "less than 1 second" is a generalization I use. But realistically you should be seeing 10's of milliseconds at most for the example query you provided. There's not much more that can usually be done after that other than increasing the provisioned hardware or re-designing how your use cases work. If by a lot of queries, you mean you're running the same one multiple times but with different parameters then you may find a more efficient workflow is using a wider parameter set (e.g. larger timestamp range) to get more data at one time and make less database calls. YMMV.
    – J.D.
    Feb 28 at 0:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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